Nested Insert Exec Statement
Feb 7, 2008
I am using the following statement in a SP.
EXECUTE (' INSERT INTO #OutPut
EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units
)
AND IN h_DailyDividend I am using the following statement.
EXECUTE (' INSERT INTO #TickerTable
EXEC h_SecMstr_SecMap_TQAExch_Info
@IDList = '+@TickerTable+',
@IsTable = 1,
@Type = 0,
@OutPutFormat = 0,
@VenType = 14 ')
And i am getting the following error.
Message: An INSERT EXEC statement cannot be nested.
Can any body help me out how to solve this problem.
Regards
Sulaman
View 4 Replies
ADVERTISEMENT
Sep 19, 2007
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
View 1 Replies
View Related
Apr 12, 2006
i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error
An INSERT EXEC statement cannot be nested.
heres the actual insert code:
set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'
INSERT INTO reportData
exec (@sqlString)
ive tried just a simple :
insert into reportdata
exec scoreGetLines @customerId,@programID...........
that still doesnt work. same error. how can this be sorted
View 13 Replies
View Related
May 3, 2004
HI,
WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.
BUT WE ARE GETTING A FEW HICCUPS. PLS HELP
THIS IS HOW IT GOES :-
CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN
CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP2
EXEC MY_PROC1
ST1 .........;
ST2..........;
END
THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED
NO PROBLEM TILL NOW.......
BUT,
CREATE PROCEDURE MY_PROC3
AS
BEGIN
CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP3
EXEC MY_PROC2
ST1 .........;
ST2..........;
END
THEN IT GIVES AN ERROR AS :-
"An INSERT EXEC statement cannot be nested."
CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........
WHAT IS THE NESTING LEVEL OF A PROCEDURE ?
IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
PLS HELP ME OUT IN THIS
THANKS
View 13 Replies
View Related
Nov 28, 2005
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
View 2 Replies
View Related
Jul 24, 2006
Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert 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 SPexecutes 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 sameparameters 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 .
View 1 Replies
View Related
Jun 16, 2006
Hi All,
I am having a problem with nested insert exec. Say for example I have three stored procedure procA, procB and procC.
I am executing procedure procC in procB; I am storing the values returned by procC in a tempTable. The code is
INSERT INTO #Temp
EXEC procC
And in procA, I am executing procB, and the values returned by procB are stored in another temp table. The code is
INSERT INTO #TempOne
EXEC procC
When I execute the procA, I am getting error as
An INSERT EXEC statement cannot be nested.
My requirement is like this, please give me a solution.
Thanks
View 5 Replies
View Related
Jan 17, 2007
Greeting.
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.
P.S.S. Sorry for bad english.
View 1 Replies
View Related
Aug 22, 2002
I would like to execute something like that with sql6.5 :
select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (cmd)
I tried like this but I don't know what's wrong
exec ("USE "+ RTRIM(@dbname) +"exec sp_helprotect")
Thank you
View 1 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 1 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 3 Replies
View Related
Dec 16, 2007
declare @rej_nm as varchar(50)
exec('select count(*), ' + "'@rej_nm'" + 'from ' + @rej_nm)
trying to return the rowcount AND the name of the object. return set should look like this:
12, tbl_name
i've tried single quotes around @rej_nm, nested single quotes, single/double nested, etc..
instead, i get an error stating that @rej_nm is not a valid column of tbl_name. bottomline...
how do i return a variable 'value' within a select statement...
thanks!
View 3 Replies
View Related
Aug 1, 2007
Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:
EXEC(SELECT QRY FROM temp_Update)
Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:
Msg 156, Level 15, State 1, Line 123Incorrect syntax near the keyword 'SELECT'.Msg 102, Level 15, State 1, Line 123Incorrect syntax near ')'.
Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.
View 9 Replies
View Related
Jun 20, 2006
Hi everyone,
What is EXEC statement ?? What is the usage and purpose of it ??
It is really difficult to find ant resource about this keywords that's why I would like you to help me.
Thanks
View 1 Replies
View Related
Sep 19, 2005
Hi Guys, I have been trying to get this SQL string below to run, but for some reason I carry on getting an error near the EXEC Statements.
Does anyone have any idea what I doing wrong?
CREATE PROCEDURE [dbo].[UpdateWarnings]
@EndDate DateTime
AS
DECLARE @iid varchar(100)
DECLARE @fid varchar(100)
SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')
IF @iid <> ''
BEGIN
EXEC('UPDATE memorial SET active=0 WHERE id IN ('+ @iid +')')
END
SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')
IF @fid <> ''
BEGIN
EXEC('UPDATE memorial SET warning=(warning+1) WHERE id IN ('+ @fid + ')')
END
IF @enddate <> ''
BEGIN
UPDATE warnings SET startdate=getdate(), enddate=@enddate, warnings=(warnings + 1) WHERE id=1
END
GO
View 1 Replies
View Related
Jan 2, 2007
hi,
can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..
Select column1, coulumn2, (select EXEC SP_NAme), blabla
thanks in advance... :)
View 6 Replies
View Related
Jan 8, 2007
I am trying to run this simple nested sql statement, just wondering what am i doing wrong.
Appreciate any help
SELECT dtl_doccode, dtl_prodid, sum(TotalItems)
FROM
(
SELECT
dtl_doccode,
dtl_prodid,
dtl_prodquantity,
uomd_packing,
TotalItems = dtl_prodquantity * uomd_packing
FROM tblDocumentDetails
Inner Join tblProducts ON tblDocumentDetails.DTL_ProdId = tblProducts.PROD_Id
Inner Join tblDocuments ON tblDocumentDetails.DTL_DocId = tblDocuments.DOC_Id
Inner Join tbluomdetails On tblDocumentDetails.dtl_produomdid = uomd_id
WHERE
tblDocumentDetails.DeletedFlag = 0
AND DOC_TypeId = 20
AND DTL_DocId = 451
AND PROD_Id = '17'
)
GROUP BY dtl_doccode, dtl_prodid
View 3 Replies
View Related
Oct 2, 2007
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
View 7 Replies
View Related
May 14, 2001
Hello,
I need to get the count into a local variable:
Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause
Exec(@SQL)
It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.
Any help greatly appreciated,
Judith
View 4 Replies
View Related
Sep 5, 2007
Hi!
Is it possible to call proc with parameter in openrowset?:
declare @str varchar(100);
select @str = 'EXEC SP_HELPTEXT ''createReport''';
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str)
AS a;
Incorrect syntax near '@str'.
------------------
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC
SP_HELPTEXT ''createReport''') AS a;
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE
DB provider "SQLNCLI" for linked server "(null)" indicates that either
the object has no columns or the current user does not have
permissions on that object.
Best regards
B. D. Jensen
View 6 Replies
View Related
Apr 16, 2014
I'm fairly new to SQL. I have a function in Excel that I'm trying to translate to SQL to run my query.
Basically what i want to do is if Date1 and Date2 are blank then display Date Not Tracked.
Else if Date1 is blank however Date2 has a value then pull Date2 other wise Date1
This is my Excel function:
=IF(AND(A2="",B2=""),"Date Not Tracked",IF(A2="",B2,A2))
A2 = Product.Date1
B2 = Product.Date2
View 5 Replies
View Related
Jan 21, 2004
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz
View 2 Replies
View Related
Apr 19, 2007
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
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
View 8 Replies
View Related
May 17, 2001
I nested a case to 15 levels and got an error message saying I could only nest it to 10. Is there a way to increse this??
TIA
Adam
View 3 Replies
View Related
Dec 27, 2007
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')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')
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.
Any help would be appreciated
View 3 Replies
View Related
Oct 23, 2007
Hi,
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.
Thanks all,
Anand
View 11 Replies
View Related
Nov 9, 2007
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.
help if you can.
View 7 Replies
View Related
Jul 20, 2005
I have a trigger on a table. I am trying to dynamically log thechanged fields on the table to another table, so I am iteratingthrough the bits in COLUMNS_UPDATED() to find what's changed, andgetting the column name programatically. This is all working fine.If I do a regular insert command in my trigger then everything worksfine. However, since I want to retrieve data from the column namewhich I got programatically from the inserted and deleted tables (toget the old and new values) I wanted to do something like this:insert into auditTransactionLog (TableName,PrimaryKeyId,ColumnName,OldValue, NewValue, ActionType) EXEC( 'SELECT(''cmContactInfo''), I.contactID,'''+ @colname+''', D.'+@colname+',I.'+@colname+', '+@action+' FROM inserted I INNER JOIN Deleted D onI.ContactId = D.ContactId')The presence of this line of code appears to be preventing theupdating of the table with the trigger. Is there some reason why Ican't do the EXEC in the trigger? If I did it without EXEC it worksfine but I have no idea of getting at the D. and I. @colname columnsotherwise.Thanks for any help!Rebecca
View 1 Replies
View Related
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 Replies
View Related
Sep 18, 2007
Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc
The follwing statement works fine if i give the table name directly in the query
Code Snippet
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below
Code Snippet
@Category - Will be passed as a parameter to the stored proc
DECLARE @vsBulkSQL VARCHAR(MAX)
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'
Print @vsBulkSQL - This prints the folliwing statement
INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
Exec @vsBulkSQL - This statement gives the following error
The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same
~Mohan
View 4 Replies
View Related
May 25, 2008
All- I'd be interested if any of you could advise as to how the nested CASE statement below could refactored to be more concise. (Works fine as is, but just interested to know!) Thanks!
1 SELECT headcount.person_id, person.last as Lname, person.first as Fname, 2 3 CASE headcount.coming WHEN 'False' THEN '0' ELSE 4 5 CASE person.is_adult WHEN 'True' THEN 6 7 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_parent 8 ELSE activity_session.usd_per_guest_adult9 END10 ELSE11 12 CASE person.is_kid WHEN 'True' THEN13 14 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_kid15 ELSE activity_session.usd_per_guest_kid16 END17 END18 END19 20 END AS 'Cost', 21 22 23 24 25 no_answer, not_coming, coming, wanted, comment, activity_session.act_session_id, for_sale, headcount_id FROM headcount26 27 INNER JOIN person28 ON headcount.person_id = person.person_id29 30 INNER JOIN activity_session31 ON headcount.act_session_id = activity_session.act_session_id
View 2 Replies
View Related
May 18, 2015
I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
View 2 Replies
View Related
