469,631 Members | 980 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

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

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.

Nov 15 '05 #1
4 12415
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.

Nov 15 '05 #2
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.


Nov 15 '05 #3
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.

.

Nov 15 '05 #4
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.

Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by J | last post: by
5 posts views Thread by Jason | last post: by
2 posts views Thread by Nils Magnus Englund | last post: by
3 posts views Thread by Nils Magnus Englund | last post: by
7 posts views Thread by dadocsis | last post: by
1 post views Thread by Scorpion657 | last post: by
2 posts views Thread by Andrew Cooper | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.