Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: SQL server OLEDB - Error

    Hi,

    I am using WIN XP, SQL Server 2000 database and vb, asp application running on MTS. I have a transaction where it will process 500 items and write to the tables in database. When opening the connection I tried to cache the connection and made use of it when ever the code is called. Here is how I did it.

    Public Function GetCachedADOConn(ByVal eConnect As DBConnectVar, Optional ByVal blnUseShapeRS As Boolean = False) _
    As ADODB.Connection

    Dim cnnADO As ADODB.Connection

    On Error GoTo EH

    If m_CachedConnection Is Nothing Then
    Set cnnADO = New ADODB.Connection
    ' Check if we need to load the cache first...
    If Len(mstrConnect(eConnect - 1)) = 0 Then Call CacheOneConnectionSet(eConnect)

    ' Now just return the cached data
    With cnnADO
    If blnUseShapeRS Then
    .ConnectionString = "Provider=MSDataShape;Data " & mstrConnect(eConnect - 1)
    Else
    .ConnectionString = mstrConnect(eConnect - 1)
    End If

    .CommandTimeout = mintCommandTimeout(eConnect - 1)

    .ConnectionTimeout = mintConnectTimeout(eConnect - 1)

    .CursorLocation = adUseClient

    .Open
    End With
    Set m_CachedConnection = cnnADO
    End If

    Set GetCachedADOConn = m_CachedConnection
    'Set cnnADO = Nothing
    Exit Function


    Here the main part is making use of m_CachedConnection as a public variable declared as adodb.connection.


    When I run the application it throws me an error

    Unable to establish connection(Micorsoft OLEDB Provider for SQL Server , New transaction cannot enlist in the specified transaction coordinator.)

    Can some one help me How I can address the above issue.

    Thank you,

    Nagesh.

  2. #2
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Question

    Does this error appear in this code posted? It seems that you're trying to begin a new transaction before commit the last one. It was not clear the entire content of your connectionstring.

    On MTS is very common a situation where you try to begin many transactions in separated codes (by trying manually begin the transactions). Take a look in the entire code that treats this (or these) transaction.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Why are you trying to cache the connection when oledb uses object pooling ?

  4. #4
    Join Date
    Jan 2004
    Posts
    3

    SQL Server, OLEDB error(New transaction cannot enlist)

    Hi ,

    Thanks for replying to my question. I am using the code for open connection for creating shape records as well as sql statements. And this code is also used in conjunction with objectcontext while updating database. In my code I use the code described before to open a connection and execute to create a shape recordset. And then based on that recordset I update the table. Actual scenario is, I create the instance of object( COM Object), Call the function GetSQLRecordset with in which i call the function described before to open the connection( as well as cache the connection), execute the sql and then finally after all my selects and updates I set the COM object to nothing.I want to know whether setting the com object to nothing will set my connection to nothing or not. When I set this to nothing, will this close my connection or not. I am a bit confused regarding this. I know ado uses .close to close the connection. How MTS handles the database connections. Please help me understand this concept. I really appreciate this help.

    Thank you,

    Nagesh.


    Originally posted by fabiano
    Does this error appear in this code posted? It seems that you're trying to begin a new transaction before commit the last one. It was not clear the entire content of your connectionstring.

    On MTS is very common a situation where you try to begin many transactions in separated codes (by trying manually begin the transactions). Take a look in the entire code that treats this (or these) transaction.

  5. #5
    Join Date
    Jan 2004
    Posts
    3

    SQL server OLEDB - Error

    Hi,

    Thank You for your reply. Could you explain me how object pooling works. Or if you can suggest me reading i can make use of, it would be of great help.

    Thank You,

    Nagesh.
    Originally posted by rnealejr
    Why are you trying to cache the connection when oledb uses object pooling ?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Here is a link (the article is old so the newer mdac versions take care of some of the registry issues):

    link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •