demmick
asked on
Referencing data from another server in a Cursor
Hi all
I have a stored procedure which sets a cursor for data on a remote server. this is how I set the Cursor
declare data scroll cursor
for select ltrim(rtrim(emplid)) as emplid, convert(int,emplid) as empid, ltrim(rtrim(company)) as company, ltrim(rtrim(Last_name)) as Last_name, ltrim(rtrim(preferred_name )) as Preferred_name, ltrim(rtrim(name)) as [name], ltrim(rtrim(log_unit_cd)) as log_unit_cd, ltrim(rtrim(log_division_c d)) as log_division_cd, ltrim(rtrim(employee_statu s)) as employee_status, ltrim(rtrim(location)) as location from GPS5A.warehouse.dbo.Pims_i nterface where ltrim(rtrim(company)) = 'LUK' or ltrim(rtrim(company)) = 'LGC' or ltrim(rtrim(company)) = 'LUH' order by emplid
When I run the code I get the following error.....
Server: Msg 16953, Level 16, State 1, Procedure sp_update_staff_table_live , Line 54
Updatable keyset cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level.
How can I resolve this?
I have a stored procedure which sets a cursor for data on a remote server. this is how I set the Cursor
declare data scroll cursor
for select ltrim(rtrim(emplid)) as emplid, convert(int,emplid) as empid, ltrim(rtrim(company)) as company, ltrim(rtrim(Last_name)) as Last_name, ltrim(rtrim(preferred_name
When I run the code I get the following error.....
Server: Msg 16953, Level 16, State 1, Procedure sp_update_staff_table_live
Updatable keyset cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level.
How can I resolve this?
ASKER
No I don't need to keep it open on a remote table but if I create the temp table would it not slow down the sp?
Not a lot I doubt - it has to transfer the data from the remote server and you are using a cursor - anything else slowing things down is unlikely to be noticed above these.
Main advantage of the temp table over the cursor is that you are not forced to perform everything on a single row at a time.
Main advantage of the temp table over the cursor is that you are not forced to perform everything on a single row at a time.
ASKER
I have just chnaged the cursor type to INSENSITIVE and so far it seems to run. If its successful then I will let u know
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
demmick---> Please update and finalize all your open questions. If you need help in terms of splitting points, just comment with details or post a zero point question in the Community Support topic area with the link to the question and details.
Expert input is always welcome to determine the fair outcome of abandoned questions; such as this, if it remains "unattended" by asker for one more week.
Thank you,
Moondancer
Community Support Moderator @ Experts Exchange
Expert input is always welcome to determine the fair outcome of abandoned questions; such as this, if it remains "unattended" by asker for one more week.
Thank you,
Moondancer
Community Support Moderator @ Experts Exchange
Your responsiveness here is very much appreciated.
:)
Moondncer
Community Support Moderator @ Experts Exchange
:)
Moondncer
Community Support Moderator @ Experts Exchange
It have a lot less impact on the system and be safer if you could select the data into a temp table on the local server and work on that.