Comparing Records Across Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing Records Across Servers

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2004-10-06 : 13:06:07

What is the best way to compare records across 2 separate databases. Although the structures are completely different I can create a subset of the pertinent fields with the following queries

SELECT dbo.tarCustomer.CustID, dbo.tarCustomer.CustName, dbo.tciAddress.AddrLine1, dbo.tciAddress.City,
	dbo.tciAddress.StateID, dbo.tciAddress.PostalCode, dbo.tciPaymentTerms.PmtTermsID,
	dbo.tciContact.Phone, dbo.tciContact.Fax, dbo.tciContact.EMailAddr
FROM dbo.tciAddress INNER JOIN
	dbo.tarCustAddr ON dbo.tciAddress.AddrKey = dbo.tarCustAddr.AddrKey INNER JOIN
	dbo.tarCustomer ON dbo.tarCustAddr.AddrKey = dbo.tarCustomer.PrimaryAddrKey INNER JOIN
	dbo.tciPaymentTerms ON dbo.tarCustAddr.PmtTermsKey = dbo.tciPaymentTerms.PmtTermsKey INNER JOIN
	dbo.tciContact ON dbo.tarCustomer.PrimaryCntctKey = dbo.tciContact.CntctKey
ORDER BY CustID

SELECT Customer_id, Customer_Name, Billing_Address, Billing_City, Billing_State,
	Billing_Zip, Method_of_payment, Phone_Number, Fax_Number, Email_Address
FROM EZGoes.dbo.Customer_Information
ORDER BY Customer_id

BCrowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 13:17:58

use those as views and join both table on the fields you want to compare.
based on the join you'll get which records are same and which are not.

Go with the flow & have fun! Else fight the flow

BCrowe
Starting Member

23 Posts

Posted - 2004-10-06 : 13:24:13

My problem is that the views/tables whatever form i get them into are on separate servers. How do I compare them?

BCrowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 13:27:26

you could use linked servers. look them up in BOL.

Go with the flow & have fun! Else fight the flow

BCrowe
Starting Member

23 Posts

Posted - 2004-10-06 : 15:21:17

Thank you, that seems to be what I'm looking for but I can't get the darn thing to work. I am getting a "'SER_ORD' is not a recognized OPTIMIZER LOCK HINTS option." from the following command:

SELECT * FROM OPENQUERY( SER_ORD, 'SELECT Customer_id, Customer_Name, Billing_Address, Billing_City, Billing_State,
	Billing_Zip, Method_of_payment, Phone_Number, Fax_Number, Email_Address
FROM EZGoes.dbo.Customer_Information
ORDER BY Customer_id')

Any ideas?

BCrowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 15:37:08

read this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;200797

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources