locked
Odd Linked Server Behavior RRS feed

  • Question

  • I have a linked server that is created with sp_addlinkedserver.  The server exists.  When I execute the resulting @SQL, it works but, when I run this script, it says that it doesn't exist in sys.servers.

    Can anyone lend a hand?

     

    DECLARE @SQL varchar(500)

    DECLARE @RemoteServer sysname

    SEt @RemoteServer = N'your_linked_server_name'

    DECLARE @RemoteTable sysname

    Set @RemoteTable = @RemoteServer + 'your_db_name.schema_name.table_name'

    Set @SQL = N'Insert into your_new_table_name Select * from ' + @RemoteTable

    Print 'SQL = ' + @SQL

    Execute @SQL


    John Steven Frase
    Thursday, June 23, 2011 9:30 PM

Answers

  • Try

    DECLARE @SQL varchar(500)
    
    DECLARE @RemoteServer sysname
    
    SEt @RemoteServer = N'your_linked_server_name'
    
    DECLARE @RemoteTable sysname
    
    Set @RemoteTable = quotename(@RemoteServer) + '.your_db_name.schema_name.table_name'
    
    Set @SQL = N'Insert into your_new_table_name Select * from ' + @RemoteTable
    
    Print 'SQL = ' + @SQL
    
    Execute (@SQL)
    

    You're missing . and also () around the dynamic SQL command.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Steve Frase Thursday, June 23, 2011 10:09 PM
    Thursday, June 23, 2011 10:04 PM

All replies

  • Try

    DECLARE @SQL varchar(500)
    
    DECLARE @RemoteServer sysname
    
    SEt @RemoteServer = N'your_linked_server_name'
    
    DECLARE @RemoteTable sysname
    
    Set @RemoteTable = quotename(@RemoteServer) + '.your_db_name.schema_name.table_name'
    
    Set @SQL = N'Insert into your_new_table_name Select * from ' + @RemoteTable
    
    Print 'SQL = ' + @SQL
    
    Execute (@SQL)
    

    You're missing . and also () around the dynamic SQL command.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Steve Frase Thursday, June 23, 2011 10:09 PM
    Thursday, June 23, 2011 10:04 PM
  • Wow! Very quick, thanks. I've been looking at it too long. Thanks.
    John Steven Frase
    Thursday, June 23, 2011 10:11 PM
  • The example as posted would attempt read from the database your_linked_server_nameyour_db_name.

    Another error in the script is that you are tring to execute the procedure "Insert into your_new_table_name....". To execute an SQL string, you parenthese:

    EXECUTE(@SQL)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 23, 2011 10:36 PM