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.
asked Apr 16 '12 at 06:31 PM in Default
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.
See: Linked Servers (Database Engine)
answered Apr 16 '12 at 06:44 PM
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
answered Apr 16 '12 at 07:47 PM