sql server connect: Hi guys,

I have a simple problem, and on the paper I've solved it, but the
results are absolutely baffling and inconsistent, hence my cry for help.

My client has an SQLServer database (designed and fed by my software) at
a hoster, with, for the sake of this exercise, 2 related tables, lets
call them A and B:
- the main table A contains loads of rows with mostly numeric data
- the related table B is holding footnotes related to some of the rows
of the main table

Through an earlier mishap of my software, there is an unknown number of
"orphan" footnote rows in B that relate to rows which have since then
beeen deleted from A. And no, I did not define any referential integrity
between the 2 tables, thank you very much, I know, it was silly.

I thus set out today to write a small ASP application to clean up the
"orphan" footnote links in table B. The plot is simple:
- read table B sequentially
- for every row in B, check if the related row in A is still existing
- if not, delete rogue row in B

Both tables have keys made out of many attributes. A has a 4-part
primary key, B a 6-part one. I am reading B sequentially, using 4 out of
the 6 parts to access the full key of table B.

Sounds simple. Being cautious, as the data is production data, I first
defined 3 functions in my app:
1 read B and show its rows in a table
2 read B and find out which rows are orphans, and show these in the table
3 read B, find out which rows are orphans, and delete them
I programmed 1 and 2, and decided to run that and check the results.
That's where the problems started.

B has about 6,000 rows. To me, that's really nothing. Function 1 was
reasonably quick (20 secs?). But then, running function 2 first led to a
crash (general network error), then, when rerun, took absolute ages
(minutes in any case). But the worse was that the results were wrong: it
was showing a huge number of orphans, and, when checking, most of them
turned out NOT to be orphans.

I then started to narrow my reading, by only reading sections of B. Even
that gave inconsistent results. Once it finds no orphans for certain
sections, re-running finds hundreds of them. And always the same
happens: from a given row in B on, it shows ALL accessed rows in A to be
missing, which is wrong. But the point at which it starts to happen vary
every time !

My code (VBScript) is dead simple, so I'll post it here:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' Gear up to access sequentially a section of the footnote table
Set rs_1 = Server.CreateObject("ADODB.Recordset")
rs_1.ActiveConnection = MM_SDS_STRING
' Set up SQL statement. The start and end point are set by the user
rs_1.Source = "SELECT * FROM dbo.ComparFootNotes where ComparId
[quoted text, click to view]
rs_1.CursorType = 0
rs_1.CursorLocation = 2
rs_1.LockType = 1
rs_1.Open()
while not rs_1.Eof
' Get the footnote data relating to the parent data
cId = rs_1.Fields.Item("ComparId").Value
vD = rs_1.Fields.Item("VersionDate").Value
rowId = rs_1.Fields.Item("RowId").Value
colId = rs_1.Fields.Item("ColId").Value
iId = rs_1.Fields.Item("ItemId").Value
typ = rs_1.Fields.Item("RowType").Value
' If we are showing or deleting orphans
if action <> "ShowAll" then
' Gear up to access main table
Set rs_2= Server.CreateObject("ADODB.Recordset")
rs_2.ActiveConnection = MM_SDS_STRING
Call CvtDate(vD, "ToSQL", vDate, sTemp, sTemp1, bDebug)
rs_2.Source = "SELECT * FROM dbo.ComparVersionData WHERE
ComparId = " & cId & " AND VersionDate = '" & vDate & "' AND Type = '" &
typ & "' AND RowId = " & rowId
rs_2.CursorType = 0
rs_2.CursorLocation = 2
rs_2.LockType = 1
rs_2.Open()
' If the row is not found, it's an orphan, so we show it
if rs_2.EoF then show = true else show = false
' If the run is showing all footnotes, we set the show flag
else
show = true
end if
rs_2.Close
' Show what we found in the display table
if show = true then
Response.Write("<tr>")
Response.Write("<td>" & cId & "</td>")
Response.Write("<td><div align=""center"">" & vD & "</td>")
Response.Write("<td>" & rowId & "</td>")
Response.Write("<td>" & colId & "</td>")
Response.Write("<td>" & iId & "</td>")
Response.Write("</tr>")
end if
' Get next footnote row
rs_1.MoveNext
wend
rs_1.Close()
Set rs_1 = nothing

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

What happens is that the "if rs_2.EoF" above suddenly triggers for ALL
rows accessed after a while. E.g. after 200 read rows in B and A, then
EVERY accessed row in A gets the EoF flag ! Or it works perfectly, and I
get only the orphan rows. And I run it gain, and it triggers again, but
after 600 rows...

I don't get it at all, can anyone tell me what I'm doing wrong ???

Thanks

[quoted text, click to view]

Why not:

DELETE FROM dbo.ComparFootNotes B
WHERE NOT EXISTS
(SELECT 1
FROM dbo.ComparVersionData A
WHERE a.ComparId = b.ComparId
AND a.VersionDate = b.VersionDate
AND a.Type = b.Type
AND a.RowId = b.RowId)


--
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95

[quoted text, click to view]