Error "Cannot start transaction while in firehose mode" in terminal server hosted legacy application...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SammyBar - 16 Jul 2005 20:33 GMT

Hi all,

We have a legacy client-server client application (developed with Centura
SQL Windows/32) that connects to a SQL Server 2000 database. In our LAN it
works without mayor flaws. Recently we added support to some remote offices
by using W2K terminal server. The decision was not to allow direct
connection of the terminal server (that is located on a demilitarized zone,
out of the corporate LAN firewall) to the internal SQL Server. We did the
following: We setup an "external" Sql Server 2000 database that relays the
stored procedure calls into the internal real SQL database. This is done by
making a set of stored procedures in the external server with the same
parameters that the rela one, but they only call the real sp by calling

Exec realserver.realdatabase.dbo.StoredProcName

This way the users connected to the terminal server can access the real
database without any changes to the legacy client application. This works.
Initially we had no mayor problems, but when the number of terminal server
users increased, we get more and more frequently the following error from
the client:
Microsoft Sql Server:7312[Microsoft][ODBC SQL Server Driver][SQL
Server][OLE/DB provider returned message: Cannot start transaction while in
firehose mode.] Microsoft SQL Server:7300[Microsoft]
When this errors happens, the user closes the connection to the terminal
server, reopens a new connection, opens again the legacy application and the
error reappears. I should stop and restart the SQL Server service on the
"external server" in order to allow the user operate normally.
We never had such error when working on the LAN, even when there are many
instances of the client application running on the same machine, so it is
not a fault of the client application. I suppoused it can be caused by
terminal server ODBC connection pooling. May be some way one instance is
using the other's corrupted connection. Then I disabled connection pooling
for the SQL Server driver in the terminal server and nothing changed.
Where is the problem?
This problems has a strategic importance for us 'cause we are considering
increase the number of remote offices connected by terminal server to the
corporate LAN.
Any idea is welcomed.

Thanks in advance
Sammy

Reply to this Message

Mike Epprecht (SQL MVP) - 17 Jul 2005 19:39 GMT

Hi

I would speak to your vendor about this. I have seen this problem in a lot
of applications where they were put onto terminal server and the developers
never implemented for it.

Also, make sure that you have the latest version of MDAC on the terminal
server. www.microsoft.com/data

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

> Hi all,
>
[quoted text clipped - 38 lines]
> Thanks in advance
> Sammy

Reply to this Message

SammyBar - 18 Jul 2005 14:49 GMT

> I would speak to your vendor about this. I have seen this problem in a lot
> of applications where they were put onto terminal server and the
> developers never implemented for it.

Application was developed in house. If anybody can point us to the problem
we can fix it. I'll investigate on the subject. Thanks for the hints.

Sammy

Reply to this Message

David Gugick - 18 Jul 2005 16:11 GMT

>> I would speak to your vendor about this. I have seen this problem in
>> a lot of applications where they were put onto terminal server and
[quoted text clipped - 3 lines]
> the hints.
> Sammy

Make sure SET NOCOUNT ON appears at the top of all stored procedures.
I've seen this error reported before with distributed transactions.
Also, always make sure all rows are consumed by the client in all
transactions. If you are using connection pooling, it's possible you are
executing a second query when there are still rows waiting to be
consumed.

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

Reply to this Message

SammyBar - 18 Jul 2005 16:30 GMT

> Make sure SET NOCOUNT ON appears at the top of all stored procedures. I've
> seen this error reported before with distributed transactions.

What is the problem with this?

> Also, always make sure all rows are consumed by the client in all
> transactions. If you are using connection pooling, it's possible you are
> executing a second query when there are still rows waiting to be consumed.

This is a possible cause. We have faced this problem with old code some
times. Connection pooling is now disabled. I'll be monitoring the behaviour.

Thanks for your help
Sammy

Reply to this Message

David Gugick - 18 Jul 2005 17:39 GMT

>> Make sure SET NOCOUNT ON appears at the top of all stored
>> procedures. I've seen this error reported before with distributed
[quoted text clipped - 16 lines]
>> www.imceda.com
>> www.quest.com

When SET NOCOUNT ON is not set, it causes what I call a phantom result
set to be sent to the client which can cause problems with ADO. It's not
a result set, per se, but ADO thinks there is something there. There's
also no reason to send affected row counts from the server to the client
with every SQL statement. It just adds unnecessary network traffic and
work for SQL Server and is not required in production environments. The
option prevents the row count affected message you see in Query
Analyzer. For example:

(37 Row(s) affected)

An application should set the SET NOCOUNT ON immediately after
connecting to the server by issuing that command and it should also
appear as the first statement in every stored procedure. Triggers are
exempt.

Signature

David Gugick
Quest Software
www.imceda.com
www.quest.com

Reply to this Message

sergio - 19 Jul 2005 04:28 GMT

Hi Sammy,

if you're calling the SQL Server proc, using ADO Execute Method, it will
default to Firehose Cursor (the same as FAST_FORWARD). Distributed
Transaction are not allowed when Firehose cursors are used.

Try to open the ResultSet, or chage the Cursor.

best regard,
Sérgio Monteiro

> Hi all,
>
[quoted text clipped - 37 lines]
> Thanks in advance
> Sammy

Reply to this Message