[callahan09]

Hello. I had to build a sync application to sync two databases, one from a local access file to an MS SQL Server 2008 R2 database. The basics: it goes through the database and hashes every record, then compares the hash table against yesterday's hash table and takes the new, deleted and modified records and does the insert, delete, and update statements to the database on SQL Server in a transaction.

The number of records being added/deleted/modified today is close to 5000, with approximately 100 of them being updates, 10 being deletes, and the rest inserts.

There were no errors whatsoever reported during the running of my program and I have error reporting built in at practically every step of the way so if something went wrong I would have gotten a log of it. But at the end of the sync, I went into the database to see if it worked, and only the deletes and modifies are reflected in the database right now.

Each insert statement was executed without exception catch. All 4900 or so insert statements were executed first, then the 10 deletes, then the 100 modifies, then the whole thing was committed.

I began the transaction with READ COMMITTED isolation type.
Everything is done in a C# application via OleDbConnection/OleDbCommand/OleDbTransaction.

So I can't figure out why I would have gotten no errors, no exceptions, and some of the statements are visibly reflected in the database after the commit (all of the deletes & updates), and none of the inserts are visible.

It's been about 20 minutes since the commit. Is it possible that it just hasn't finished committing yet? How long could it take if that's the case, and how can I get confirmation when the Commit is completed?

I tried to rename the database and restore a backup and run the sync again to see if I could do some step-through and find any issue, but I get the following error when I try to rename the database:

Quote

TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to rename [database]. (ObjectExplorer)

------------------------------
ADDITIONAL INFORMATION:

Rename failed for Database '[database]'. (Microsoft.SqlServer.Smo)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

Please help. Thank you in advance.

[ForcedSterilizationsForAll]

Have the inserts ever worked? If so, what's changed since they last worked? Is it just that there are more records today than previously?

[callahan09]

ForcedSterilizationsForAll, on 11 March 2011 - 02:18 PM, said:

Have the inserts ever worked? If so, what's changed since they last worked? Is it just that there are more records today than previously?

The inserts work when not part of a transaction. This is the first time I've tested the sync with transactions implemented. I'm baffled because the updates and deletes are going through, but the inserts are not, even though they're all part of the same transaction.

I have confirmed that the insert statements are well-formed and working. I dumped each one to an output file and ran them manually in management studio, and they work in that case.

There is only one transaction and no nested transactions.

How is it possible for the inserts to not take effect but the other statements to take effect? I thought that the nature of the transaction was that if one didn't work, none would work, but I'm clearly getting a situation here where SOME work, and some do not.

[callahan09]

OK, I have an update: I finally remembered that I can pull a report on transaction activity. So I pulled a report right after my code called BeginTransaction() on my OleDbConnection, setting the value of my OleDbTransaction.

No transactions.

Then again after setting the OleDbCommand's Connection property to the OleDbConnection that I began the transaction on & setting the Transaction property of my OleDbCommand to my OleDbTransaction.

Still no transactions.

Then my code opens and loops through the record set of each record that needs to be inserted onto the SQL Server database. For each one, it builds an Insert statement, sets the OleDbCommand's CommandText property to the new SQL statement string, then executes the OleDbCommand's ExecuteNonQuery() method.

Throughout this entire process, still no transactions on the database exist.

After going through this entire record set, it closes the record set and opens the record set of all records to be deleted from the SQL Server database. It goes through each one, builds a Delete statement, and so on the same as with the inserts.

As soon as it does the first ExecuteNonQuery() for the deletes, THEN the database reports a transaction as being open.

So that explains why the transaction is committing and only the deletes and updates work, not the inserts. It's because the transaction doesn't exist until it gets to the deletes. I'm doing all of my inserts on the transaction, so they aren't just inserted into the database at the moment of the ExecuteNonQuery(), but the transaction itself ALSO somehow does not exist, so those statements are just sort of disappearing into thin air.

So this raises a couple of questions more for me: why would the transaction not begin until it gets to the deletes, after trying to do 4000+ inserts? And why would executing those inserts against a transaction that hasn't even begun yet as far as the database is concerned not throw any errors or exceptions?

Does any of this mean anything more to anybody than it does to me?

Thanks in advance, again!

Edit:

I looked up BeginTransaction on google, just to see what there was on that. Here's something that stuck out at me:

Quote

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement.

So obviously none of my 4000+ INSERT statements are actually being "performed". Because if they were, then that would create the log for the transaction. So the question is: WHY are my INSERT statements not being performed? As I stated in my first post, they are well-formed: they work when I manually enter them, or when I do them without a transaction. I step through my code and see explicitly that the action is executed and that no errors or exceptions occur. So what gives?

This post has been edited by callahan09: 11 March 2011 - 02:01 PM

[Atli]

Have you tried executing a smaller batch of insert statements?
I don't know much about the internals of OleDB or even Microsoft's SQL server, but my first thought when I read this was some sort of a limit on the number of statements/amount of data a prepared statement can contain.

As I understand it a prepared statement is actually executed on the server, rather than compiled on the client. That is, the statement is sent to the server with all the data, where the statement is compiled and executed against all the data. - Perhaps 4000+ rows of data exceed some sort of a limit on the server, and the entire statement is getting thrown out. (Note, this is just a theory, but it sounds plausible... right? )

My point is, try a smaller sub-set of the INSERT statements, just for debugging purposes.

P.S.
Also, I can't help but ask, is there a reason for why you want the transaction to be so big? Are the INSERTs, UPDATEs, and DELETEs related in some way that would cause them to fail if inserted separately?