SQL 2000 and SqlTransaction Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is my saga . .

I have a VB.Net project that works on my development system when hitting my own loca
SQL Server 2000, but has problems when I try to hit the production SQL Server (stil
running the code on my development system.

I create an ADO.NET SqlTransaction and then I intermix writing to a single database
both within the transaction and outside of the transaction. The connect string is the
same for both the transaction's connection and all the non-transaction connections

When hitting the local SQL Server it all works fine, but if I switch to the productio
server, I can write to the database just fine until I create the SqlTransaction a
which point, my next attempt to write to the database from a fresh connection outsid
the transaction fails with a timeout. I did comment out the call to BeginTransactio
and then the next write on an fresh connection works just fine

Details

My development system runs Win 2003 Server and the following version of SQL 2000

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: )

and the connectstring = "server=monkey;database=UVC_PCST;uid=sa;pwd=;

The production server runs the following version of SQL 2000

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )

and the connectstring = "server=uvwProd;database=UVC_PCST;Integrated Security=True

Any thoughts ?

Thanks

LES
 
Hi lester,

You are probably deadlocking yourself.
Why don't you do everything under same transaction?
 
The only thing I do outside the trasaction is write to an activity log that I don't want rolled bac
if things go wrong. Besides, it works on one system and not the other. Also, after I create th
transaction via BeginTransaction, the first thing I do outside the transcation on a fresh connectio
is log that I just called BeginTrans. So at this point there should be no locks held yet

LE

----- Miha Markic [MVP C#] wrote: ----

Hi lester

You are probably deadlocking yourself
Why don't you do everything under same transaction
 
Im not sure why it is working on one DB and not the other, but since you
do not care about the initial transaction, why not start the write to
the activity log asynchronously? That way the write to the activtiy log
will be done using a different thread, making the process faster for
the initial transaction.
The only thing I do outside the trasaction is write to an activity log that I don't want rolled back
if things go wrong. Besides, it works on one system and not the other. Also, after I create the
transaction via BeginTransaction, the first thing I do outside the transcation on a fresh connection
is log that I just called BeginTrans. So at this point there should be no locks held yet.

LES

----- Miha Markic [MVP C#] wrote: -----

Hi lester,

You are probably deadlocking yourself.
Why don't you do everything under same transaction?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

lester said:
my own local
SQL Server 2000, but has problems when I try to hit the production SQL Server (still
running the code on my development system.) database
both within the transaction and outside of the transaction. The connect string is the
same for both the transaction's connection and all the non-transaction connections. the production
server, I can write to the database just fine until I create the SqlTransaction at
which point, my next attempt to write to the database from a fresh connection outside
the transaction fails with a timeout. I did comment out the call to BeginTransaction
and then the next write on an fresh connection works just fine.
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: )
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
 
Back
Top