stored procedure altered the schema of the target table

  • Isaac Bar Nisan

    Old Hand

    Points: 356

    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

  • Ian Yates

    SSCoach

    Points: 19738

    Could you please post some of the relevant code?

    How many rows are you talking about?

  • Grant Fritchey

    SSC Guru

    Points: 395394

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ChiragNS

    One Orange Chip

    Points: 26137

    Hi

    I agree with Grant.

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

    "Keep Trying"

  • Isaac Bar Nisan

    Old Hand

    Points: 356

    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

  • Jeff Moden

    SSC Guru

    Points: 994238

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Johan Bijnens

    SSC Guru

    Points: 134253

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

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • opdaniel

    SSC Enthusiast

    Points: 147

    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

  • jcarsley

    SSC Enthusiast

    Points: 118

    @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.

  • Alan Browne

    Old Hand

    Points: 383

    submitted in error

  • Zolla Michalak

    SSC Enthusiast

    Points: 174

    Thanks @opdaniel. That worked for me also. 😀

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

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