Timeout error -2146232060 SQL

[gollnick]

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

[GaryMazzone]

Have you tried settinc the connection timeout value? Can you try and batch the delete in an SP?

[techgnome]

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

[gollnick]

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

[GaryMazzone]

On your command object (if doing this from a font end). If you are doing this from SSMS let us know.

[gollnick]

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