Re: Error msg

Roy (Roy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> I use OLE DB to call a series of stored procedures to add records to
> database tables within one transaction. I got the following errors:
>
> Description: Cannot create new connection because in manual or distributed
> transaction mode.
> Source Description: Microsoft OLE DB Provider for SQL Server
>
> The stored procedures are very simple. I do not try to create a new
> connection explicitly. Neither do I try to use distributed transactions.
> The only thing that might be a little complicate is I use
> ISequentialStream to write blobs to table fields. The error msg confuses
> me. Any one has ideas about this?

This sound like something that happens because you have not retrieved
all data from a result set, before you initiate a new connection. With
some APIs thies leads to an immediate error that the connection is busy.
OLE DB's default behaviour is instead to open a second connection
behind your back. Which apparently does not succeed here.

You can prevent OLE DB to open extra connection by setting the data-
source property DBPROP_MULTICONNECTIONS to FALSE.

But the root of the problem is the unconsumed result sets. A common
gotcha here are the rowcount messages from UPDATE, INSERT and DELETE
statement. These you can turn off with SET NOCOUNT ON.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Having SQL Connection Open throughout C# windows application
    ... Passing the connection (and/or a transaction) between your forms and to all ... SQL Server 2005, this uses promotable transactions, which means that it uses ... > DataAccess layer). ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Word/Excel mailmerge problems
    ... it is a SQL server thing and that isn't my dept here and i have to rule out ... Also i posted on both Excel and Word ... ODBC drivers and OLE DB Providers. ... she can *try* using an ODBC or DDE connection. ...
    (microsoft.public.word.docmanagement)
  • Re: sp_reset_connection needed?
    ... if you are not using Sql Server 7 there is _no benefit_ to ... common way to modify connection state is to do things like change database ... connections that are closed with a transaction active, ... This alias is for newsgroup purposes only. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: ROWLOCK hint does not seem to have any effect
    ... Mike Epprecht, Microsoft SQL Server MVP ... > We use ROWLOCK hint but it looks like it does not do anything. ... > From one connection, I begin a transaction and update a record in a table ...
    (microsoft.public.sqlserver.clients)
  • RE: Snapshot isolation with pooled connections
    ... This test case demonstrates a bug in the Microsoft SQL Server 2005 JDBC ... the driver does not initialize connections properly. ... time a connection is borrowed from the pool, ... When a snapshot transaction conflict occurs, ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading