CODEWITHSUNDEEP
sqlsql-server
schalld
schalld

Reputation: 39

Retrieving data from different servers and databases

I have two servers 10.10.7.10 and 10.10.2.10. 10.10.7.10 has the dev database with dbo as owner of a table named vendor. On 10.10.2.10 I have the same table in a database name prod. How do I retrieve data from both servers logging into 10.10.7.10 as a remote connection and using sql management studio to create and run my queries/

Upvotes: 0

Views: 76

Answers (2)

M.Ali
M.Ali

Reputation: 69524

Create a linked server and use four part name from your dev server to retrieve data from your prod server.

Create a Linked Server

Logon to your Dev server and add the Prod server as your linked server using the following command,

EXEC master.dbo.sp_addlinkedserver @server     = N'PRODSERVER'
                                 , @srvproduct = N'SQLSERVER'
                                 , @provider   = N'SQLOLEDB'
                                 , @datasrc    = N'10.10.2.10'

The above statement will create a linked server to your Prod server 10.10.2.10 now you can write t-sql statement from your Dev server using the four-part name. something like.... 

Select * from [PRODSERVER].DBname.dbo.TableName

Upvotes: 1

Mike
Mike

Reputation: 1667

Create a Linked server on your Dev server called Prod.

Then you can run a query like this

SELECT p.*, dev.* FROM PROD.dbName.dbo.VENDOR  as p 
inner join dbo.Vendor as Dev 
ON p.vendorId = dev.vendorId

Upvotes: 0

Related Questions