none
How to fetch data from server A to Server B without using Linked-Servers in SQL Server

    Question

  • Hi,

    I have a scenario wherein I have to fetch/retrieve data from a database situated on Server A to a database situated on Server B.

    Please note that i want to do this without creating a linked server from server B to Server A. Is this possible? Please reply..

    SQL Server Version I am using is SQL Server 2008R2 Enterprise Edition, Server OS: Windows 2008 Server.

    Thanks in advance.

    Regards,

    Tauhid S Shaikh

    Thursday, December 15, 2011 10:27 AM

Answers

  • You could use OPENROWSET which'll require the connection info, username & password.

    While I understand that the client believes that having an always-on connection to their data is risky, that's why you lock down the account. OPENROWSET means including the connection info in plain text.

    Thursday, December 15, 2011 11:05 AM

All replies

  • You could use OPENROWSET which'll require the connection info, username & password.

    While I understand that the client believes that having an always-on connection to their data is risky, that's why you lock down the account. OPENROWSET means including the connection info in plain text.

    Thursday, December 15, 2011 11:05 AM
  • Hi Tauhid,
    This can be done only in two ways, one being the linked server i.e. u need to link the two servers and then use the values using the function

    sp_addlinkedserver

    or the second way is to use openrowset function.
    Database consultant NY, USA - Intelcs.com
    Thursday, December 15, 2011 2:37 PM
  • why not use ssis to pull your data from disparate places and put it in one server?

    That's how business gets done.


    foxjazz
    Friday, December 16, 2011 2:17 PM
  • Hi

    You can use SSIS or data Import/Export wizards for fetching or retriving the data from on server to another server.

    The port opening and DSN creation needs to be done before using this.

    And create alias for the destination server in client network utility.

    Friday, July 12, 2013 6:05 AM