odd error message in function in a proc

  • tcronin 95651

    SSCertifiable

    Points: 6735

    I have a cursor which has calls a function (sorry for  design this is a 3rd party db).  Odd part is there are 10 rows in the cursor select, only every other row gets updated, next row gets error below.  This happens no matter what the sort order is, one row works, next row errors, next row works, next row errors until all 10 rows are done

     

    Msg 7202, Level 11, State 2, Line 26

    Could not find server 'update images set image_data=(select myimage' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

  • rVadim

    Hall of Fame

    Points: 3922

    Is UPDATE statement being dynamically constructed? If so, print it out and see if it's correct.

    --Vadim R.

  • ScottPletcher

    SSC Guru

    Points: 98206

    You'd need to post the actual code that processes the cursor for us to really accurately help.

    But, likely an extra FETCH is being issued.  People insist on coding multiple FETCH statements when processing a cursor, and doing the FETCH at the end of the loop, and that can cause issues.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

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

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