SQL Server Performance

SqlCommand busy fetching

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by johnjohn2076, Nov 29, 2005.

  1. johnjohn2076 New Member

    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.
  2. Adriaan New Member

    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).
  3. johnjohn2076 New Member

    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.
  4. mmarovic Active Member

    Do you use MSDE?
  5. Adriaan New Member

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

    We are not using MSDE

  7. johnjohn2076 New Member

    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.
  8. Adriaan New Member

    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.
  9. johnjohn2076 New Member

    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.
  10. Adriaan New Member

    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.
  11. johnjohn2076 New Member

    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.

  12. johnjohn2076 New Member

    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.
  13. Adriaan New Member

    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.
  14. johnjohn2076 New Member

    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
  15. Adriaan New Member

    quote:Originally posted by johnjohn2076

    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.
    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?
  16. johnjohn2076 New Member

    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?
  17. mmarovic Active Member

    It can be done by a single stored procedure as mentioned by Adriaan.
  18. Argyle New Member

    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.

Share This Page