none
Stubborn Stored Procedure RRS feed

  • Question

  • Hi everybody,

    I think I saw similar questions here in the past, but now it's my turn. I have a stored procedure that has the following code at the top of it

     IF OBJECT_ID('TempDb..#AccountInvoices', N'U') IS NOT NULL DROP TABLE #AccountInvoices;
    
        CREATE TABLE #AccountInvoices (invoice_no numeric(17,0) NOT NULL,
         Descrip VARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL,
         hidden BIT NOT NULL, finalized BIT NOT NULL, Created DATETIME NULL,
         Balance MONEY, Rn Int);
    
         DECLARE @Sql NVARCHAR(MAX);

    Then it uses dynamic SQL and executes insert into that temp table and finally selects from it.

    Originally there was a bug in that table definition (it was 25 characters instead of 100). I since then corrected the problem and if I execute this procedure from SSMS it works fine and returns result correctly. However, when I attempt to run the application against that same DB, this procedure produces a "String or Binary String will be truncated" error. I already attempted both to run the WITH RECOMPILE and EXEC sp_recompile for this procedure and nothing helped.

    I guess I can try dropping and re-creating this procedure now, but why do I get the issue at all? Why the application seems to access old version of the procedure which is clearly already fixed? How can we avoid this situation in a future?

    Thanks in advance.

    UPDATE. Even after I dropped this SP I still got the same exact error. At that time I looked closer and found a very similar procedure with that exact problem. I'm guessing that this procedure is executed and not the one I was fixing. I'll fix this one directly and hopefully this is it.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    Monday, February 11, 2019 9:10 PM
    Moderator

Answers

  • Bit column can not be a reason for that error. It is the description column. Turned out (as I wrote in my UPDATE) that it was a different procedure, not the one I already fixed. Once I found it and fixed, it was it. But it was very funny that I was so sure that the procedure I was testing was the culprit - I looked into it with my colleague tester, did all the vudu tricks I could have thought of and finally decided to ask advice here. And only after that I finally noticed that other procedure :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, February 12, 2019 3:52 AM
    Moderator

All replies

  • But your first line to check the existence for the temp table in a stored procedure is doing nothing. 
    Monday, February 11, 2019 9:33 PM
    Moderator
  • But your first line to check the existence for the temp table in a stored procedure is doing nothing. 

    Yes and no. The paranoid programmer thinks that the caller may have created a temp table with the same name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 11, 2019 11:17 PM
  • Hi,

    You have several bit columns. Most likely one of them getting more than its length and hence the message maybe.

    Tuesday, February 12, 2019 12:55 AM
  • Not sure why you're saying it. When I'm about to create a temp table in my procedure I always have this line preceding its creation.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, February 12, 2019 3:49 AM
    Moderator
  • Bit column can not be a reason for that error. It is the description column. Turned out (as I wrote in my UPDATE) that it was a different procedure, not the one I already fixed. Once I found it and fixed, it was it. But it was very funny that I was so sure that the procedure I was testing was the culprit - I looked into it with my colleague tester, did all the vudu tricks I could have thought of and finally decided to ask advice here. And only after that I finally noticed that other procedure :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, February 12, 2019 3:52 AM
    Moderator
  • Bit column can not be a reason for that error. It is the description column. Turned out (as I wrote in my UPDATE) that it was a different procedure, not the one I already fixed. Once I found it and fixed, it was it. But it was very funny that I was so sure that the procedure I was testing was the culprit - I looked into it with my colleague tester, did all the vudu tricks I could have thought of and finally decided to ask advice here. And only after that I finally noticed that other procedure :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    OK. Since you have clarified the reason of the issue, please close the thread by marking your reply as answer.

    I feel that we're probably out of your expected environment, couldn't guess all kinds of possible scenario.:-)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 14, 2019 7:30 AM
    Moderator