question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

What is INSERT EXEC SQL Exception?

I was discussing with a senior colleague of mine about a design technique existing for a stored procedure. Here is a sample of it - USE [Alpha] GO ALTER PROCEDURE spCalculations ( @AccountID INT, @Rate FLOAT, @EffectiveDate DATE -- Few other variables ) AS BEGIN -- Creation of #TempData code here -- Non Clustered Indexing of columns of #TempData code here /** Current Technique to insert data** Declare @CmdString1 varchar(2000) Declare @CmdString2 varchar(2000) Set @CmdString1 = 'execute [Omega].dbo.spLINK_GetData' Set @CmdString1 = @CmdString1 + convert(varchar(15), @AccountID) + ', ' Set @CmdString1 = @CmdString1 + convert(varchar(15), @Rate) + ', ' Set @CmdString1 = @CmdString1 + convert(varchar(15), @EffectiveDate) Set @CmdString2 = ' INSERT INTO #TempData ( AccountID, Rate, EffectiveDate ) Select * From OPENQUERY (MasterLinkedServer, ''' + @CmdString1 + ''') ' EXEC (@CmdString2) **/ /** My proposed Technique towards Insertion ***/ INSERT INTO #TempData ( AccountID, Rate, EffectiveDate ) EXEC MasterLinkedServer.[Omega].dbo.spLINK_GetData @AccountID, @Rate, @EffectiveDate -- Other logic for the procedure continues... END GO My colleague retorted that with my proposed technique, there is a possibility that you will end up with "INSERT EXEC SQL Exception" since spCalculations procedure is called again and again from one of its child procedure in a loop. Can anyone please help me in understanding in detail what do we mean by the INSERT EXEC SQL Exception?
sql-server-2008exceptioninsert-exec-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

David Wimbush avatar image
David Wimbush answered
Funny, I just had an issue with that pattern this morning. Something about how it failed to set up a distributed transaction. Initially I fixed it by changing the linked server's option "Enable Promotion of Distributed Transactions for RPC" from True to False. Then I realised I didn't have time to research the side-effects of that so I set it back and changed the stored procedure to return output parameters instead of a row set. This worked for me because I was looking for just the latest row, not a set of rows. Mind you, I can't see any functional difference between the current code and what you propose except your version is more readable. If your colleague is talking about some kind of recursion error, I can't see any reason why the current code is safe and your version is not. I suggest you ask your colleague to clarify. "INSERT EXEC SQL Exception" is not a term I've heard of.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hmm.. thankyou for the reply. What are your thoughts on the OPENQUERY functionality? Existing one uses it, mine doesn't use it..
0 Likes 0 ·
Sorry, I missed that difference! I'm not too sure how OPENQUERY works. My first choice would be to do it the same way you suggest, with a stored proc.
0 Likes 0 ·
Thanks David. I have dropped a note to my senior colleague to explain more about "INSERT EXEC SQL Exception". Let's see what he has got.
0 Likes 0 ·