Hi,
Obviously, in a select statement, I can select * into #temptable. But can I do the equivalent from a stored procedure? I do not want to edit the stored procedure, just take the records it returns and put them into the temporary table. I do not know what records are returned by the stroped procedure until it is executed. Is there a way I can do this?
Thanks
J
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
Hi!
I have several problems of my coding, please give me some advice.
1. How to let result data place at temptable temperary for other mapping? should i create temptable first ?
2. When I got the duplicated record result, I require to map with the main tables (tblROrder & tblSOrder)to find out the record reference no. Please advice how to handle this issue with reference no. at the outcome.
Many thanks,
New Learner
***Coding as following
SELECT code, SMSNo, holderNo, count(*) From tblROrder
WHERE Day = @Day
GROUP BY code, SMSNo, SholderNo
HAVING COUNT(*) > 1<=====result will be found, but since i didn't get the RefNo at the listing, please advise how to let the outcome with RefNo
INTO #tmpOne (code, SMSNo, holderNo) <====error found
SELECT code, SMSNo, holderNo, GrossAmt, count(*) From tblSOrder
WHERE Day = @prmDay between D1 AND D14
GROUP BY code, SMSNo, holderNo, GrossAmt
HAVING COUNT(*) > 1<=====result will be found, but since i didn't get the RefNo at the listing, please advise how to let the outcome with RefNo
INTO #tmpTwo (code, SMSNo, holderNo) <====error found
How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
I can't script the table because this process must be automatic no manual.
Thank you
How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
Thank you
I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.
The command in the stored procedure is this N'xcopy c:source*.* c:dest'
Help please!!!!
Thanks,
Saied
HI,
Actally we have different server and we work on only one server and end of the day i have to copy the list of newly or modified procedure to other databases of other server. i have create the following Sp. but i am hving problem int it. please any one can have a look..
Declare @ServerName nvarchar(max)
Declare @DatabaseName nvarchar(max)
Declare @Prod nvarchar(max)
declare @m nvarchar(max)
DECLARE @SERVER nvarchar(max)
Declare @String nvarchar(max)
DECLARE @sp VARCHAR(MAX)
SELECT @sp = OBJECT_DEFINITION(OBJECT_ID('SPNAMEMOD'))
SET @Prod = 'SPNAMEMOD'
DECLARE ProcedureScripingCursor CURSOR FOR
SELECT SQLServer,DatabaseName FROM databaseListName
-- where sqlserver is ip address and databasename is databasename on that server
OPEN ProcedureScripingCursor
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp = REPLACE(@sp,'''','''''')
Set @String = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
Set @M = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
SELECT @M = @M + ' IF EXISTS (SELECT * FROM sys.objects where NAME = '''''+ @Prod + ''''') DROP PROCEDURE ' + @Prod +' '''
select @string = @string + ' ' + @sp + ' '''
SELECT @M
select @string
PRINT @M
-- Print @string
EXEC sp_Executesql @M
exec sp_Executesql @string
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
END
CLOSE ProcedureScripingCursor
DEALLOCATE ProcedureScripingCursor
Hi all, is there a way I can simply copy the stored procedures in one DB, and "paste" them over into another DB that is identical? Thanks!
View 2 Replies View RelatedI am trying to make a copy of my live MSSQL 2005 Database to my local Developer Edition install.
To copy the whole database, I am using MS Database Publishing Wizard. I am able to copy the tables and data without problem, but after generating the sql file and running the query against the local db to copy everything, I get errors for the stored procedures.
The stored procedures name is preceded by a login, for instance:
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [matthews].[proc_allitemswithtaxologykeylist]
( @list varchar(7777) )
AS (sql code below)
I have created the user "matthews" on the local instance and given the account DBO role membership in the database. What else do I need to do?
Is there a better way to synch live databases with a local install, perhaps from within management studio itself? I am more experienced with enterprise manager, but 2005 won't let you connect with it.
Thank you for any help.
Hi, Could someone please advise me of how to copy stored procedures from one database to another?
Many thanks,
James
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
How insert values from TABLE2 into @TempTable if a row into TABLE1 not in TABLE2?
--Query:
DECLARE @TempTable (IdTemp int, TempDate datetime)
INSERT INTO @TempTable (IdTemp, TempDate)
SELECT T1.Id, MAX(T2.Date2)
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.Id = T2.Id
GROUP BY T1.Id
/*
Example:
TABLE1 Id = '1'
TABLE2 = Id '1' not exists
*/
Go
Create table #RealTable
(
LastName varchar (20)
,Phone varchar (20)
,MissingInfo varchar (100)
,ErrMessage varchar (255)
);
Insert INTO #RealTable Values('Abraham','917 250 9999', '1', 'Need More Paper')
Insert INTO #RealTable Values('Sar', '917 999 9999' '2', 'Need ID')
Insert INTO #RealTable Values('John', '732 888 8888', '3', 'Need More Paper')
Insert INTO #RealTable Values ('Sal', '555 555 5555', '4', 'Legal Documentation')
some how it is not working??? i dont know why
Hello,
When I transfer/copy tables from one database to another (on the same SQL Server), everything is fine. When I try to tranfer/copy Stored Procedures, I get the error message:
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server."
I am using the DTS import/export wizard to do this.
Thanks,
Bruce
Hi All,
I have been abloe to copy tables from one db to another, but am getting a "Failed to copy objects" error when trying to import stored procs. can anyone please tell me what I'm doing wrong?
TIA,
Bruce
Can i do something like this
INSERT INTO CITIBANK.dbo.Contract (*)
FROM exec sp..
where sp is the stored procedure ?
Or do i need to specify all the colums where the * is ?
and if so is it the columns from the SP or the colums
from the destination table ?
I have several stored procedures, created in a development environment,that I need to move to a 'QA' environment, and then in turn, to variousproduction environments.When I move these stored procedures, I would like to encrypt them,using the 'WITH ENCRYPTION' clause.My question is, how do I copy these stored procedures from developmentto their target SQL server environment in an encrypted state?Up until now, we have been moving them by generating an SQL script andthen executing that script on the target server. I have tried thisusing a script with 'WITH ENCRYPTION' specified within it, but itdoesn't appear to work when I try and execute that script on the targetserver.Any advice would be greatly appreciated.Nick.
View 1 Replies View RelatedHow do i copy Stored Procedures from one SQL Express database to another?
View 8 Replies View RelatedI have a stored procedure in which I create a temporary table : ##xyz
The stored procedure runs fine, however if I try to run it again later on
I get the following message:
"Msg 2714, Level 16, State 1
There is already an object named '##xyz' in the database."
If I issue the drop table ##xyz I get the following message:
"Msg 3701, Level 11, State 1
Cannot drop the table '##xyz', because it doesn't exist in the system catalogs
Help!
Thanks in advance...Marisa
Is there a way in T-SQL to check to see if a #tempTable exists? I want to write a proc the uses a temp table, but I first need to see if the table already exists. if it does I want to drop it, otherwise skip
View 23 Replies View RelatedHi,
A stored procedure mySlowProc performs a complicate SELECT. It creates 10 temporary tables either by SELECT ... INTO #Table1 or by doing CREATE TABLE #Table2 and then INSERT #Table2. Beside the fact this SP is known to be slow and could be better written. However, for educational purpose, can you please shed some lights:
Question 1: Could the use of many #TempTable(s) lead directly or indirectly to a blocking situation where another user cannot perform a write (UPD, DEL, INS) on the table (or tables) which are SELECTed by the mySlowProc SP?
Question 2: What are the consequences of abusing #TempTable if mySlowProc is called concurrently by 100 different users? In particular, is there any locking mechanism that would prevent the mySlowProc SP from being executed concurrently.
Thanks very much in advance for any help.
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
Any suggestions, anyone, please?
Hi,
If any one could suggest if Is it possible to make a JOIN of two different select statements.
SELECT PartNumber,SUM(Value) FROM dbo.List_Parts where PartNumber like '%TX'
To be Joined with
SELECT PartNumber,COUNT(PartNumber) FROM dbo.List_Orders where Part Number like '%TX'
The result should be:
PartNumber , SUM(Value),COUNT(PartNumber)
I am trying to add a column to a temp table and then immeditaely queryagainst that new column. If I do this in Query Analyzer it works fineas long as there is a go in between, but I can't use a go inside astored proc.. How do i get SQL to finish processing the alter tablecommand so I can use the new column?alter table #TempPaging add TIId int not null identity--go --fixes the problem in QA, but not in procSelect * From #TempPaging Where TIId > 0 AND TIId < 11(Error TIId does not exist)
View 7 Replies View RelatedI am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Code:
exec('select RIGHT(00000 + CAST(dh.zipcode AS varchar(5)), 5) as zip, '+@fieldname +' as distance into #distance from sumplicity.dbo.t_distancetohospital dh')
This runs normally and returns the number of rows created. Yet when I do a 'select * from #distance' query, I get a message that #distance doesn't exist.
Where is it storing all of this and how do I access it?
Hi,In SQL 2000 if I wanted to take a complete copy of another running sqldatabase all did was create a new database locally and right-click itand select import and point to another database and click copyeverything (stored procedures as well) and it did it for. I can't seemto find the same functionality in SQL 2005. You can copy tables andviews but not the whole database. Is there another way of doing this?Our SQL database is hosted externaly and they recommend using theimport/export feature to do it. Does anyone know I can copy everything(such stored procedures, data table relations...etc)TanksMA.
View 1 Replies View RelatedHi group,
I don't know what I should try next, all of my tries have been without results in this case. I just wanted to create a temporary table, then insert one row into it and then use this row to compare if this row exists in another table or not. This is my script:
------------------------
CREATE PROCEDURE imp_Tippimport (@TGName char(33)) AS
BEGIN
CREATE TABLE #tTippgeber (
TGName2 char(33)
)
INSERT INTO #tTippgeber (TGName2)
VALUES (@TGName)
*** see below ***
IF NOT exists (select TGName FROM Tippgeber WHERE TGName = @TGName)
INSERT INTO Tippgeber SELECT TGName2 FROM #tTippgeber
/* SELECT * INTO Tippgeber FROM #tTippgeber */
ELSE
UPDATE Tippgeber SET
TGName = t.TGName2
FROM #tTippgeber AS t, Tippgeber
WHERE TGName = t.TGName2
END
GO
I also created a cursor and used a fetch in to a veriable in the line with the stars to see the value of TGName2 but it was NULL.
Any ideas ?!
Thanks for any help !!
Sascha
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT