I'm getting this error message in one of my procedures... Has anyone ever
seen this, I've searched the group's most messages I found mention a firehos
e
cursor which I know I'm not using... Here's the procedure thats generating
the error, this is the first time I've seen this error the application has
been working fine in production for months. I think this may have to do with
the fact that I'm using a linked server and I get an implied transaction whe
n
I use it, can I turn this off...?
Here's was the whole error message.
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Cannot start transaction while in
firehose mode]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x80004005: ].
CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
@.SalesPersonID int = NULL OUTPUT,
@.VendorCode CHAR(10),
@.VendorGroup CHAR(10)
AS
BEGIN
DECLARE @.SSN AS CHAR(11),
@.Name AS CHAR(50),
@.FirstName AS VARCHAR(50),
@.LastName AS VARCHAR(50),
@.Index AS INT
SELECT
@.SSN = ven_tax_id,
@.Name = ven_name
FROM
LinkedServer.database.dbo.databasetable
WHERE
ven_cd = @.VendorCode AND
ven_grp_cd = @.VendorGroup
INSERT INTO SalesPersons (VendorCode,VendorGroup,SSN,FirstName,La
stName)
VALUES (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
SET @.SalesPersonID = @.@.IDENTITY
END
GOAlien2_51 wrote:
> I'm getting this error message in one of my procedures... Has anyone
> ever seen this, I've searched the group's most messages I found
> mention a firehose cursor which I know I'm not using... Here's the
> procedure thats generating the error, this is the first time I've
> seen this error the application has been working fine in production
> for months. I think this may have to do with the fact that I'm using
> a linked server and I get an implied transaction when I use it, can I
> turn this off...?
> Here's was the whole error message.
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Cannot start transaction while in
> firehose mode]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x80004005: ].
>
> CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
> @.SalesPersonID int = NULL OUTPUT,
> @.VendorCode CHAR(10),
> @.VendorGroup CHAR(10)
> AS
> BEGIN
> DECLARE @.SSN AS CHAR(11),
> @.Name AS CHAR(50),
> @.FirstName AS VARCHAR(50),
> @.LastName AS VARCHAR(50),
> @.Index AS INT
> SELECT
> @.SSN = ven_tax_id,
> @.Name = ven_name
> FROM
> LinkedServer.database.dbo.databasetable
> WHERE
> ven_cd = @.VendorCode AND
> ven_grp_cd = @.VendorGroup
> INSERT INTO SalesPersons
> (VendorCode,VendorGroup,SSN,FirstName,La
stName) VALUES
> (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
> SET @.SalesPersonID = @.@.IDENTITY
> END
> GO
What type of cursor are you using if not a firehose (Server / read-only
/ forward-only)?
Maybe you could try wrapping the linked server call in a transaction? Is
that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
set?
David Gugick
Imceda Software
www.imceda.com|||Not using a cursor, anywhere... The @.Name and @.SSN are being read from the
linked server, the first and last name are parsed from @.Name but that code
was irrelevant so I omitted it. The distributed transaction is implied even
though it's only a read on the linked server, MS DTC will still create a
transaction if the remote server supports the "ITransactionJoin" interface a
t
least thats what BOL says. I'd like to try and prevent the creation of the
transaction with the REMOTE_PROC_TRANSACTIONS option but I think I'll have t
o
create a procedure with output parameters on the linked server rather than
just reading from the table directly.
Here's an excerpt from BOL titled (Transact-SQL Distributed Transaction)...
I'm interpreting this as if I can create a procedure on the target of the
linked server execute it, read the output parameters and as long as I have
set the REMOTE_PROC_TRANSACTIONS OFF option for the connection I shouldn't
get a distributed transaction from MS DTC, am I correct in that assumption,
anyone...?
Starting Distributed Transactions
You can start distributed transactions in Transact-SQL in these ways:
Start an explicit distributed transaction using the BEGIN DISTRIBUTED
TRANSACTION statement.
You can also execute a distributed query against a linked server. The SQL
Server you have connected to calls MS DTC to manage the distributed
transaction with the linked server. You can also call remote stored
procedures on a remote SQL Server as part of the distributed transaction.
While in a local transaction, execute a distributed query.
If the OLE DB data source supports the ITransactionJoin interface, the
transaction is promoted to a distributed transaction, even if the query is a
read-only query. If the data source does not support ITransactionJoin, only
read-only statements are allowed.
If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction
calls a remote stored procedure on another SQL Server, the local transaction
is promoted to a distributed transaction.
SQL Server uses MS DTC to coordinate the transaction with the remote server.
Calls to remote stored procedures execute outside the scope of a local
transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the
remote procedure is not rolled back if the local transaction is rolled back.
The work done by the remote stored procedure is committed at the time the
procedure completes, not when the local transaction is committed.
The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects
only remote stored procedure calls made to remote servers defined using
sp_addserver. For more information about remote stored procedures, see Remot
e
Stored Procedure Architecture. The option does not apply to distributed
queries that execute a stored procedure on a linked server defined using
sp_addlinkedserver. For more information about distributed queries, see
Distributed Queries.
"David Gugick" wrote:
> Alien2_51 wrote:
> What type of cursor are you using if not a firehose (Server / read-only
> / forward-only)?
> Maybe you could try wrapping the linked server call in a transaction? Is
> that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
> set?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Not using a cursor, anywhere... The @.Name and @.SSN are being read
You always use a cursor when selecting data. It's inherent in the
mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
are setting parameters which define what type of cursor is used. Don't
confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
same thing. When you issue a query from QA, for example, QA is probably
using a firehose cursor, same when you open a table from SQL EM.
If this is run from your application, you are either setting these
parameters or using the defaults. A firehose cursor is nothing more than
using what SQL Server generates at the back-end. That is a server-side,
read-only, forward-only result set. If you can, change this result set
to a static, client-side cursor and it may eliminate the problem.
David Gugick
Imceda Software
www.imceda.com|||I remember reading that a firehose cursor is an obsolete term for a default
result set in BOL. How do I tell SQL Server to use something other than the
default cursor when selecting data..? How is that going to prevent the
distributed transaction..?
"David Gugick" wrote:
> Alien2_51 wrote:
> You always use a cursor when selecting data. It's inherent in the
> mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
> are setting parameters which define what type of cursor is used. Don't
> confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
> same thing. When you issue a query from QA, for example, QA is probably
> using a firehose cursor, same when you open a table from SQL EM.
> If this is run from your application, you are either setting these
> parameters or using the defaults. A firehose cursor is nothing more than
> using what SQL Server generates at the back-end. That is a server-side,
> read-only, forward-only result set. If you can, change this result set
> to a static, client-side cursor and it may eliminate the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> I remember reading that a firehose cursor is an obsolete term for a
> default result set in BOL. How do I tell SQL Server to use something
> other than the default cursor when selecting data..? How is that
> going to prevent the distributed transaction..?
>
What are you programming in? This is a client, data-access library
issue, not a SQL Server one. Firehose cursors are still around, even in
.Net, although are are implemented by the datareader object, and are
considered the fastest method to return data to the client. There are
really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
more information:
Firehose Cursors
Traditional ADO exposes four different types of cursors that change how
the ADO Recordset object functions. An ADO Recordset object behaves very
differently depending on how its CursorType property is set. For
example, by setting the CursorType to adOpenForwardOnly, the Recordset
remains connected to its data source and must be traversed in a
forward-only direction. However, when you set the CursorType property to
adOpenDynamic, the Recordset can be traversed forward or backward, or
you can even jump the cursor to a particular row. Through its CursorType
and CursorLocation properties, the ADO Recordset object takes the
approach of wrapping many solutions into a single object. ADO.NET takes
a different approach where it has distinct objects and methods that are
designed to handle specific situations.
In classic ADO forward-only, read-only cursors are implemented by
setting the CursorType to adOpenForwardOnly and the CursorLocation to
adUseServer (which are also the default settings). This makes the
Recordset object take the form of a forward-only, server-side cursor.
The MoveNext method repositions the Recordset to the next row and the
MovePrevious method is not allowed at all. You can, however, invoke the
MoveFirst method on a Recordset. It is a bit misleading, however, as it
does not reposition itself to the beginning of the current rowset.
Rather, it invokes the original SQL statement and refills the Recordset
from scratch, thus moving to the first record again.
This can easily be seen by opening the SQL Profiler tool and watching
the SQL execute every time the MoveFirst method is executed on a
traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
type of cursor is commonly used in applications where thousands (or
more) rows need to be traversed one at a time or when a smaller rowset
is required but only needs to be traversed once, perhaps to be loaded
into a pick list:
'-- Forward-only Firehose Cursor in ASP and ADO
Set oRs.ActiveConnection = oCn
oRs.LockType = adLockReadOnly
oRs.CursorType = adOpenForwardOnly
oRs.CursorLocation = adUseServer
oRs.Open sSQL
The closest equivalent to this type of traditional ADO firehose cursor
is the ADO.NET DataReader object. Just like the traditional forward-only
ADO Recordset, the DataReader remains connected to its data source while
it is open and can be traversed only in a forward direction. However,
there are differences. One is that individual DataReader types are
written specifically for a data provider such as SQL Server? (the
SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
The ADO.NET DataReader object is very efficient as it is built
specifically for the purpose of implementing forward-only, read-only
cursors. The classic ADO forward-only cursor is implemented through the
same Recordset object as a disconnected Recordset or even as a data
source-sensitive Recordset. The DataReader is designed solely to be a
lean firehose cursor.
' ASP.NET and ADO.NET in Visual Basic .NET
Dim oDr As SqlDataReader = oCmd.ExecuteReader()
// ASP.NET and ADO.NET in C#
SqlDataReader oDr = oCmd.ExecuteReader();
David Gugick
Imceda Software
www.imceda.com|||David,
Thank you very much for your time in helping me on this, it's very much
appreciated! The client application is written in VB.NET and uses Rocky
Lohtkas CSLA component framework. The stored procedure that's called that
generates this error is in the save method of one of my components, there's
no data reader there, although I do use DataReaders to populate the objects
with data. I'm still scepticle that this is a SQL Server (MS DTC) issue and
not a client cursor problem because the ITransactionJoin interface is
something MS DTC uses, right...? Also I have a powerbuilder application tha
t
has generated the same exact error.
Dan
"David Gugick" wrote:
> Alien2_51 wrote:
> What are you programming in? This is a client, data-access library
> issue, not a SQL Server one. Firehose cursors are still around, even in
> ..Net, although are are implemented by the datareader object, and are
> considered the fastest method to return data to the client. There are
> really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
> more information:
> Firehose Cursors
> Traditional ADO exposes four different types of cursors that change how
> the ADO Recordset object functions. An ADO Recordset object behaves very
> differently depending on how its CursorType property is set. For
> example, by setting the CursorType to adOpenForwardOnly, the Recordset
> remains connected to its data source and must be traversed in a
> forward-only direction. However, when you set the CursorType property to
> adOpenDynamic, the Recordset can be traversed forward or backward, or
> you can even jump the cursor to a particular row. Through its CursorType
> and CursorLocation properties, the ADO Recordset object takes the
> approach of wrapping many solutions into a single object. ADO.NET takes
> a different approach where it has distinct objects and methods that are
> designed to handle specific situations.
> In classic ADO forward-only, read-only cursors are implemented by
> setting the CursorType to adOpenForwardOnly and the CursorLocation to
> adUseServer (which are also the default settings). This makes the
> Recordset object take the form of a forward-only, server-side cursor.
> The MoveNext method repositions the Recordset to the next row and the
> MovePrevious method is not allowed at all. You can, however, invoke the
> MoveFirst method on a Recordset. It is a bit misleading, however, as it
> does not reposition itself to the beginning of the current rowset.
> Rather, it invokes the original SQL statement and refills the Recordset
> from scratch, thus moving to the first record again.
> This can easily be seen by opening the SQL Profiler tool and watching
> the SQL execute every time the MoveFirst method is executed on a
> traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
> type of cursor is commonly used in applications where thousands (or
> more) rows need to be traversed one at a time or when a smaller rowset
> is required but only needs to be traversed once, perhaps to be loaded
> into a pick list:
> '-- Forward-only Firehose Cursor in ASP and ADO
> Set oRs.ActiveConnection = oCn
> oRs.LockType = adLockReadOnly
> oRs.CursorType = adOpenForwardOnly
> oRs.CursorLocation = adUseServer
> oRs.Open sSQL
> The closest equivalent to this type of traditional ADO firehose cursor
> is the ADO.NET DataReader object. Just like the traditional forward-only
> ADO Recordset, the DataReader remains connected to its data source while
> it is open and can be traversed only in a forward direction. However,
> there are differences. One is that individual DataReader types are
> written specifically for a data provider such as SQL Server? (the
> SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
> The ADO.NET DataReader object is very efficient as it is built
> specifically for the purpose of implementing forward-only, read-only
> cursors. The classic ADO forward-only cursor is implemented through the
> same Recordset object as a disconnected Recordset or even as a data
> source-sensitive Recordset. The DataReader is designed solely to be a
> lean firehose cursor.
> ' ASP.NET and ADO.NET in Visual Basic .NET
> Dim oDr As SqlDataReader = oCmd.ExecuteReader()
> // ASP.NET and ADO.NET in C#
> SqlDataReader oDr = oCmd.ExecuteReader();
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> David,
> Thank you very much for your time in helping me on this, it's very
> much appreciated! The client application is written in VB.NET and
> uses Rocky Lohtkas CSLA component framework. The stored procedure
> that's called that generates this error is in the save method of one
> of my components, there's no data reader there, although I do use
> DataReaders to populate the objects with data. I'm still scepticle
> that this is a SQL Server (MS DTC) issue and not a client cursor
> problem because the ITransactionJoin interface is something MS DTC
> uses, right...? Also I have a powerbuilder application that has
> generated the same exact error.
> Dan
I'm not going to be able help much more. A firehose cursor as set up by
one data access library is really the same as the next since there's no
client management of the cursor. The error you are receiving has been
reported in SQL EM as well.
When you run the procedure it sounds like you are using .Net call that
may be generating a recordset/resultset or whatever it's called in
.net. If that's the case, try and make an ADO.net call that does not
generate a result set.
You should also have a SET NOCOUNT ON at the top of every procedure. And
you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
instead of @.@.identity.
Bu as I mentioned, I'm out of ideas onthis issue.
David Gugick
Imceda Software
www.imceda.com|||Thanks David... I'm using ExecuteNonQuery which doesn't generate a result
set. Anybody else have any ideas...?
"David Gugick" wrote:
> Alien2_51 wrote:
> I'm not going to be able help much more. A firehose cursor as set up by
> one data access library is really the same as the next since there's no
> client management of the cursor. The error you are receiving has been
> reported in SQL EM as well.
> When you run the procedure it sounds like you are using .Net call that
> may be generating a recordset/resultset or whatever it's called in
> ..net. If that's the case, try and make an ADO.net call that does not
> generate a result set.
> You should also have a SET NOCOUNT ON at the top of every procedure. And
> you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
> instead of @.@.identity.
> Bu as I mentioned, I'm out of ideas onthis issue.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Thanks David... I'm using ExecuteNonQuery which doesn't generate a
> result set. Anybody else have any ideas...?
>
Get the SET NOCOUNT ON in there and try again. Without it, there is a
phantom result set generated.
David Gugick
Imceda Software
www.imceda.com
