Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Tuesday, December 9, 2014 2:56 PM
Points: 212, Visits: 752
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 @ 9:31 AM
Points: 12,962, Visits: 32,501
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Tuesday, December 9, 2014 2:56 PM
Points: 212, Visits: 752
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 @ 9:31 AM
Points: 12,962, Visits: 32,501
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1345050
Posted Tuesday, August 14, 2012 3:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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: Tuesday, December 9, 2014 2:56 PM
Points: 212, Visits: 752
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: Tuesday, December 9, 2014 2:56 PM
Points: 212, Visits: 752
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, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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