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)
 Checking missing records between servers

Author  Topic 

wtsyung
Starting Member

1 Post

Posted - 2005-01-24 : 11:11:11
Dear all,

I have been assigned a task to check any missing records between 2 servers. Assume both serverA and serverB contain the same tables. My task is to make up a solution such that it will
1) check all missing records (based on its primary keys) on all tables between 2 servers.
2) put all missing records in a tempeorary table for replication.

I have not much of a problem on the 2nd task, but I am very frustrated on the 1st task. I cannot think of any good way to check all tables since they all have different primary keys and my boss wants it as generic as possible (meaning servers, databases and table as parameter).

declare @server1, @server2 varchar(10)
declare @db1, @db2 varchar(10)
declare @tbl varchar(10)

I really don't know how I can retrieve missing records using tables's primary keys BETWEEN servers and the code has to be GENERIC!! I will greatly appreicated for any suggestion, methods, some code samples.

WTS

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-24 : 12:16:43
I'd create a linked server, create a cursor bwteen the 2 catalogs (I can't figure it out with the INFORMATION_SCHEMA views...anyone?)

The create a cursor and build a sql string that will identify the missing rows....



EXEC sp_addlinkedsrvlogin 'Tax_Dev', 'false', NULL, 'sa', 'password'

CREATE CURSOR myCursor99
AS
SELECT LocalTableName, LocalColumnName, LocalColId
, RemoteTableName, RemoteColumnName, RemoteColId
FROM (
SELECT o.[name] AS LocalTableName, c.[name] AS LocalColumnName, c.colid AS LocalColId
FROM Northwind.dbo.sysobjects o
INNER JOIN Northwind.dbo.sysColumns c
ON o.[id] = c.[id]
WHERE o.xtype = 'U'
) AS L
JOIN (
SELECT o.[name] AS RemoteTableName, c.[name] AS RemoteColumnName, c.colid AS RemoteColId
FROM Tax_Dev.Northwind.dbo.sysobjects o
INNER JOIN Tax_Dev.Northwind.dbo.sysColumns c
ON o.[id] = c.[id]
WHERE o.xtype = 'U'
) AS R
ON LocalTableName = RemoteTableName
AND LocalColumnName = RemoteColumnName
ORDER BY 1,3




I'd write the wole thing, but it'd take a bit....



Brett

8-)
Go to Top of Page
   

- Advertisement -