Strange Timeout expired error

[SteveMac32]

My VB6 program calls a stored procedure (SQL server 2000) that runs the code outlined below

CREATE PROCEDURE [spExportResetDNP]

@TableName as varchar(50)

AS

Declare @SQL as varchar(250)

Set @SQL = 'UPDATE ' + @TableName + ' SET ExpProcess = Null Where ExpProcess = 1'
Exec (@SQL)
GO


there are only about 750000 rows.

Now this worked fine for quite a while until the beginning of December when it started getting the Error -2147217871 (Timeout expired).

Here's the kicker it only fails on the timeout if there are no records matching the ExpProcess.....
If i manually amend one record to have a ExpProcess = 1 then it runs fine..



The user has full permissions
The ConnectionTimeout = 0

I have gone as far as i can with this and have no solutions can any of you help.

Thanks loads

Steve Mac

 

[genomon]

What messages do you get when you manually run the proc in query analyzer with no ExpProcess = 1?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[ArtieChoke]

The connection timeout only affects connecting, not running the query.

There is a command timeout property that affects how long ADO will wait (if you are using the command object). It won't affect the connection object.

You're probably running into a server timeout. ADO seems to do queries differently than QA. I've had queries run in seconds in QA and take a long time or time out in ADO.

Is there an index on the ExpProcess field? If not, you are most likely doing a sequential search. Run a query analysis in QA to see what kind of look up is going on. Maybe add an index so the query will run faster.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[genomon]

ODBC/timeout errors will also trigger if the procedure fails.
Artie is most likely correct, but see what happens in QA just for giggles, too.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[SteveMac32]

Hi
Thanks for the info I have tried them all in QA it runs fine I think you may be correct in the server timeout as it actually runs on two tables on one it works on the other it does not. Both tables are the same except for the data held in them.

The full error message is
Run-time error ‘-2147217871 )80040e31)’:
Timeout expired


I have actually cured it but not sure how…
I exported all the data into another table so I could safely experiment on it, removing records and such, so I ran the program and it worked fine….. I am very confused now and slightly concerned.

Any way it works for some reason and I will have to make up a good one to tell my boss.

Thanks for all your help.

 

[genomon]

Did you check for triggers & constraints on the old table?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[SteveMac32]

Yea sure did, there arnt any only a few default values.

 

[chamilz]

instead of ConnectionTimeOut = 0, try using CommandTimeOut=0

cmd.CommandTimeOut = 0
cmd.ExecuteNonQuery()