Strange error, need help! 
Author Message
 Strange error, need help!

Hi everybody,
        I'm getting the following error, -2147467259 Automation error -
Unspecified error, when I call one of my MTS components. I have a  business
layer component with a couple of functions that basically passes an SQL
statement to a data layer component, which it then executes it using ADO.
What I find strange is why is it that all but two of my functions work,
while the two of them return a -2147467259 error. All of these functions are
identical except for the SQL statement. I've tested the SQL statement and it
works find. I even tried copying the SQL statement from the function that
doesn't work and paste it into the function that did work, and still I get
the -2147467259 error. All of the functions in the business layer call the
same function in the data layer which executes the given SQL statement. Also
what I find strange is that when the data layer is running on my PC
(development PC) it all works fine, the problems begin when I move the data
layer onto the MTS server. Sometimes the error is, -2147467259 Method
'ExecuteSelect2' of object '_cisDatabaseFunctions' failed.

From what I can tell scanning through Microsoft's knowledge base is that I
might have a problem in the registry, client side or MTS server, I'm not
sure.

Both of these components are compiled using binary compatibility, and I'm
using VB6.

I could really use some help on this folks!

Thanks in advance!
Steve



Sun, 10 Nov 2002 03:00:00 GMT  
 Strange error, need help!
Does this help any?

PSS ID Number: Q234218
Article last modified on 10-28-1999

WINDOWS:2.1,6.0 sp1, sp2, sp3; winnt:2.0

WINDOWS winnt

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Transaction Server 2.0
- Microsoft Visual Studio 6.0 sp1, sp2, sp3
- Microsoft Data Access Components version 2.1
-------------------------------------------------------------------------------

SUMMARY
=======

When you use a single ADODB Connection object multiple times within an MTS
transaction, other ADO objects must be cleaned up after use. Also, client
cursors should be used when possible. If ADO objects are not cleaned up properly

ADO may rise an "Unspecified Error" [-2147467259 / 80004005] when executing
another operations against the database.

MORE INFORMATION
================

When within a transaction in MTS, using an open ADO Connection object on
multiple operations it is recommends the following:

 - All open recordsets be either disconnected; or closed and set to nothing
  before executing other operations,

 - All Command objects that are not used are set to nothing.

If these steps are not followed ADO might display an "Unspecified Error" or open

secondary connections to complete the operations.

This situation arises more frequently when within an MTS transaction.
A simple example of code that FAILS when it is run within an MTS transaction as
follows:

This is FAILING code:

      Public Sub DoStuff()
     On Error Goto ErrHandler

       Dim oConn As New ADODB.Connection
      Dim oCmd As New ADODB.Command
      Dim oRS As ADODB.Recordset

       oConn.Open sConnectionString

       Set oCmd.ActiveConnection = oConn
      oCmd.CommandText = "SELECT * FROM Authors"
      oCmd.CommandType = adCmdText
      Set oRS= oCmd.Execute
      '...Operate on Recordset...

       'This FAILS if executed within an MTS transaction with ADO
'Unspecified    Error' message:
      oConn.Execute "INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (
'edjez',25,100 )"

       GetObjectContext.SetComplete

      Exit Sub
     ErrHandler:
      GetObjectContext.SetAbort
      Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
Err.HelpContext
     End Sub

The preceding code should be optimized and organized as follows:

      Public Sub DoStuff()
     On Error Goto ErrHandler
      Dim oConn As ADODB.Connection
      Dim oCmd As ADODB.Command
      Dim oRS As ADODB.Recordset

       Set oConn = New ADODB.Connection
      oConn.Open sConnectionString
      'Here we specify cursor locations to adUseClient (3)
      'because we will be reusing the connection afterwards

       Set oCmd = New ADODB.Command
      Set oCmd.ActiveConnection = oConn
      oCmd.CommandText = "SELECT * FROM Authors"
      oCmd.CommandType = adCmdText

       Set oRS= oCmd.Execute
      '...Operate on Recordset...
      '...and we won't be needing it anymore so let's clean up
      oRS.Close
      Set oRS = Nothing
      Set oCmd = Nothing

       oConn.Execute "INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (
'edjez',25,100 )"

       oConn.Close
      Set oConn = Nothing

       GetObjectContext.SetComplete
     Exit Sub
     ErrHandler:
      GetObjectContext.SetAbort
      Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,
Err.HelpContext
     End Sub

Please note that the error is raised to the caller within the error handler, and

that the ADO objects are not created using ObjectContext.CreateInstance but
rather the New operators - this is because ADO objects do not need to be created

using ObjectContext.CreateInstance to participate in the current transaction (if

any). If you are using a custom wrapper for database access (which in turn could

be using ADO) you would need to create this wrapper using
ObjectContext.CreateInstance for it to participate in the MTS transactions.

REFERENCES
==========

For additional information about , please see the following article(s) in the
Microsoft Knowledge Base:

   Q186342 HOWTO: Create a 3-Tier App using VB, MTS and SQL Server

Quote:

> Hi everybody,
>         I'm getting the following error, -2147467259 Automation error -
> Unspecified error, when I call one of my MTS components. I have a  business
> layer component with a couple of functions that basically passes an SQL
> statement to a data layer component, which it then executes it using ADO.
> What I find strange is why is it that all but two of my functions work,
> while the two of them return a -2147467259 error. All of these functions are
> identical except for the SQL statement. I've tested the SQL statement and it
> works find. I even tried copying the SQL statement from the function that
> doesn't work and paste it into the function that did work, and still I get
> the -2147467259 error. All of the functions in the business layer call the
> same function in the data layer which executes the given SQL statement. Also
> what I find strange is that when the data layer is running on my PC
> (development PC) it all works fine, the problems begin when I move the data
> layer onto the MTS server. Sometimes the error is, -2147467259 Method
> 'ExecuteSelect2' of object '_cisDatabaseFunctions' failed.

> From what I can tell scanning through Microsoft's knowledge base is that I
> might have a problem in the registry, client side or MTS server, I'm not
> sure.

> Both of these components are compiled using binary compatibility, and I'm
> using VB6.

> I could really use some help on this folks!

> Thanks in advance!
> Steve



Mon, 11 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Strange error, need help!

2. need help solving strange OVERFLOW error

3. Need help debugging ADO error -- VERY Strange

4. Strange Strange Error

5. Font in List box VB5 strange problem - Help needed

6. Strange problem - need Help

7. strange behavior of control.focus() funktion. Need Help

8. Need help with strange extended selection list box problem

9. Strange result using InputB() - need help!

10. Need some strange help

11. Need help with strange extended selection list box problem

12. Need help with strange behavior

 

 
Powered by phpBB® Forum Software