SQL Server Performance Forum – Threads Archive

SqlCommand busy fetching

We have a desktop application that called a WebService. The WebService takes the data passed to it from the desktop app and pass this data to PostTrx.DLL along with a connection string. PostTrx.dll then creates a connection, opens it and creates a Transaction. Several tables are updated within the transaction and some other tables are read outside the transaction. At the end the transaction is either committed or rolled back. All works well when used in a single user environment. when 2 or more desktop all his the WebService at the same time it falls apart. one of the 2 or both processes will get a message saying "The SqlCommand is currently busy Open, Fetching". It’s like they are sharing the same connection.

---

Try to avoid using totally identical connection strings. One fake fix for this would be to misuse the WORKSTATION or APPLICATION parameter – provided the SQL Server database doesn’t expect specific info on those parameters (like whn using application roles).

---

Thanks for the idea but it did not help. It appears the the connections between the 2 instances of the DLL "know" about each other and block each other when one starts a read or transaction.

---

Do you use MSDE?

---

Can you catch the error, and retry until either a time-out occurs (one that you decide) or there is no error?

---

We are not using MSDE

---

Not sure i can trap because the error seems to change depending on where they collide. Sometimes is SqlCommand is busy fetching other times its Connection is closed and no longer valid, I’ve also gotten Connection does not support parrell transactions, it’s all over the place. I don’t understand why I’m getting this, the model I’m using must be getting used by most ASP.Net web pages with tons of users. This happens with us with 2 users. I must be missing something.

---

Just curious – could the database be in single-user access mode? Then only one connection at a time is allowed. Could be the DLL is catching the error but not passing on the original error details.

---

No the database is not single-user access. it’s part of a large system that allows multi-use, this app is a add in to the database. I’m testing in database that is not being used by anyone else at this time.

---

Single-user mode is not a design issue! Check the Properties for your db in EM, the Options tab, "Restricted access", suboption "Single User", or issue this query from QA: SELECT DATABASEPROPERTY(<dbname>, ‘IsSingleUser’) If the result is 1, then the database is in single-user mode and only one connection can be made to the database at any time, any attempt at a second concurrent connection fails.

---

comes back with a "0". the 2nd connection doesn’t fail when i try to connect, it happens deeper when attempting to read or update but not always at same place, i think it depends on the timing of the 2 apps.

---

I’m using several cmd objects to read/update/insert/delete from various tables. all are wrapped in the same transaction and rolled back or committed at end.

---

Your application WebService is instantiating objects to communicate with the DLL, right? But does it explicitly close those objects? For instance with VB you could be thinking that there’s garbage collection going on so why bother, but you should really include the cleaning-up in your own code.

---

Good point. is was not destroying the PostTrx.dll object. that is the object that does all the SQL work. i will add that. Thanks

---

Hm, not sure it’s wise to have different command objects in a transaction. Can’t you combine the set of read/update/insert/delete instructions into a single procedure, which includes the transaction, and call that from your command object?

---

i have to read/update/insert/delete many tables and they all need to get rolled back if they fail. I’m probably modifing 10+ tables in some way. Can that be done with a single command object?

---

It can be done by a single stored procedure as mentioned by Adriaan.

---

This DLL is it compiled in a mode that allows multiuse? No expert on this but seen issues with single threaded (or what is called) DLLs that simply do not work in a multiuser environment if not compiled correctly. The DLL will instanziate and act as one "single" user for the entire server.

---

]]>