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

shalabh21 gravatar image

51 9 11 12

(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)][2]

[2]: http://msdn.microsoft.com/en-us/library/ff772782.aspx
more ▼

answered Apr 16, 2012 at 06:44 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1967 times

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