Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error using linked server and EXECUTE AT Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 2:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 281, Visits: 1,069
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..
Post #1345031
Posted Tuesday, August 14, 2012 3:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 14,326, Visits: 37,418
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!
Post #1345043
Posted Tuesday, August 14, 2012 3:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 281, Visits: 1,069
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.
Post #1345047
Posted Tuesday, August 14, 2012 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 14,326, Visits: 37,418
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!
Post #1345050
Posted Tuesday, August 14, 2012 3:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 19, 2016 10:25 AM
Points: 2,751, Visits: 3,639
Is the database on ServerA trusted?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1345052
Posted Tuesday, August 14, 2012 5:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 281, Visits: 1,069
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
Post #1345084
Posted Wednesday, August 15, 2012 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 281, Visits: 1,069
SQLKnowItAll (8/14/2012)
Is the database on ServerA trusted?


Yes , it is.
Post #1345270
Posted Wednesday, August 15, 2012 7:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 19, 2016 10:25 AM
Points: 2,751, Visits: 3,639
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?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1345276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse