none
Sporatic E_FAIL errors using Native Client in OLEDB

    Question

  • Hello fellow Devs,

    I've got a program that uses SQL Server Native Client 2005 to connect to SQL Server 2000 through OLEDB (C++). While for the most part everything works like a charm, I'm getting sporatic E_FAIL errors on doing things like selects, updates, etc. These happen about 5 times per 1000 queries. MSDN only says "
    A provider-specific error occurred" about E_FAIL. How do I figure out what's going on and stop it?

    Thanks a ton in advance,
    Jeff

    Edit: It's important to note that there are a few other applications running at the same time that run queries on the same tables. Could this be a lock or too many connections issue? If so, how do I fix it?
    Wednesday, July 02, 2008 4:27 PM

Answers

  • I would say go for it. This approach benefits you with connection pooling and absence of connection ownership bottleneck.

    Tuesday, July 08, 2008 5:08 PM
    Moderator

All replies

  • You would need to retrieve the error information through the appropriate interface, see: http://technet.microsoft.com/en-us/library/ms174513(SQL.100).aspx.

     

    Hope that helps,

     

    John

    Thursday, July 03, 2008 1:59 AM
    Moderator
  • I followed the example in the link, but the function failed to get the error description. The hresult was DB_E_NOLOCALE which according to MSDN means:

    The locale ID specified by the lcid parameter in IErrorRecords::GetErrorInfo was not supported by the provider.

    So what's the next step?

    Edit: here's the entire output:

    HRESULT           = 80004005
    clsid             = {85ECAFCC-BDD9-4B03-97A8-FA65CBE3859B}
    iid               = {0C733A63-2A1C-11CE-ADE5-00AA0044773D}
    dispid            = 0
    Native Error Code = 0
    Failed to retrieve standard error description
    Thursday, July 03, 2008 9:34 PM
  • I tried another set of error retrieval code and got the following:

    HRESULT:        0X80004005
    SQLSTATE:       08001
    Native Error:   1326
    Error state:    1
    Severity:       16
    Procedure:
    Line:   0
    HRESULT:        0X80004005
    SQLSTATE:       08001
    Native Error:   1326
    HRESULT:        0X80004005
    SQLSTATE:       HYT00
    Native Error:   0

    This comes from http://blogs.msdn.com/sqlserverue/archive/2007/11/20/using-ole-db-extensions-in-sql-server-native-client-10-0-9sql-server-2008-to-change-the-password-of-a-sql-server-authentication-user-account.aspx

    Any idea what these errors mean?

    Edit: A different one popped up when i was connecting locally:

    HRESULT:        0X80004005
    SQLSTATE:       08001
    Native Error:   10048
    Error state:    1
    Severity:       16
    Procedure:
    Line:   0
    HRESULT:        0X80004005
    SQLSTATE:       08001
    Native Error:   10048
    HRESULT:        0X80004005
    SQLSTATE:       HYT00
    Native Error:   0
    Thursday, July 03, 2008 10:13 PM
  • After a bit of further research...it appears the last error code (10048) means: "Only one usage of each socket address (protocol/network address/port) is normally permitted."

    I did a netstat -n and behold the source of the problem (it starts around port 1):

      TCP    165.91.213.193:4979    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4980    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4981    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4982    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4983    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4984    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4985    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4986    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4987    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4988    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4989    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4990    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4991    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4992    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4993    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4994    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4995    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4996    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4997    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4998    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:4999    165.91.213.193:1433    TIME_WAIT
      TCP    165.91.213.193:5000    165.91.213.193:1433    TIME_WAIT

    So its clear that my problem has something to do with ports and sockets. Does anyone have any idea how to fix this?
    Thursday, July 03, 2008 10:57 PM
  • I believe that TIME_WAIT indicates the sockets which are closed but for which maximum segment lifetime is not reached.
    You do not use any connection pooling in your application, do you?
    Do you open a lot of independent connections?

    Thursday, July 03, 2008 11:11 PM
    Moderator
  • My setup is one session object (and thus only one connection) creating multiple commands (a new command is made when the last one generated a rowset that needs to remain in use). Whenever the my classes are done with their rowsets, they release the rowset and command. Everything is released appropriately, and I have no memory leaks. Is this what's meant by connection pooling?

    It's important to note that I don't see this behavior running the same program on a Windows Vista machine running SQL Server 2005 Express.

    Edit: It appears a new port is used for every sql query...why on earth would that happen when there's only one session? BTW, the box that's having the problem is Windows Server 2003, SQL Server 2000.

    Edit 2: I read somewhere that a IDataInitialize interface had to remain active throughout the program for connection pooling to work. Is this really the case? I use
    IDBInitialize::Initialize instead of IDataInitialize.
    Thursday, July 03, 2008 11:25 PM
  • You might have implicit connections being opened and they are not pooled.
    See
    PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled



    Friday, July 04, 2008 12:16 AM
    Moderator
  • Thanks, that would be the issue...I'm creating a ton of rowsets, but what I don't understand is that only about 5 or so are in operation at any given time. I call IRowset::Release when I'm done with them. I even release the command that used the rowset. That leads me to assume that SQL Server 2000 doesn't reuse connections in TIME WAIT status. Is that accurate?

    Anyway, here is the solution given by the article:

    "To ensure pooling, you must create additional data source/session pairs, or explicitly create ActiveX Data Objects (ADO) connections. By explicitly creating the ADO connection or OLE DB Session, you can be guaranteed that the connection will be pooled by OLE DB, which means that the next request for an ADO connection or OLE DB Session can reuse an existing session and SQL Server connection."

    It would be absolutely ludicrous to create a new data source and session everytime i needed to make a select statement! As for the rest of the paragraph, it doesn't seem very clear...could anyone explain in simple OLEDB terms what needs to be done?

    BTW, can this be resolved by setting DBPROP_MULTIPLECONNECTIONS to VARIANT_FALSE?

    Thanks for all the help!! It's much appreciated!
    Friday, July 04, 2008 1:08 AM
  • By setting DBPROP_MULTIPLECONNECTIONS to VARIANT_FALSE you disable implicit connections. In this case if your connection is busy with the results from the previous query and you try to run a different query on the same connection you'll end-up with an error.

     

    Only primary connections are pooled. Check this out http://msdn.microsoft.com/en-us/library/ms810829.aspx. See the OLE DB Services section.

     

    What are you trying to achieve?

    Friday, July 04, 2008 10:33 PM
    Moderator
  • Here is what I'm trying to accomplish:

    I created a database interface to make it easy for other programmers on my team to create C++ applications to (initially) connect to SQL Server Compact Edition databases on embedded devices. Later I took the same model for the interface and integrated SQL Server Native Client to allow connections to desktop servers. The interface is setup as following:

    I have a DBInterface class that does the following (among many other things):

    1 IDBInitialize object that handles the original connection
    That Initialize object then creates an IDBCreateSession object
    That CreateSession object then creates an IDBCreateCommand object
    That CreateCommand object then creates an ICommandText object

    From there I have another class (DBDataStore), that holds a pointer to an ICommandText object, IRowset object, and subsequent objects for processing rowsets. I can have multiple of these classes in use at any given time.

    Whenever a select statement is made (at the DBInterface), the ICommandText and IRowset are placed in the DBDataStore class specified in the call. Then the CreateCommand object creates a new ICommandText to have ready for the next query.

    Thus I can have multiple DBDataStore objects, each able to cycle through their respective rowsets at the programmer's request.

    This framework worked great for SSCE and SQL Server 2005 Express. Does this implicit connection issue only affect SQL Server 2000?

    How could I change this such that I could maintain the functionality but not have the problem of so many implicit connections?

    Y'all are the best!
    Monday, July 07, 2008 2:40 PM
  • You can use Multple Active Result Sets (MARS) against SQL Server 2005, otherwise you might try to see whether you can use client-side cursors.
    Monday, July 07, 2008 7:05 PM
    Moderator
  • Your architecture is vulnerable for connection ownership since you have multiple ICommandText / IRowset objects corresponding to one session which encapsulates a physicall connection.

     

    Implicit connections issue is a feature of SQL Server Native Client. By creating an implicit connection under the hood you eliminate the bottlenack so I would stick to it from the performance perspective.

     

    Using MARS would be an implicit connections workaround but it's available in SQL Server 2005 and later versions.

     

    Other option would be to synchronize connection access across all threads using mutex.

    Monday, July 07, 2008 10:11 PM
    Moderator
  • Would creating a seperate session for each DBDataStore class be a good option? That way each object gets it's own connection, and only one command/rowset object pair. It at least solves the problem of creating thousands of implicit connections that get used only once and thrown away.

    I'm not sure I know about connection ownership vulnerability, would I still be susceptible?
    Tuesday, July 08, 2008 4:46 PM
  • I would say go for it. This approach benefits you with connection pooling and absence of connection ownership bottleneck.

    Tuesday, July 08, 2008 5:08 PM
    Moderator
  • Awesome! Glad to get a solution.

    Do you per chance know if this is an acceptable way to set up the interface for SQL Server Compact Edition as well?

    Thanks!
    Tuesday, July 08, 2008 5:52 PM