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/
7
-
Yep I would use linked servers just very weary of ever linking Development to Production though! What are you trying to do with the data? Merge it? replace development or production with it?Matt– Matt2016-06-20 21:53:44 +00:00Commented Jun 20, 2016 at 21:53
-
I can only load files into the test environment. Once I am done testing my loads there I want to execute them against productionschalld– schalld2016-06-21 22:20:08 +00:00Commented Jun 21, 2016 at 22:20
-
So you are wanting to keep you dev environment up to date with the production? If you simply wanting to test and NOT keep data in synch between them I don't see a reason you should have them connected at all if that is the case, but I am sure I am missing something too...Matt– Matt2016-06-21 22:26:21 +00:00Commented Jun 21, 2016 at 22:26
-
and will I be able to update to prod from qc through the linked servers?schalld– schalld2016-06-21 22:29:03 +00:00Commented Jun 21, 2016 at 22:29
-
I have to use the dev environment to develop in. I cant put any of my load or new files on the production server. I can however update prod when on prod but I have files on dev that aren't on prod that I use for updates.schalld– schalld2016-06-21 22:31:02 +00:00Commented Jun 21, 2016 at 22:31
|
Show 2 more comments
2 Answers
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
Comments
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
