This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
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?
Best Answer
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.
Hmm.. thankyou for the reply. What are your thoughts on the OPENQUERY functionality? Existing one uses it, mine doesn't use it..
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.
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.
