Select Query On Two Different Database

Hi All,

I want to know how can I write cross server select query.

e.g. I have two database one is support database DB1 on server S1 and second is production database DB2 on server s2.

Also there is one similar table T1 on both database DB1 and DB2.

Each weekend say Saturday support database is schedule to refresh with latest backup of live. In some case this will get fail due some reason.

Now I want to write select query through which I can compare whether the records of DB1 (support database) is similar to database DB2 (production database).

The purpose behind this I want to generate a report which will flag us whenever support weekly database get fail.

I can achieve this by comparing table T1 records of these two database. So for this I need cross server / database select query.

Please do the needful.

more ▼

asked Apr 16, 2012 at 06:31 PM in Default

avatar image

51 11 11 13

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can achieve this by a Linked Server.

When you create a Linked Server, then you can refer to objects on other server the 4 parts object name.

 FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

See: Linked Servers (Database Engine)
Create Linked Servers (SQL Server Database Engine)

more ▼

answered Apr 16, 2012 at 06:44 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

Going on from what @Pavel Pawlowski says, if you don't have permissions to create a Linked Server object, you may be able to do what you want using an OPENROWSET query.

See: http://msdn.microsoft.com/en-us/library/ms190312.aspx - the first example there is most appropriate to your requirements

more ▼

answered Apr 16, 2012 at 07:47 PM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 16, 2012 at 06:31 PM

Seen: 2332 times

Last Updated: Apr 16, 2012 at 07:47 PM

Copyright 2018 Redgate Software. Privacy Policy