I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .
Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .
I have defined a # table in Parent SP and calling a Child SP like this
insert into #temp exec childsp ......
Child SP has Select * from local # temp table ( local to child SP
) as the last statement .
When number of records are less ( around 1000 - 5000) Parent SP
executes but slow .
When the Child SP returns higher number of rows ( 1,00,000 or more )
the SP will be running for hours with out completion .
Although executing the child SP , with exec ChildSP .... with same
parameters it is completed in 2 mins for 3,00,000 rows .
Resolution : - Define a temp table (say #tempChild ) in the Parent SP
..
In the Child SP instead of select * replace with insert into
#tempChild select * from ...
Also note that this problem is not noticed in SQL 2000 Server with SP4
..
This may be due to SP executing in implicit transactions .
I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure, When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.". In Fire bird /Interbase store procedure we can nested. Below are the code; declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) Insert Into @dtReturnData Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES' Select doccode, docdate, debtoraccount From @dtReturnData Inside the GetPickList It will do like this, but most of the code I not included; ALTER PROCEDURE GETPICKINGLIST @doctype nvarchar(2), @datefrom datetime, @dateto datetime, @includegrn char(1), @includesa char(1), @includedata nvarchar(5) AS BEGIN declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) IF (@DOCTYPE = 'SI') BEGIN Insert Into @dtSALESINVOICEREGISTER Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END ELSE BEGIN Insert Into @dtDELIVERYORDERREGISTER Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END Select doccode,docdate,debtoraccount From @dtReturnData END
So how can I select a nested store procedure? can someone help me
Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
Code is really not important. Any sys SP can replace the above code.
I am getting the following error Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80 An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue, http://www.sommarskog.se/share_data.html But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------ I think, therefore I am - Rene Descartes
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT. Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable (Col1 int NULL, Col2 varchar(50) NULL) GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG= 'TestDB' AND TABLE_NAME ='TestTable' AND COLUMN_NAME= 'Fred')
This results in an "Unknown column" error. What am I missing here and how can I accomplish it. I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
I have a stored procedure that transfers data from one database to another. I do a lot of manipulation to the data in between because although both databases are for the same purpose, the database designs are different. The two databases exist on two different servers. Till now my transfer procedure used a linked server to pull the data from one server to the other. This process takes a ton of time with complex queries across linked servers. I was researching on a faster method to transfer data and the only other working method seemed to be the insert..exec statement with which I can call a remote stored procedure which would run and return the results of the complex queries which I can then use locally as I wish.
My main question is, can I rely on insert..exec to work consistently and/or is there another method to achieve what I need that I'm totally ignorant of? I already know of inline/multi table value functions (they wont work across servers); openquery/openrowset (I need to be able to pass parameters); creating permanent tables to be used and then destroyed (not very appealing). I read an excellent article about these online by Erland Sommarskog (http://www.sommarskog.se/share_data.html) but I'm hoping someone can explain a little further.
There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.
has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.
eg
create table #fred (IntColumn int)
insert into #fred exec ProcThatSelectsAnIntColumn
This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.
I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.
I can't find any references in knowledgebase.
Any pointers appreciated.
Cheers Simon ________________________ Simon Davis Bankers Trust Australia Limited Asset Management Technology Ph: 61 2 9259 9137 <mailto:Simon.Davis@Bankerstrust.com.au>
I have an sp that sends cdomail which requires 4 variables. I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?
This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'
-- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling. IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END
-- Do some error handling after each step if you have to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg
I have written a master proc which calls another proc (say proc1). This proc1 has insert-exec statements, for eg insert into #temp exec proc1. i.e. multiple times the proc would be nested.
This the err thrown : An INSERT EXEC statement cannot be nested.
Hi When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....). Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question: Is there a way to INSERT INTO table EXEC dataProc *Without* making a temp table that takes in every single column the dataProc puts out?
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated
I am running the DMX below and I am getting an error we I go to train my structure. It is probably something stupid but I do not see the problem. It seems like the SKIP isn't being recognized but I am not sure. Here is the error:
INSERT INTO error: The '[MSA].[HospitalID]' nested table key column is not bound to an input rowset column. DMX: CREATE MINING STRUCTURE [Hospital_Structure] ( [HospitalID] LONG KEY, [SponsorshipTypeID] LONG DISCRETE, [GeographicTypeID] LONG DISCRETE, [CaseMixIndex] DOUBLE CONTINUOUS, [PercentGovtPayers] DOUBLE CONTINUOUS, [TotalNumberInpatientCases] LONG CONTINUOUS, [MSA] TABLE ( [HospitalID] LONG KEY, [MSAGroupMember] TEXT DISCRETE ) ); GO
ALTER MINING STRUCTURE [Hospital_Structure] ADD MINING MODEL [Hospital_Model] ( [HospitalID], [SponsorshipTypeID], [GeographicTypeID], [CaseMixIndex], [PercentGovtPayers], [TotalNumberInpatientCases], [MSA] ( [HospitalID], [MSAGroupMember] ) ) USING Microsoft_Clustering; GO
INSERT INTO MINING STRUCTURE [Hospital_Structure] ( [HospitalID], [SponsorshipTypeID], [GeographicTypeID], [CaseMixIndex], [PercentGovtPayers], [TotalNumberInpatientCases], [MSA] (SKIP, [MSAGroupMember]) ) SHAPE { OPENQUERY([localhost],' SELECT [HospitalID], [SponsorshipTypeID], [GeographicTypeID], [CaseMixIndex], [PercentGovtPayers], [TotalNumberInpatientCases] FROM [dm].[vw_HospitalClustering_Inputs] ORDER BY [HospitalID]') } APPEND ( {OPENQUERY([localhost],' SELECT [HospitalID], [MSAGroupMember] FROM [dm].[vw_HospitalClustering_InputsNested] ORDER BY [HospitalID], [MSAGroupMember]') } RELATE [HospitalID] TO [HospitalID] ) AS [MSA]
I have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.
When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]
But regular linked server calls (directly to tables) and openquery calls work fine from either server...
Eg insert into #tmp Select top 5 first_name, last_name from ServerA.db1.dbo.people
--and this works also
insert into #tmp Select * from openquery(ServerA, 'Exec db1.dbo.test')
Both servers (B & C) appear to be configured the same, and I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables. All objects are dbo owned and execute permission has been given to all users. Can ant one help? Rob
When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"... Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
declare @TableName Varchar(255);--Just For Testing---DELETE!! declare @Filename varchar(255); --Store Distinct filename declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units declare @SumValue Varchar(255); Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName Set @SumUnits = 'select sum(Units) from ' + @TableName Set @SumValue = 'Select sum(Value) from ' + @TableName
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate, UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
