July 25, 2019 at 9:43 am
Hi,
I've come across a problem that results in an error that I've never seen before and I cant seem to find any articles online about what it is, why it happens and how I may go about resolving it.
The set up;
A SQL 2017 server patched up to the latest CU and CVE has several databases on it, of these we have a procedure in DB1 which essentially loops over in a cursor and runs a procedure in DB2 passing in a parameters. It will run several of these cross database executions but then after about 6 or 7 it errors with this
Msg 614, Level 16, State 1, Procedure <Procedure on DB1> Line 37 [Batch Start Line 0]
Could not locate bookmark due to data movement.
I'm trying a few things out, like getting rid of the cursor (not my code) and replacing it with a temp table and a loop but I wondered if anyone had come across this before?
Any help, comments would be appreciated.
Thanks,
Nic
July 25, 2019 at 11:37 am
Personally i really would be looking to get rid of the CURSOR first; SQL Server performs awfully at iterative tasks and is far better at set based ones. If you need to pass multiple sets of parameters to an SP, sounds like you might want a table-value parameter.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 25, 2019 at 12:50 pm
Hi,
Yes don't worry the cursor is going, top of my list for this process that I've just inherited, sadly the TVP for the procedure wont work as its a 3rd party procedure and we cant change there code without affecting our SLA.
Hopefully the cursor to temp table will make things better.
Thanks for the reply. It is appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply