Help! I've been hacked! I'm running SQL 7 Desktop (MSDE) and started getting
these messages at startup that servudaemon.exe was going to be shut down.
Then, at one point, I keep getting prompted by Norton Firewall that
serve-u.exe is trying to access the Internet. It suggested I deny it, which
I did; but I kept getting prompted every minute or so, so finally I told
Norton Firewall to always deny it.
Then, some minutes later, I close an Access 2000 mdb file I had opened which
uses SQL Server as a back end (through linked tables), and I was told that
connection to SQL Server had been lost. I tried reconnecting, but couldn't.
So I went into Enterprise Manager, and was told: "A connection could not be
established to D23 - Login failed for user 'sa'.."
So I went into Registration Properties, and saw that there was a password
there. I previously didn't have a password for 'sa' (which I know is stupid,
and now see the result of it). But there's a password now, and I can't
change it. So whatever hacked into my system set my 'sa' password.
Just to be sure that my settings in Norton Firewall weren't affecting
anything, I disconnected from the Internet and then disabled Norton Personal
Firewall. The results were the same.
So is there any way to restore my system?
Thanks for any assistance.
Neilserve-u.exe is possible ServeU, which is a popular FTP program. It seems
that you may have been hacked, and someone has configured an FTP site on
your machine (eg for serving mp3s, or warez or similar)
If you are a local administrator on the SQL Server machine, you should still
be able to login to SQL Server (using your Windows admin account, not the
"sa" account). You could reset the "sa" password then.
That said, you need to work out how these people got into your machine in
the first place. Additionally, since you don't know what other "back doors"
they have installed which might let them back in again, I'd say your best
bet is to backup your data, and then setup the machine from scratch.
Cheers
Ken
"Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
news:vfS4c.37319$aT1.26124@.newsread1.news.pas.earthlink.net...
: Help! I've been hacked! I'm running SQL 7 Desktop (MSDE) and started
getting
: these messages at startup that servudaemon.exe was going to be shut down.
: Then, at one point, I keep getting prompted by Norton Firewall that
: serve-u.exe is trying to access the Internet. It suggested I deny it,
which
: I did; but I kept getting prompted every minute or so, so finally I told
: Norton Firewall to always deny it.
:
: Then, some minutes later, I close an Access 2000 mdb file I had opened
which
: uses SQL Server as a back end (through linked tables), and I was told that
: connection to SQL Server had been lost. I tried reconnecting, but
couldn't.
: So I went into Enterprise Manager, and was told: "A connection could not
be
: established to D23 - Login failed for user 'sa'.."
:
: So I went into Registration Properties, and saw that there was a password
: there. I previously didn't have a password for 'sa' (which I know is
stupid,
: and now see the result of it). But there's a password now, and I can't
: change it. So whatever hacked into my system set my 'sa' password.
:
: Just to be sure that my settings in Norton Firewall weren't affecting
: anything, I disconnected from the Internet and then disabled Norton
Personal
: Firewall. The results were the same.
:
: So is there any way to restore my system?
:
: Thanks for any assistance.
:
: Neil
:
:|||sp3A is designed to stop access to the IO Port that exposes MSDE (and SQL
Server) to the web.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ken Schaefer" <kenREMOVE@.THISadOpenStatic.com> wrote in message
news:%23Wam0XbCEHA.3132@.TK2MSFTNGP11.phx.gbl...
> serve-u.exe is possible ServeU, which is a popular FTP program. It seems
> that you may have been hacked, and someone has configured an FTP site on
> your machine (eg for serving mp3s, or warez or similar)
> If you are a local administrator on the SQL Server machine, you should
still
> be able to login to SQL Server (using your Windows admin account, not the
> "sa" account). You could reset the "sa" password then.
> That said, you need to work out how these people got into your machine in
> the first place. Additionally, since you don't know what other "back
doors"
> they have installed which might let them back in again, I'd say your best
> bet is to backup your data, and then setup the machine from scratch.
> Cheers
> Ken
> "Neil Ginsberg" <nrg@.nrgconsult.com> wrote in message
> news:vfS4c.37319$aT1.26124@.newsread1.news.pas.earthlink.net...
> : Help! I've been hacked! I'm running SQL 7 Desktop (MSDE) and started
> getting
> : these messages at startup that servudaemon.exe was going to be shut
down.
> : Then, at one point, I keep getting prompted by Norton Firewall that
> : serve-u.exe is trying to access the Internet. It suggested I deny it,
> which
> : I did; but I kept getting prompted every minute or so, so finally I told
> : Norton Firewall to always deny it.
> :
> : Then, some minutes later, I close an Access 2000 mdb file I had opened
> which
> : uses SQL Server as a back end (through linked tables), and I was told
that
> : connection to SQL Server had been lost. I tried reconnecting, but
> couldn't.
> : So I went into Enterprise Manager, and was told: "A connection could not
> be
> : established to D23 - Login failed for user 'sa'.."
> :
> : So I went into Registration Properties, and saw that there was a
password
> : there. I previously didn't have a password for 'sa' (which I know is
> stupid,
> : and now see the result of it). But there's a password now, and I can't
> : change it. So whatever hacked into my system set my 'sa' password.
> :
> : Just to be sure that my settings in Norton Firewall weren't affecting
> : anything, I disconnected from the Internet and then disabled Norton
> Personal
> : Firewall. The results were the same.
> :
> : So is there any way to restore my system?
> :
> : Thanks for any assistance.
> :
> : Neil
> :
> :
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:13508 microsoft.p
ublic.sqlserver.security:19785 microsoft.public.sqlserver.connect:41876
I'm running SQL 7. Do you know if there's a similar sp for that?
Neil
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:%23RoIB3fCEHA.1544@.TK2MSFTNGP09.phx.gbl...
> sp3A is designed to stop access to the IO Port that exposes MSDE (and SQL
> Server) to the web.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Ken Schaefer" <kenREMOVE@.THISadOpenStatic.com> wrote in message
> news:%23Wam0XbCEHA.3132@.TK2MSFTNGP11.phx.gbl...
> still
the
in
> doors"
best
> down.
told
> that
not
> password
>
Showing posts with label messages. Show all posts
Showing posts with label messages. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
'Cannot start transaction while in firehose mode'
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
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
Thursday, February 16, 2012
Cannot see SQL Server in my pc
Hi - I just installed VS 2005, SQL Server 2005 Developer Edition. The install went fine with out any error messages.
all I can see is the configuration tools choice under Microsoft SQL Server 2005, and no way to access the SQL Server from my pc. I have a Windows XP Prof.
But I am able to access my server using VS 2005. But i am not used to creating the database thru this and more used to using the Enterprise manager to drive this.
Any help is much appreciated!
Thanks.
It sounds like you didn't ask to install the workstation components. By default, they're not selected when you go through the GUI install.
Go to Add/Remove Programs and select "change" for Microsoft SQL Server 2005. You'll see at a high level what's installed, and from there you can add new components if needed.
Paul
Subscribe to:
Posts (Atom)