SQL Server 2005 causes problems with second connection

[Guest]

I have a scenario where I am making an update using multiple calls to the
database and all the calls are within the same transaction. However, before
I commit I have need to query some additional data. So I created a NEW
connection and performed the query. Problem is, when the database is SQL
Server 2005 this second query times out because of the other connection with
the transaction open.
This does not happen when the database is SQL Server 2000.

What is the problem? I have read that you can't have multiple readers open
on the same connection, etc. But if you have two separate connections what
I'm trying to do should be possible. Can anyone explain the behavior I'm
seeing?

Thanks!

 

[Miha Markic [MVP C#]]

It depends on the type of transaction. If first transaction is locking rows
then the other can't read them.
Why don't you read within first transaction?

 

[Guest]

That is how I had to fix it. The main reason I wasn't doing it in a
transaction is the object model that wrapped the calls to retrieve the data
didn't take in a transaction as a parameter because they were retrieval
methods vs. methods that modify data in the database.

Thanks

It depends on the type of transaction. If first transaction is locking rows
then the other can't read them.
Why don't you read within first transaction?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I have a scenario where I am making an update using multiple calls to the
database and all the calls are within the same transaction. However,
before
I commit I have need to query some additional data. So I created a NEW
connection and performed the query. Problem is, when the database is SQL
Server 2005 this second query times out because of the other connection
with
the transaction open.
This does not happen when the database is SQL Server 2000.

What is the problem? I have read that you can't have multiple readers
open
on the same connection, etc. But if you have two separate connections
what
I'm trying to do should be possible. Can anyone explain the behavior I'm
seeing?

Thanks!