none
An unhandled exception ('System.Data.SqlClient.SqlException') in a windows service RRS feed

  • Question

  • I have a windows service which is multi threaded using a ThreadPool.QueueUserWorkItem , that does some data capture from several other databases. The service for the production environment runs in a 2003 server which has multiple processors. Every now and then almost once every week (not consistent), I get an error. This error does not get trapped in any of my error handling code. My development box has XP, I have run this service on it for several days but never have encountered this error.

    A maximum of 4 threads are spawned during each run. All threads attempt to update/delete/insert two tables, but never the same record.

    The service crashes and the only entry is in the Application Event Viewer.

    Event Type: Error
    Event Source: .NET Runtime 2.0 Error Reporting
    Event Category: None
    Event ID: 5000
    Date:  9/18/2009
    Time:  3:22:38 AM
    User:  N/A
    Computer: DP01ABRE
    Description:
    EventType clr20r3, P1 salesincentivedatacapture.exe, P2 1.0.0.0, P3 4a9beed3, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2490, P8 2c, P9 system.data.sqlclient.sql, P10 NIL.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 63 00 6c 00 72 00 32 00   c.l.r.2.
    0008: 30 00 72 00 33 00 2c 00   0.r.3.,.
    0010: 20 00 73 00 61 00 6c 00    .s.a.l.
    0018: 65 00 73 00 69 00 6e 00   e.s.i.n.
    0020: 63 00 65 00 6e 00 74 00   c.e.n.t.
    0028: 69 00 76 00 65 00 64 00   i.v.e.d.
    0030: 61 00 74 00 61 00 63 00   a.t.a.c.
    0038: 61 00 70 00 74 00 75 00   a.p.t.u.
    0040: 72 00 65 00 2e 00 65 00   r.e...e.
    0048: 78 00 65 00 2c 00 20 00   x.e.,. .
    0050: 31 00 2e 00 30 00 2e 00   1...0...
    0058: 30 00 2e 00 30 00 2c 00   0...0.,.
    0060: 20 00 34 00 61 00 39 00    .4.a.9.
    0068: 62 00 65 00 65 00 64 00   b.e.e.d.
    0070: 33 00 2c 00 20 00 73 00   3.,. .s.
    0078: 79 00 73 00 74 00 65 00   y.s.t.e.
    0080: 6d 00 2e 00 64 00 61 00   m...d.a.
    0088: 74 00 61 00 2c 00 20 00   t.a.,. .
    0090: 32 00 2e 00 30 00 2e 00   2...0...
    0098: 30 00 2e 00 30 00 2c 00   0...0.,.
    00a0: 20 00 34 00 38 00 38 00    .4.8.8.
    00a8: 39 00 64 00 65 00 61 00   9.d.e.a.
    00b0: 66 00 2c 00 20 00 32 00   f.,. .2.
    00b8: 34 00 39 00 30 00 2c 00   4.9.0.,.
    00c0: 20 00 32 00 63 00 2c 00    .2.c.,.
    00c8: 20 00 73 00 79 00 73 00    .s.y.s.
    00d0: 74 00 65 00 6d 00 2e 00   t.e.m...
    00d8: 64 00 61 00 74 00 61 00   d.a.t.a.
    00e0: 2e 00 73 00 71 00 6c 00   ..s.q.l.
    00e8: 63 00 6c 00 69 00 65 00   c.l.i.e.
    00f0: 6e 00 74 00 2e 00 73 00   n.t...s.
    00f8: 71 00 6c 00 20 00 4e 00   q.l. .N.
    0100: 49 00 4c 00 0d 00 0a 00   I.L.....

    • Moved by eryang Thursday, September 24, 2009 3:29 AM (From:.NET Base Class Library)
    Friday, September 18, 2009 6:18 PM

All replies

  • What does your error handling code look like?

    You should have a catch(Exception) at each thread's top-level method for logging purposes.

            -Steve
    Programming blog: http://nitoprograms.blogspot.com/
      Including my TCP/IP .NET Sockets FAQ
    MSBuild user? Try out the DynamicExecute task in the MSBuild Extension Pack source; it's currently in Beta so get your comments in!
    Friday, September 18, 2009 6:29 PM
  • Yes I do, and what suprises me that it does not get caught in any of those.
    I have a try {}catch(Exception Ex) {} on the on start method and in the call back method for the Timer (using a System.Threading.Timer). I have gone on to add a try catch in very method as of now, so as to know when its happening. But all that happens is the service crashes and this error is entered into the event logs.

    To throw some more light into this. Data access is through Microsoft Enterprise Library. I do not have any add insert/updates/deletes from the .net code. They are handled by calls to stored procedures which does the processing in batches, so there isnt much of code on the .net side other than to call the stored procedures.
    • Edited by zVin Friday, September 18, 2009 6:56 PM
    Friday, September 18, 2009 6:40 PM
  • Are your database resources (SqlConnection/SqlTransaction/SqlCommand) shared among threads?

           -Steve


    Programming blog: http://nitoprograms.blogspot.com/
      Including my TCP/IP .NET Sockets FAQ
    MSBuild user? Try out the DynamicExecute task in the MSBuild Extension Pack source; it's currently in Beta so get your comments in!
    Friday, September 18, 2009 6:48 PM
  • Answer is No, for for all three. All transactions begun and committed are within the stored procedures. Infact every thread has its own set of data to deal with except for the fact that they reside in the same two tables. This seperation is identified with the use of a key field. I have ensured that I am not sharing any database resoruces among threads. Each data access method opens the connection and closes it when it exists the method. I dont use data readers. I have passed data in a few cases through datasets which are disconnected. I do not update these data sets. I only use them to read data (basically loop through) and then pass the values into stored procedures to insert/update/delete.

    I have encountered one or two deadlocks, but these have been trapped in the exception handling and I have them recorded in the log files. But since they have been trapped, the service continues to process the next record and never crashes.

    Thanks Steve for taking time to help resolve this issue.
    Friday, September 18, 2009 7:23 PM
  • Hmmm... I'm out of the "easy answers".

    Give it a bit of time to let others here post an answer; also ask in a SQL server forum in case it's an issue with SqlClient.

    If no solution is found then escalate it: get a minidump when it crashes and crank up SoS. You also may want to get Microsoft involved at that point.

            -Steve
    Programming blog: http://nitoprograms.blogspot.com/
      Including my TCP/IP .NET Sockets FAQ
    MSBuild user? Try out the DynamicExecute task in the MSBuild Extension Pack source; it's currently in Beta so get your comments in!
    Friday, September 18, 2009 7:39 PM
  • When you debug, try set your exception thown policy to break on all CLR excetions.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
    Visual C++ MVP
    Saturday, September 19, 2009 12:40 AM
  • Thank you for your reply. Correct me if I am wrong, what you have stated stands good when I want to debug from Visual Studio. I have a strange situation here. I have run this service on my machine (XP) for days and have never encountered this error. I have run this on a staging server which has a Windows 2003 Server with a single processor for months it still is running and have not encountered this error. Its only when I run this service on the multi processor, multi core environment. I strongly suspect this issue is with System.Data.SqlClient, since I have gone on to add exception handling is every method in all the dll's that this windows service is using and never gets caught.
    I will post this in the SQL Server forums as well.
    Monday, September 21, 2009 1:02 PM
  • Hi,
    I redirect this question to ADO.NET forum, hope experts here can give more help.

    Thanks,
    Eric
    Please remember to mark helpful replies as answers and unmark them if they provide no help.
    Thursday, September 24, 2009 3:29 AM
  • i know this is thread is a little old, but the event type matches one that i get in an application.

    Event text is "system.data, P5 2.0.0.0, P6 4889deaf, P7 2490, P8 2c, P9 system.data.sqlclient.sql, P10 NIL."

    with Sysinternals DebugView'er running in the background i was able to cath the exception text, that statet an sql error: "duplicate key entry" in my primary key.
    in my application it is an design bug, but my guess would be that when running multi threadded your code or the database insert the same primary key and there by generating an error.

    But what puzzels me is that my application crashes even thoug there is plenty of try/catch statements.
    Thursday, November 19, 2009 8:36 AM
  • Thank you for your reply. My application does not insert a value for the primary key, it being auto generated from the server, I have two types depending on the tables, a few of them are Identity keys and the others are Guid columns which have a default value (newid()).
    What I did recently though was apply the using statements for the connection and the command objects (I had it only for the connection objects and some of the command objetcs).
    I also changed the database isolation level to use row versioning READ_COMMITTED_SNAPSHOT database option is set to ON

    It seems to be working without errors until now.
    Friday, November 20, 2009 12:06 AM