Error: Cannot create new connection because in manual or distributed transaction mode

[CJM]

Luke,

The first & second SP's both return a Scope_Identity() value for the record
that the SP Inserts.

However, I ignore the first one, but need to act on the second one. If I
ignored the returned value from the second recordset, I could continue to
use the original connection.

I understand what you mean about the connection potentially being occuped,
but I'm unclear as to how you think I should check this.

Thanks

Chris

"[MSFT]" <lukezhan@online.microsoft.com> wrote in message
news:eQwtUsrGEHA.2308@cpmsftngxa06.phx.gbl...

> Hi Chris,
>
> From another post, I know you have temporarily get around this problem by
> creating a new connection for the SP. My question is, if the second SP
> didn't return a Recordset, is there going to being such a problem? Since
> the new connection can work, the original connection seems to be

"occupied"

> by something. It looks to me that we may check the recordset first.
>
> Luke
> Microsoft Online Support
>
> Get Secure! [url]www.microsoft.com/security[/url]
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>

[[MSFT]]

Hi Chris,

You may check the connection's State after it execute the second SP, for
example:

Do While <some condition>

sSQL = "Exec etc"
oConn.Execute sSQL

sSQL = "Exec etc"
rs = oConn.Execute (sSQL)

Response.Write oConn.State

sSQL = "Exec etc"
oConn.Execute sSQL <---- fails here

sSQL = "Exec etc"
oConn.Execute sSQL

Loop

To see if it is in correct state (ready to execute next SP). Additionally,
you may try a client side cursor for the connection object to see if it
will help.

Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)