How to the find the missing records

Question

Hi All .

I  am trying to find the missing records from tables in different databases across different instances in SQL Server 2008 R2.   I ran into two issues ,

Issue # 1:

SQL Server error message  "Remote table-valued function calls are not allowed." , when I issue  a SELECT with no lock hint .

Issue #2:

Out of Memory Error   , when I issue the following SELECT with no "no lock " hint

..   The count(*) in Table A - 24 million & count (*) is 19 million records .

Is there any other efficient way to find the missing records ?

SELECT  ISBN  FROM dbo.TableB

EXCEPT

SELECT  ISBN  FROM Instance.db.dbo.TableA

Thanks 

Answer 1

Only when your ISBN columns are indexed. Thus I would consider creating an index for ISBN ASC on each table and then run the EXCEPT. Or at least use a temp table:

SELECT  ISBN
INTO    #RemoteIsbn
FROM    Instance.db.dbo.TableA ;

-- CREATE INDEX IX_ISBN ON #RemoteIsbn ( ISBN ASC ) ;

SELECT  ISBN
FROM    dbo.TableB
EXCEPT
SELECT  ISBN
FROM    #RemoteIsbn ;

DROP TABLE #RemoteIsbn ;

Answer 2

The ISBN columns are already indexed . Still , it was spitting "Out of Memory" .

Answer 3

Hi DBA_CONSLUTING,

>>Out of Memory"

Regarding to the error message you provided, this is a general error which might occurs when the result set returned by your query batch is very large and you reach the limits.

Assuming you temper opening large tables with Management Studio. If your table contains millions records of that column, opening this table might take too much GB of memory to open and display the data.

So please post the Error log for further troubleshooting.

---

Regards, Amber zhang

Answer 4

hi, checkout the below link, it may help you.

http://www.ehow.com/how_6567613_missing-records-using-sql.html