INSERT EXEC Statement Cannot Be Nested

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

An INSERT EXEC Statement Cannot Be Nested.

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

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 View Related

ERROR:- An INSERT EXEC Statement Cannot Be Nested.

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

An INSERT EXEC Statement Cannot Be Nested Error.

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

Problem With Insert Exec In Nested SP

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

Problem With Nested INSERT EXEC

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

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

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

EXEC Statement

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

Exec Statement

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

Exec Statement

Mar 23, 2004

View 3 Replies View Related

Exec() Statement

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

EXEC Statement

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

EXEC Statement

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

Error Near Exec Statement

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

Exec In Select Statement

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

Help : Nested Sql Statement

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

Stored Procedure Exec An SQL Statement

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

SProc - Ad Hoc Sql Statement With COUNT For Exec(@SQL) ??

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

Using Single Quotes In EXEC Statement

Mar 27, 2001

View 1 Replies View Related

Openrowset With Parameter In Exec Statement?

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

Nested Case When Statement

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

Insert Into...exec-- Help

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

INSERT-EXEC

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

Nested CASE Statement Limits??

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

Conditional Insert Without Using Exec

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

Insert Exec Reliability

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

Insert Into Exec Distributedprocname

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

Trigger With Exec Statement Blocking Execution Of Triggering Cmd?

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

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure

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

Error While Executing A A Query String Using EXEC Statement

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

Interested If This Nested CASE Statement Can Be More Concise...

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

Transact SQL :: Nested Select Case Statement

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