error using linked server and EXECUTE AT

  • 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..

  • 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!

  • 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.

  • 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!

  • Is the database on ServerA trusted?

    Jared
    CE - Microsoft

  • 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

  • SQLKnowItAll (8/14/2012)


    Is the database on ServerA trusted?

    Yes , it is.

  • 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

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

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