I'm using Borland C++ Builder 4 and its TQuery component to try to work with
a SQL Server 7.0 database via ODBC from Win98. My goal is to completely
delete and then repopulate four tables (from external flat files) so that no
one can do anything with these four tables while they're loading. My
approach is the following:
Create four TQuery objects all using the same database.
Use one of the TQuery objects to execute the following:
"set transaction isolation level serializable"
Use each TQuery object to "delete from <table>" in turn.
Prepare a different "insert" statement in each of the four TQuery objects.
Process the flat files, inserting as you go.
Use one of the TQuery objects to "commit work".
The deletion of the four tables goes fine, but when the first (or something
close to the first) "insert" statement is run, the process hangs. Looking
at the SQL Server Enterprise Manager, I see that there are more than one
"spid" processes accessing my database (typically, three [I think]). One
spid is WAITing for a table lock, held by another, to be released.
Shouldn't there be only one SQL Server process running? How can a single
database connection wait for itself? Shouldn't all four prepared statements
share the same spid?
Thanks for any assistance you can render!