Timeout error -2146232060 SQL
I am having a timeout issue. My application call a T-SQL stored procedure to delete several hundred thousand records after a process has been completed.
650,000 not unusual.
The code for the T-SQL is a no-brainer
DELETE FROM CardDetail
WHERE BCDGAME=@GameNbr and BCDGameDate = @Date_Id and BCDGameCntrl = @Seq_Id.
I have a try/catch to trap this and get this error
{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}
None of the records have been deleted. How do I get around this??
Thanks
gollnick
Re: Timeout error -2146232060 SQL
Have you tried settinc the connection timeout value? Can you try and batch the delete in an SP?
Re: Timeout error -2146232060 SQL
By default the command timeout is only 30 seconds... so if it is going to take longer than that, you need to change the CommandTimeout value to something larger that will allow the command to finish executing.
-tg
Re: Timeout error -2146232060 SQL
It is in a stored procedure already. I am new to SQL.management. How do I change the CommandTimeout value for this stored procedure? Or is it for the entire database and all the talbles within it?
Thanks
gollnick
Re: Timeout error -2146232060 SQL
On your command object (if doing this from a font end). If you are doing this from SSMS let us know.
Re: Timeout error -2146232060 SQL
Thanks all.... went to my code front end and changed the timeout value on the command string into the sp... duhhhhhhh....
Again .. thanks for the direction
gollnick