Error beginning first transaction with Vb+Ado+Sql7 
Author Message
 Error beginning first transaction with Vb+Ado+Sql7

Using Vb5-6+SP3+ADO2.1+SQL7, opening a new transaction, i receive the
following error: "Run-time error '-2147168227 (8004d01d)': A new transaction
could not be created due to a unspecified capacity issue". Well, i know that
only one transaction may be active at one time: but this is the first one
anyway! Here is the code:

======
Private Sub Command1_Click()

Dim cnNorthwind As New ADODB.Connection

Dim rsEmployees As New ADODB.Recordset
Dim rsEmployees1 As New ADODB.Recordset

'Opens the connection to Northwind database
cnNorthwind.Open "Provider=SQLOLEDB;User Id=sa;Initial Catalog=Northwind"

'Opens a dynamic view on employees
rsEmployees.Open "Select * From Employees where EmployeeID=1", cnNorthwind,
adOpenDynamic, adLockPessimistic, adCmdText

'Opens a firehose (forwardonly-readonly) view on employees
rsEmployees1.Open "Select * From Employees where EmployeeID=2", cnNorthwind,
adOpenForwardOnly, adLockReadOnly, adCmdText

'Begin of a new transaction: here is raised the error!
cnNorthwind.BeginTrans '<--- This gives the error.

   rsEmployees!LastName = "Davolio1"

   rsEmployees.Update

cnNorthwind.CommitTrans

End Sub

=====

Note that if the rsEmployees1.open is different than "adOpenForwardOnly",
everything works ok.

So, what's the issue?

Fabio Pagano



Sat, 26 Jan 2002 03:00:00 GMT  
 Error beginning first transaction with Vb+Ado+Sql7
Your locking is pessimistic.  Might that be the problem?

Joe H

Quote:

>Using Vb5-6+SP3+ADO2.1+SQL7, opening a new transaction, i receive the
>following error: "Run-time error '-2147168227 (8004d01d)': A new
transaction
>could not be created due to a unspecified capacity issue". Well, i know
that
>only one transaction may be active at one time: but this is the first one
>anyway! Here is the code:

>======
>Private Sub Command1_Click()

>Dim cnNorthwind As New ADODB.Connection

>Dim rsEmployees As New ADODB.Recordset
>Dim rsEmployees1 As New ADODB.Recordset

>'Opens the connection to Northwind database
>cnNorthwind.Open "Provider=SQLOLEDB;User Id=sa;Initial Catalog=Northwind"

>'Opens a dynamic view on employees
>rsEmployees.Open "Select * From Employees where EmployeeID=1", cnNorthwind,
>adOpenDynamic, adLockPessimistic, adCmdText

>'Opens a firehose (forwardonly-readonly) view on employees
>rsEmployees1.Open "Select * From Employees where EmployeeID=2",
cnNorthwind,
>adOpenForwardOnly, adLockReadOnly, adCmdText

>'Begin of a new transaction: here is raised the error!
>cnNorthwind.BeginTrans '<--- This gives the error.

>   rsEmployees!LastName = "Davolio1"

>   rsEmployees.Update

>cnNorthwind.CommitTrans

>End Sub

>=====

>Note that if the rsEmployees1.open is different than "adOpenForwardOnly",
>everything works ok.

>So, what's the issue?

>Fabio Pagano



Sat, 26 Jan 2002 03:00:00 GMT  
 Error beginning first transaction with Vb+Ado+Sql7

Quote:
Joe H wrote...
>Your locking is pessimistic.  Might that be the problem?

No. The same happens with other lock types (with adLockReadOnly too!).
Anyway, it seems to be a behaviour "by design".

Fabio Pagano



Sat, 26 Jan 2002 03:00:00 GMT  
 Error beginning first transaction with Vb+Ado+Sql7
Is there anyone from Microsoft who can explain this ? I have the same
problem and the Microsoft Knowledge base has nothing about it. Sorry not to
help you guys but, your not alone having this weird error.
The native error number is also absent from the Errors object (???).
Bye


Quote:
> Using Vb5-6+SP3+ADO2.1+SQL7, opening a new transaction, i receive the
> following error: "Run-time error '-2147168227 (8004d01d)': A new
transaction
> could not be created due to a unspecified capacity issue". Well, i know
that
> only one transaction may be active at one time: but this is the first one
> anyway! Here is the code:

> ======
> Private Sub Command1_Click()

> Dim cnNorthwind As New ADODB.Connection

> Dim rsEmployees As New ADODB.Recordset
> Dim rsEmployees1 As New ADODB.Recordset

> 'Opens the connection to Northwind database
> cnNorthwind.Open "Provider=SQLOLEDB;User Id=sa;Initial Catalog=Northwind"

> 'Opens a dynamic view on employees
> rsEmployees.Open "Select * From Employees where EmployeeID=1",
cnNorthwind,
> adOpenDynamic, adLockPessimistic, adCmdText

> 'Opens a firehose (forwardonly-readonly) view on employees
> rsEmployees1.Open "Select * From Employees where EmployeeID=2",
cnNorthwind,
> adOpenForwardOnly, adLockReadOnly, adCmdText

> 'Begin of a new transaction: here is raised the error!
> cnNorthwind.BeginTrans '<--- This gives the error.

>    rsEmployees!LastName = "Davolio1"

>    rsEmployees.Update

> cnNorthwind.CommitTrans

> End Sub

> =====

> Note that if the rsEmployees1.open is different than "adOpenForwardOnly",
> everything works ok.

> So, what's the issue?

> Fabio Pagano



Mon, 28 Jan 2002 03:00:00 GMT  
 Error beginning first transaction with Vb+Ado+Sql7
These are Microsoft's words:

"When a forward read-only server side cursor requests a recordset, it
implies the client wants the records as fast as possible.  As a result a
the data store is free'ed from the overhead of creating a cursor, and one
will not be created.  Therefore the connection to the data store is blocked
until all the records are returned."

In other words, this is due to the cursorless recordset (fire-hose).

To me, it is a very clear explanation, and i think that this behaviour is
right. I have solved the problem using the adOpenDynamic, adOpenKeyset or
adOpenStatic. The problem may also be solved closing the second recordset
(the fire-hose one) before the BeginTrans.

Fabio Pagano

Quote:
weso wrote...
>Is there anyone from Microsoft who can explain this ? I have the same
>problem and the Microsoft Knowledge base has nothing about it. Sorry not to
>help you guys but, your not alone having this weird error.
>The native error number is also absent from the Errors object (???).
>Bye



>> Using Vb5-6+SP3+ADO2.1+SQL7, opening a new transaction, i receive the
>> following error: "Run-time error '-2147168227 (8004d01d)': A new
>transaction
>> could not be created due to a unspecified capacity issue". Well, i know
>that
>> only one transaction may be active at one time: but this is the first one
>> anyway! Here is the code:

>> ======
>> Private Sub Command1_Click()

>> Dim cnNorthwind As New ADODB.Connection

>> Dim rsEmployees As New ADODB.Recordset
>> Dim rsEmployees1 As New ADODB.Recordset

>> 'Opens the connection to Northwind database
>> cnNorthwind.Open "Provider=SQLOLEDB;User Id=sa;Initial Catalog=Northwind"

>> 'Opens a dynamic view on employees
>> rsEmployees.Open "Select * From Employees where EmployeeID=1",
>cnNorthwind,
>> adOpenDynamic, adLockPessimistic, adCmdText

>> 'Opens a firehose (forwardonly-readonly) view on employees
>> rsEmployees1.Open "Select * From Employees where EmployeeID=2",
>cnNorthwind,
>> adOpenForwardOnly, adLockReadOnly, adCmdText

>> 'Begin of a new transaction: here is raised the error!
>> cnNorthwind.BeginTrans '<--- This gives the error.

>>    rsEmployees!LastName = "Davolio1"

>>    rsEmployees.Update

>> cnNorthwind.CommitTrans

>> End Sub

>> =====

>> Note that if the rsEmployees1.open is different than "adOpenForwardOnly",
>> everything works ok.

>> So, what's the issue?

>> Fabio Pagano



Mon, 28 Jan 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Error beginning first transaction with Vb+Ado+Sql7

2. Stored Procedure and ADO.net Begin Transaction Code

3. ADO SQL7 and nested transactions

4. Access97 ADO SQL7 transactions - undefined recordset after COMMIT

5. ADO Begin/Commit Transactions

6. SQL7 + MTS + ADO : Select with where clause : Locks entrire table until Transaction ended

7. ADODB TRANSACTION ADO ERROR No Transaction is Active

8. ADODB TRANSACTION ADO ERROR No Transaction is Active

9. ADO and Transaction in SQL Server 7.0: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

10. Begin-Rollback-Commit Transaction

11. Workspace Transaction Begin/Commit/Rollback bug?

12. Limits on Transactions between Begin & Commit

 

 
Powered by phpBB® Forum Software