Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: Copy table data between non-linked servers

    I want SQL query to copy data from one table to another when the tables are present in two different db which are present in 2 differernt servers. Servers are not linked. The server which contains the table where data needs to be copied contains a separate set of UserId and Password. we need to connect to that server and copy data from the current server using sql query. This logic needs to be implemented using stored procedure only. We cannot use enterprise manager or command prompt, because server changes dynamically depending on the input.
    Please help. I am using SQL Server 2005.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I assume that you are doing this periodically, so this could be set up as SQL Server Agent job.

    You say that this has to be dynamic because the destination server may change.

    Have you looked at programmitcally and dynamically linking to the destination server (sp_addlinkedserver), transferring the table, then dropping the linkage (sp_dropserver)?

    You could have a local table on the source server with all of the parameters needed to create the linkage.

    This is the general direction I would take.

    Just some thoughts.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Sep 2010
    Posts
    8

    need to copy some rows only

    I don't want to transfer the entire table. It is only some rows that will be transferred based on the status. Moreover I tried with addlinkedserver. After that when I try to copy data from the existing server to the newly added linked server, I get cannot establish connection error.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I don't think there is anything in my proposed scheme that would prevent you from transferring only a portion of a table.

    If you are having a problem configuring a linked server, then you need to resolve that problem first.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2010
    Posts
    8

    Issue resolved

    when I add a linked server directly instead of using the SP_addlinkedserver. I am able access the data from another server.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I HAVE TO BELIEVE, that if you can add a linked server via SQL Management Studio, that you should be able to add that liked server using sp_addlinkedserver.

    After all, what do you believe SQL Management Studio is doing behind the scenes--it's executing the sp_addlinkedserver command.

    There is something wrong in how you are executing the sp_addlinkedserver command.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Sep 2010
    Posts
    8

    Catisi our

    I have tried using addlinkedserver and dropserver. Problem is server type is wrongly set. can you please let me know how to use addlinkedserver SP. May be there is some issue in parameter passing.

    Thanks

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Are the other servers you are trying to link-to SQL Servers?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by debankita View Post
    when I add a linked server directly instead of using the SP_addlinkedserver. I am able access the data from another server.
    right click this linked server, script, create, done.

  10. #10
    Join Date
    Jan 2005
    Posts
    28
    Hello,
    If you have a SQL Server 2008 or later SSMS, you can connect to the source database and script data of the source table.
    This will create insert statements
    Then you can execute the script on the target database which will insert data from source table.
    Please check the article Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard

    Please note that SQL2008R2 has some minor changes on the wizard but capable of scripting data from SQL2005.

    I hope that helps,
    Eralper

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •