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

Answer 1

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.

Answer 2

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

Answer 3

why not use ssis to pull your data from disparate places and put it in one server?

That's how business gets done.

---

foxjazz