Post reply

stored procedure altered the schema of the target table

  • February 21, 2008 at 3:53 am

    #183258

    Hello I have a problem when I execute a stored procedure that call other stored procedure that return a result set and in the first stored procedure the data is insert into a table I get this error

    "INSERT EXEC failed because the stored procedure altered the schema of the target table." but when I call the stored procedure with less data it work ok what can I do to solve this problem

  • February 21, 2008 at 4:23 am

    #780574

    Could you please post some of the relevant code?

    How many rows are you talking about?

  • February 21, 2008 at 6:54 am

    #780636

    It can't possibly be caused simply by the number of rows. Inserting one row or one million, neither one changes the schema of the table. That procedure you're calling has other code in it that is making that change.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 22, 2008 at 4:17 am

    #781032

    Hi

    I agree with Grant.

    Is the procedure calling any other procedure before inserting the rows?

    "Keep Trying"

  • February 24, 2008 at 2:24 am

    #781417

    this is the code

    1) EXEC [dbo].[s_tenuot_base_r]'01.01.2006','01.31.2006', 1

    2) EXEC [dbo].[s_tenuot_base_r]'01.01.2006','03.31.2006', 1

    and it generate an error only in the second option because the range

    of date it significant from the first and that it because I send only one month so it work but with more then that it fail

    insert into #tenuot

    exec s_tenuot_base_logi @m_tarich,@a_tarich,@sug_yechida,@tarich_doc,@mispar_doc,@miun

  • February 24, 2008 at 9:13 am

    #781426

    You'll probably need to post the code for the s_tenuot_base_r stored procedure...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 24, 2008 at 9:47 am

    #781428

    So is your #tenuot being altered by s_tenuot_base_r in some cases ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 19, 2008 at 9:46 am

    #874337

    I think your first stored procedure creates a #table and the second stored procedure creates another (temp) table having the same name.

    Or, you are trying to insert the records retrieved by the stored procedure in a table having the same name as the (temp) table created by your stored proc.

    Try changing the temp table names and see if it works.

    /daniel

  • April 1, 2011 at 9:14 am

    #1306559

    @opdaniel - Thanks! That worked. I was giving my temp table the same name as the one in the stored procedure. Glad I read this thread until the bottom.

  • May 31, 2016 at 4:49 am

    #1882117

    submitted in error

  • November 2, 2016 at 4:51 pm

    #1910252

    Thanks @opdaniel. That worked for me also. 😀

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply