Hi,
I'm new to the forum. Hope I'm starting this in the right place.

I have the infamous timeout error accessing a sql server 2000 d
through asp pages on iis. I read the other post concerning this issu
and added the recommendations to my code. Problem is, no resolutio
for me.

I have a dynamically generated update statement which has a rathe
large IN clause making comparisions on an indexed and keyed intege
field. Here's the code below:

If Request("Submit").Count > 0 Then
Server.ScriptTimeout = 600
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 600
cn.open CONN_STRING

Set editCmd = Server.CreateObject("ADODB.Command")
editCmd.ActiveConnection = cn
editCmd.commandtimeout = 600

'This part dynamically generated translates to....
editCmd.CommandText = "UPDATE Inspections SET Mailgrou
= '10774, 10773, 10771, 10766, 10764, 10763, 10767, 10770, 10769
10772, 10768, 10765, 10762, 10759, 10758, 10757, 10761, 10760, 10750
10748, 10755, 10751, 10753, 10752, 10749, 10754, 10756, 10745, 10741
10743', LetterNo = 'asdf', InspComment = 'asdf', Reviewed = 1 WHER
Record_ID in (10774, 10773, 10771, 10766, 10764, 10763, 10767, 10770
10769, 10772, 10768, 10765, 10762, 10759, 10758, 10757, 10761, 10760
10750, 10748, 10755, 10751, 10753, 10752, 10749, 10754, 10756, 10745
10741, 10743)"
editCmd.Execute
End If

The strange thing is that if less values are in the IN clause, th
statement executes quickly and successfully.

I also thought to put this statement in a loop for each of the I
clause values to see if that helped. Nope. It executes about 1
iterances then timesout.

Any suggestions greatly appreciated.

S


-
swransk
-----------------------------------------------------------------------
Posted via http://www.webservertalk.co
-----------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message172903.htm

> cn.open CONN_STRING

What is CONN_STRING?
[color=blue]
> Set editCmd = Server.CreateObject("ADODB.Command")[/color]

Why are you using a command object?
[color=blue]
> 'This part dynamically generated translates to....
> editCmd.CommandText = "UPDATE Inspections SET Mailgroup[/color]

Why aren't you using a stored procedure?
[color=blue]
> = '10774, 10773, 10771, 10766, 10764, 10763, 10767, 10770, 10769,
> 10772, 10768, 10765, 10762, 10759, 10758, 10757, 10761, 10760, 10750,
> 10748, 10755, 10751, 10753, 10752, 10749, 10754, 10756, 10745, 10741,
> 10743'[/color]

Ugh, are you really storing this entire list for all the elements in the
list? This a very non-normalized, non-relational way of storing this data.
If I were the database, I would probably time out on you too.
[color=blue]
> I also thought to put this statement in a loop for each of the IN
> clause values to see if that helped. Nope. It executes about 14
> iterances then timesout.[/color]

Does the table have any indexes? Have you tried executing each of these
statements individually, and see if there is one specific statement (e.g.
one value in the WHERE clause) that is causing the timeout?

This sounds pretty unconvincing that there is a bug, but with a slightly
better approach to the design you can eliminate any ambiguity...