Timeout expired. The timeout period elapsed prior to completion of the operation

[Manoj K]

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions.
The table hierarchy contains 3 levels (TL1, TL2, and TL3):
Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all
the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to
determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do
a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the
first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.

[Cowboy \(Gregory A. Beamer\)]

Consider running all of the statements in a single stored procedure and let
SQL Server handle the transaction rather than roundtrip. You can also set
the CommandTimeout on the Command object(s), ConnectionTimeout on the
Connection object.

Even faster, consider using XML for your INSERTS, UPDATES and cascaded
DELETEs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Manoj K" <ma*************@yahoo.com> wrote in message
news:01****************************@phx.gbl...

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions.
The table hierarchy contains 3 levels (TL1, TL2, and TL3):
Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all
the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to
determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do
a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the
first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.

[José Joye]

I'm not sure I understand what you mean by "Even faster, consider using XML
for ..."

Could you clarify it?

Thanks,
José
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> a écrit
dans le message de news:u%****************@TK2MSFTNGP10.phx.gbl...

Consider running all of the statements in a single stored procedure and let SQL Server handle the transaction rather than roundtrip. You can also set
the CommandTimeout on the Command object(s), ConnectionTimeout on the
Connection object.

Even faster, consider using XML for your INSERTS, UPDATES and cascaded
DELETEs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Manoj K" <ma*************@yahoo.com> wrote in message
news:01****************************@phx.gbl...

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions.
The table hierarchy contains 3 levels (TL1, TL2, and TL3):
Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all
the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to
determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do
a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the
first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.

Gregory,

I think the transaction seems to be working fine because
it is doing the ROLLBACK. I still think it is the LOCK
that is causing the problem. Any ideas?

-----Original Message-----
Consider running all of the statements in a single stored procedure and letSQL Server handle the transaction rather than roundtrip. You can also setthe CommandTimeout on the Command object(s), ConnectionTimeout on theConnection object.

Even faster, consider using XML for your INSERTS, UPDATES and cascadedDELETEs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************* ********* ************Think Outside the Box!
************************************************* ********* ************"Manoj K" <ma*************@yahoo.com> wrote in message
news:01****************************@phx.gbl...

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions. The table hierarchy contains 3 levels (TL1, TL2, and TL3): Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs. For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after the SELECT is executed.

.

[Glen Jones MCSD]

Hi,

"SELECT (on TL2) works and then I'm able to do an INSERT
into TL2."

I think what is happening is that once the insert occurs, your future
selects are locked.

Maybe you could change your transaction level, from Table to Rows.

Hope this helps.
--
Glen Jones MCSD

"Manoj K" <ma*************@yahoo.com> wrote in message
news:01****************************@phx.gbl...

Environment:-
Framework 1.1
DB: SQL Server 2000 (SQL Provider)
OS: Windows 2000
Language: VB.NET

I'm trying to do mass inserts/updates. Using Transactions.
The table hierarchy contains 3 levels (TL1, TL2, and TL3):
Here is what I'm trying to accomplish:

1) Start Begin Transaction (in desperation, I've tried all
the isolation levels- I was hoping ReadUncommitted would
work!)
2) Do an Insert/Update in table TL1 (this works).
3) Grab the ID from step 2 and do a SELECT on table TL2 to
determine if I need to an UPDATE or INSERT.

Since I'd be inserting 3 rows into table TL2 (for a
particular parent ID with different types), the first
SELECT (on TL2) works and then I'm able to do an INSERT
into TL2. The transaction is still alive. Now, I try to do
a SELECT on table TL2 to see if a record exists for this
type and this is when I get the exception "Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

My guess is that I have a LOCK on the table TL2 (from the
first SELECT), which seems to be causing the problem.

Note:
The INSERTs and UPDATEs are done by executing stored procs.
For the SELECTS, I'm using a different connection (from
the one which the transaction is using) and close it after
the SELECT is executed.