May 20, 2005 at 4:38 pm
Hey everyone,
I am running SQL Server 2000 (local) and have a Pervasive database on another server setup as a linked server. I need to update a Pervasive table using a join on a local table in a SQL Server stored procedure.
Is there a way to update a remote table by using the OPENQUERY function? If I use the following statement it updates okay (just takes FOREVER):
UPDATE RemoteServer.Database..Table
SET Field1 = B.Field1
FROM RemoteServer.Database..Table A INNER JOIN LocalTable B
ON A.Record_ID = B.Record_ID
The problem is my remote server table contains over 200,000 records and the link between the two servers is slow as ever.
I have gone through my stored procedures and changed my SELECT queries to use the OPENQUERY(remoteserver,'SELECT * FROM Table') and that has sped up the process tremendously when I am needing to query the Pervasive database from SQL Server. However, I have been getting an error message when I try to use the OPENQUERY function on the UPDATE statements. It says "could not fetch a row using a bookmark OLE DB provider".
Here is an example of what I am trying to do:
UPDATE OPENQUERY(RemoteServer, 'SELECT * FROM Table')
SET Field1 = B.Field1
FROM OPENQUERY(RemoteServer, 'SELECT * FROM Table') A
INNER JOIN LocalTable B
ON A.Record_ID = B.Record_ID
I get that error message when I use the OPENQUERY the target table and then try to reference the second OPENQUERY in the join statement.
Is there any way around this? Like I said earlier, I can get it to "WORK" using the first method but it is slow as ever and I have to find a way for it to process faster.
Thanks for any input,
KC
May 23, 2005 at 2:38 am
I don't do much openquery stuff, but it seems like the problem is that the entire remote table (200k) is brought over the wire before the join takes place.
You might also look at remote procedure calls. The idea is to create an insert proc on the remote server which you call with the needed parameters. Should perhaps be more efficient if your connection is slow and unreliable.
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply