SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


error using linked server and EXECUTE AT


error using linked server and EXECUTE AT

Author
Message
Guras
Guras
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3277 Visits: 1267
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..
Lowell
Lowell
SSC Guru
SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)

Group: General Forum Members
Points: 183303 Visits: 41569
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
Guras
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3277 Visits: 1267
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
Lowell
SSC Guru
SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)

Group: General Forum Members
Points: 183303 Visits: 41569
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 Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35659 Visits: 3703
Is the database on ServerA trusted?

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Guras
Guras
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3277 Visits: 1267
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
Guras
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3277 Visits: 1267
SQLKnowItAll (8/14/2012)
Is the database on ServerA trusted?


Yes , it is.
Jared Karney
Jared Karney
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35659 Visits: 3703
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search