Solved

Referencing data from another server in a Cursor

Posted on 2001-08-17
7
360 Views
Last Modified: 2012-06-21
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_cd)) as log_division_cd, ltrim(rtrim(employee_status)) as employee_status, ltrim(rtrim(location)) as location from GPS5A.warehouse.dbo.Pims_interface 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?

0
Comment
Question by:demmick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6396394
Do you need to hold the cursor open on the remote table?
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.
0
 

Author Comment

by:demmick
ID: 6396419
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?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6396481
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:demmick
ID: 6396492
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
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 35 total points
ID: 6396560
The insensitive cursor is a snapshot so should get all the data onto the local server like the temp table (but more restrictive in processing terms as I pointed out). It's worth checking - if it doesn't then it's probably not a good idea to use it (but then it's never a good idea to us a cursor on a relational database).
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6777892
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
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6784210
Your responsiveness here is very much appreciated.
:)
Moondncer
Community Support Moderator @ Experts Exchange
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question