December 30, 2009 at 12:00 pm
I'm going through our existing sprocs (which we don't own rights to modify) and writing sprocs to call them & add some fields to the data returned. Works well for many until I hit this one:
Msg 3916, Level 16, State 0, Procedure ProcedureNameSp, Line 188
Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first.
I don't have a transaction in my SP. I simply declare a table variable, exec the sproc, pass in the parameters, and get the data back. So far I haven't done anything like add fields or modify the returned data.
Like I said, I've done this a bunch of times all ready in the last few weeks, but this one won't work for me. Can anyone point me to some reading material? I've searched online help but find nothing. Thanks!
December 30, 2009 at 12:41 pm
does the proc you're calling have a commit in it? Can you post some code so we can see?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2009 at 2:26 pm
It does, but I don't know if I'm legally allowed to post the code. It's part of a big system we "Purchased" but we don't "own" the code and can't directly modify it.
December 30, 2009 at 2:35 pm
Can you call the proc directly, using the required input values?
Completely different direction:
Are you sure the proc you're calling is used by the 3rd party application at all? Maybe it's a non-working "left over"...
December 30, 2009 at 2:39 pm
Have you tried wrapping the insert exec itself in a transaction ?
December 30, 2009 at 2:42 pm
Thank you for your interest!
Yes, I can execute it from SSMS, provide parameters, and get data back. I can also run it from the application form using the same parms and get the same data.
My users run it many times a day, but need a couple more fields. Thus the new SP to call it and (when I can get it to run), add fields.
Here's what their support told me:
You may be running into trouble because of the fix for Issue 109194 which added explicit transaction block handling.
The error is implying that the user is trying to COMMIT a transaction that was not started in that Stored Procedure.
If this is correct you may be encountering a SQL limitation.
December 30, 2009 at 2:50 pm
Have you
tried wrapping the insert exec itself in a transaction ?
This is something I probably need to learn how to do! I've never used (never had to use) transactions.
Here's my little SP. I removed the table definition and parm list. Hope I didn't remove too much.
Thanks,
USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyCallingProcName]
(
@Parm)
AS
-- declare a temp table that matches the output of the standard stored procedure.
declare @Results table (
field datatype
,field datatype)
INSERT INTO @Results (
Field
,field )
EXEC dbo.[TheirProc]
@Parm
SELECT *
FROM @Results
December 30, 2009 at 3:06 pm
Try
Begin Transaction
exec MyCallingProcName @Param ="Whatever"
Select 'TransCount',@@TranCount
Commit
Does that work or error ?
December 31, 2009 at 8:16 am
Thank you for your suggestion. I ran it from SSMS like this - is that what you intended me to do?
Begin Transaction
--exec MyCallingProcName @Param ="Whatever"
EXEC [dbo].[MyCallingProcName]
ABCD, --@Whse
200001,-- @PoNum
'12/29/2009',--@Date
0, --@Post
0, --@Parm
0, --@Parm2
0 --@Parm3
Select 'TransCount',@@TranCount
Commit
Unfortunately I get the same error message.
The good news is that, after maintaining that it couldn't be a problem with their code, the code vendor has now sent me another SP to test. If it runs, they will log a bug. We will test it today and post the results here.
I appreciate all the interest and suggestions from everyone, you guys are great and I've learned a lot from you over the years!
December 31, 2009 at 9:53 am
The software vendor supplied a fixed version of their SP, which runs from my calling SP. The moral of the story is if it runs by itself, that's no guarantee it will run when called by another SP.
Thank you all for your interest and attempts to help! Happy New Year!
January 3, 2010 at 12:01 am
Sounds like your vendor fixed the issue.
As the others mentioned, this can be caused by a second BEGIN TRANSACTION occuring before the first is commited. However, your should be aware that table level triggers which are not neccessarly part of the store proceedure can also contain these transactions. Triggers usally are proceedures/pieces of code the run when a date in a table is inserted, updated or deleted.
Just something to keep in mind for others who may have this same issue.
Helps this Helps - Mark Mathews
January 4, 2010 at 7:52 am
Good point, we have many table triggers. I'm glad the vendor took ownership of this.
Thanks!
May 8, 2015 at 12:38 am
Hi Dave,
I am also stucked in similar problem and the query which you have suggested I have executed and I have got the result
Transcount 1
but if I am executing the query to insert into temp table it gives me the error
Msg 3915, Level 16, State 0, Procedure PROC_D_DISPATCH_CREATE_JOB_AND_COMBINE_RIDES, Line 141
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
Please suggest
Thanks in advance
Patel Mohamad
May 8, 2015 at 2:58 am
Please post new questions in a new thread. Thank you
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply