error using linked server and EXECUTE AT

Question

error using linked server and EXECUTE AT

Guras

SSCertifiable

Points: 6518
More actions
August 14, 2012 at 2:38 pm

#279682

I am using sql 2008 R2
I ran the following on server A
select name from sys.servers
The "linkserverB" exists ( linking server B from A)
However the following step of a stored proc on server A gives the error
execute ('Update n
SET upload = 1
FROM mydb1.dbo.table1 n
INNER JOIN linkserverA.mydb2.dbo.table2 n1
ON n.ptno = n1.ptno
WHERE n.upload = 0
AND n.deleted = 0') AT linkserverB ;
error
Could not find server 'linkserverB' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
I have used that linkserver in all other stored proc ( not using Execute AT) and it works..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

lets double check some settings...by abstracting out your code to be anonymous (SERVERA and SERVERB, something got lost, i think.

first, i don't think its necessary to use EXECUTE AT.

you can use an alias just like you were using.

the syntax you are using for update requires the SERVERB to be a SQL server...

the command you are using requires SERVERB to have a linked server to SERVERA

from what you posted, the opposite is true..SERVERA has a linked server, but not necessarily the reverse.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Guras SSCertifiable Points: 6518 More actions · Answer 2

Lowell (8/14/2012)
lets double check some settings...by abstracting out your code to be anonymous (SERVERA and SERVERB, something got lost, i think.
first, i don't think its necessary to use EXECUTE AT.
you can use an alias just like you were using..

When using alias, the sp is running really slow, sometimes causing a block on this very update statement

the syntax you are using for update requires the SERVERB to be a SQL server...
the command you are using requires SERVERB to have a linked server to SERVERA

Yes Server B has a linked server back to ServerA

from what you posted, the opposite is true..SERVERA has a linked server, but not necessarily the reverse.

Lowell SSC Guru Points: 323518 More actions · Answer 3

linked servers are slow for updates; it's usually because the far table (which might be huge) gets copied over into a local temp table, then the data is merged for the update, adn then the commands are sent back to the far server.

both methods, whether EXECUTE AT, or a direct update, are going to do the same thing...the only difference whoudl be where teh data gets hashed togetehr in the temp tables...AT the linked serve,r or locally by copying the data from the linked server.

how many rows are being actually updated?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 4

Is the database on ServerA trusted?

Jared
CE - Microsoft

Guras SSCertifiable Points: 6518 More actions · Answer 5

Lowell (8/14/2012)
linked servers are slow for updates; it's usually because the far table (which might be huge) gets copied over into a local temp table, then the data is merged for the update, adn then the commands are sent back to the far server.
both methods, whether EXECUTE AT, or a direct update, are going to do the same thing...the only difference whoudl be where teh data gets hashed togetehr in the temp tables...AT the linked serve,r or locally by copying the data from the linked server.
how many rows are being actually updated?

50 rows at a time

Guras SSCertifiable Points: 6518 More actions · Answer 6

SQLKnowItAll (8/14/2012)
Is the database on ServerA trusted?

Yes , it is.

Jared SSC Guru Points: 61793 More actions · Answer 7

Guras (8/15/2012)
SQLKnowItAll (8/14/2012)
Is the database on ServerA trusted?
Yes , it is.

Are you sure? How are you checking this?

Jared
CE - Microsoft