September 19, 2006 at 8:56 pm
I have two identical tables on a local and a remote SQL Server.
The remote server is setup as a linked server.
I want to update the remote server from the local.
Update RemoteServer.remotedbname.dbo.myFile
Set XXX = LC.XXX
From localdbname.dbo.myFile LC
Where RemoteServer.remotedbname.dbo.myFile.KeyName = LC.keyName
This does not execute as RemoteServer.remotedbname.dbo.myFile.KeyName is a five part name which exceeds (the arbitrary) four part limit.
How do I code this update?
September 20, 2006 at 12:51 am
Try something like this instead...
update RT
set xxx = LT.xxx
from localdbname.dbo.local_table LT
inner join remoteserver.remotedbname.dbo.remote_table RT
on LT.keyName= RT.keyName
** Note the two aliases used!
September 20, 2006 at 6:28 pm
Thank you.
Your suggestion works - but the time sucks. To perform the join SQL Server retrieves ALL of a very large table from the client and (I am told) performs the remote update without the benefit of client indexes.
I canceled the update of 1000 records after 40 minutes!. When running a pull operation from the client the same script runs in under 1 minute.
Strangely, there is no problem with the pull operation as the remote references in UPDATE FROM all appear in the from clause - which supports the table alias syntax.
UPDATE localTable
FROM remoteServer.remotedb.dbo.remoteTable as RT
Where RT.Key = localTable.key
The MS SQL Team need to do some urgent work on this aspect of T-SQL.
At the least the UPDATE and DELETE operations should support the table alias syntax or five part names should be allowed.
The performance also needs an urgent review. It is unacceptable that updates to a remote server run orders or magnitude slower than they need do.
Given that client side pull is much faster we are looking at remote execution of a stored procedure on the client from the server.
exec (..) AT servername AS login='loginname'
1. Even though we supply a loginname we get a message that anonymous login is not allowed.
2. The servername must be a constant -- a variable is not supported. (What was MS thinking? We have dozens of locations. Do we have to hardwire dozens of exec statements?)
What a mess.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply