get @@trancount for all users/ connections URGENT

Hi all,

Is there any way to get the @.@.trancount for a connection from outside the connection?

The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.

So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)

So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.

Thanks in advance,

Gert-Jan

In 2005, you can use this query to see that information.

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

|||The DB is on 2K, do you have this script for 2K?|||

Yeah, it is in sysprocesses. The column is open_tran

select spid, open_tran

from master..sysprocesses

|||

Louis,

Thanks for the very quick response, i'll get back to you if you saved the day.

Regards Gert-Jan

|||

Hi Gert-Jan van der Kamp,

> So another theory is that a certain sproc began a transaction, but never finished because of an error.

> (We had some strange timeouts as well, so this is quite plausible.)

I think you are looking for open transactions. It that case, the "select" statement provided by Louis (hope we can have your new book about DMVs and DMFs soon), will not help you much because that session will not have a match in sys.dm_exec_requests. You can find an example in BOL, under the topic about "sys.dm_exec_sessions".

This is from BOL.

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

Forgot to mention that in SS 2000 your choice is to use "dbcc opentran".AMB

sql

Get 'DB Error 10083' with SQL Server 2000

Setup: SQL Server 2000 sp3 on a Windows 2000 machine, transactions running through an ODBC connection running SQLSRV32.dll version 2000.86.1830.00

Transactions: Being generated via a Citrix server (MetaFrame Presentation Server, Enterprise Edition 4.0(Build 2198), Service Pack 2005.04.... This sits on top of an install of Windows 2003 terminal server installaion.

Error: Appears to only be happening on a ADD(Insert) ....

Any thoughts on this would be a big help.

Dave

Is there any more to the error message? I can't find reference to error 10083|||

Here is the exact message I received...

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

Insert Into Test (testid, testdesc,sequence,createdt,creatorid,lastmodifieddt,modifierid,testinstances,allowinstances,activityid,worklisttable,qutoqc) Values ('999','Test Test',0,'10-31-2006 9:22:30.253','SysAdmin','10-31-2006 9:22:30.253','SysAdmin',1,'N',0,'N','N')

The above highlighted text was the exact message...

Thanks-David

|||

This is not a SQL Server error message. You need to check your data access layer. Here are few guesses on what the problem is:

1. You are issueing insert statement but expecting a result set to be returned. SQL Server will return some result if SET NOCOUNT OFF (in which it will be number of rows affected) or if the insert trigger returns results. In other cases, if SET NOCOUNT ON is set you will not get any results for the insert statement

2. You are doing the wrong thing from the data access layer and should use some direct execute mechanism that doesn't look for results from the command.

|||

Thanks for giving me some direction on this issue! I will do some testing to how I can resolve this issue in a good way.

David

|||

I have same this error message in some computer

I use MSDE with 2 clients. found this problem 1 client.

The error message show with this query

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

This is query from another computer:

Begin Transaction

go

declare @.P1 int

set @.P1=180150003

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sidecode ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150003, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150003

go

Select prefix From SMId where name ='aum/AUM/emside'

go

Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0

go

Begin Transaction

go

INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'aum/AUM/emside' , 1000 , 1 )

go

Commit Transaction

go

declare @.P1 int

set @.P1=180150004

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(sidei', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150004, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150004

go

declare @.P1 int

set @.P1=180150005

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sideid FR', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150005, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150005

go

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

go

Select InsertFlag From SMaudit_option Where brchid =1

go

SELECT GETDATE( ) As cf_server_time

go

Select prefix From SMId where name ='aum/AUM/smAudit'

go

declare @.P1 int

set @.P1=180150006

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(audit', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150006, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150006

go

declare @.P1 int

set @.P1=180150007

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT audit_id ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150007, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150007

go

INSERT INTO SMAudit ( audit_id, audit_system, audit_screen, audit_datetime, audit_username, audit_action, audit_docuno, audit_columnid, brchid, audit_computername, audit_refid ) VALUES ( 139002, 1, 6, '5-3-2007 18:36:24.453', 'prosoft', 'I', '001', 1000, 1, 'AUM', 139002 )

go

Commit Transaction

go

|||

This's profiler from another computer with same form.

Begin Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sidecode FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sidecode , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE (sidecode like '%AAA%' AND (LTRIM(sidecode) = 'AAA' OR RTRIM(sidecode) = 'AAA')) order by emside.SideID
go
set rowcount 0
go
Select prefix From SMId where name ='accdomain\sql2000/MORAGOTD/emside'
go
Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0
go
Begin Transaction
go
INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'accdomain\sql2000/MORAGOTD/emside' , 2000 , 1 )
go
Commit Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sideid FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sideid , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE sideid = 2000 order by emside.SideID
go
set rowcount 0
go
INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 2000, 'AAA', 'AAA', '' )
go
Rollback Transaction
go

|||

Did you ever find a resolution to this? We are getting this error in an application we are upgrading. The upgraded version uses SQL Server 2000 and the old version uses SQL Server 7. We are only seeing the error on some of our machines but not all. On two different machines where we have SQL Server 2000 client utilities installed we do not get the error. On another machine where I have SQL Server 7 client utilities or no client utilities exist we do get the error. I think there is a driver found in the 2000 client utilities that resolves the issue, but can't quite put my finger on which one it is. The application vendor seems to think it is our machine configuration.

Thanks,

I found a solution: Check your version of the ntwdblib.dll driver. Version 2000.80.194.0 fixed the issue.

Generating DTS flat file connection

I'm trying to generate a DTS Package with VB.Net using the Microsoft DTSPackage Object Library and
and the Microsoft DTSDataPump Scripting Object Library
I have to load csv files into SQL tables.
I could generate both a SQL connection and a FlatFile connection and the transformationtask.

When I look at the transformationtask and click on the transformation tab I get this error

"Incomple file format information"

The problem is I don't find where I could set the FlatFile connection properties like "Text Qualifier" and
"row delimiter"

I tried this but it still shows CRLF as row delimiter when I look at the generated DTS Package

Dim oConnection As DTS.Connection2
Dim package As DTS.Package2
Dim filename As String
filename = "myfilename.csv"

oConnection = package.Connections.New("DTSFlatFile")
oConnection.Name = filename
oConnection.ConnectionProperties.Item("Row Delimiter").Value = vbTabWhy not just bcp the file out using a stored porcedure?