timeout problem, again.

[taval]

I have an asp script that allows you to delete a customer form a database including all his files. While trying to delete one of the customers from an asp script I get the following error message:

------------------------------------------------------------
Database Errors Occured
Delete From FileDescription WHERE CustId=49

Error #-2147217871

Error desc. -> [Microsoft][ODBC SQL Server Driver]Timeout expired
-----------------------------------------------------------

That particular customer has a lot of files to delete. I have tried it on customers with fewer files and I don't get this error message. I was womdering what I could do so that thw query does not timeout? i.e. so that it completes the deletion process.

Grateful for any help, thanks.
Taha

 

[Guest_imported]

Taval,
here's the article from SQL Server Books Online :

How to set a time limit for remote queries (Enterprise Manager)
To set a time limit for remote queries

1.Expand a server group.
2.Right-click a server; then click Properties.
3.Click the Connections tab.
4.Under Remote server connections in the Query time-out (sec) box, select a value from 0 through 2,147,483,647 to set the maximum number of time, in seconds, that Microsoft® SQL Server™ will wait for a remote query to be processed before timing out.
[red]5.The default value of 0 prevents SQL Server from timing out.[/red]

There's one more thing - permissions; even when I tried to perform the step 2 on a server where I wasn't a member of sysadmin role, I was getting messages that access to this feature is denied, so you need to have those permissions

 

[taval]

As above , I've set the timeout to 0 (the default) on SQL server so in theory the SQL Server Driver should not timeout, but it does? Anyone know why?

 

[Guest_imported]

Here's another article from SQL Server Books Online. It somewhats contradicts with the previous one that I've posted, but take a look:
-----------------------------------------------
ODBC error text
[Microsoft][ODBC SQL Server Driver]Timeout expired.

Explanation
The timeout can occur when you're updating the database with any Transact-SQL changes.

Action
Try again later to save the diagram or selected tables.
Save a change script and apply it to the database at a later time.
Increase the SQL Query Time-out value and try to save the diagram or selected tables again.
To increase the SQL Query Time-out value

From the Tools menu, choose Options.
In the left pane, click Data Tools, and then click Data View.
Type a new value in the SQL query time-out box.
------------------------------------------------------

 

[taval]

Thanks guestg for giving me some tips, but the query still manages to timeout.I've tried practical everything to stop the query from timing out, I've used stored procedurs as well to see if that helps, but it doesn't. I need to be able to delete customers without showing error messages, otherwise my prograam isn't/wouldn't be very good. I've tried using both stored procedures and normal querys. Heres what they look like:

asp
------------------------------------------------------
SQLstmt = "Delete From ImageData WHERE CustId=" & frm_CustID
Set RS = Conn.Execute(SQLStmt)

SQLstmt = "Delete From FileDescription WHERE CustId=" & frm_CustID
Set RS = Conn.Execute(SQLStmt)

SQLstmt = "Delete From Customer WHERE CustId=" & frm_CustID
Set RS = Conn.Execute(SQLStmt)
-------------------------------------------------------

stored procedure
-------------------------------------------------------
CREATE PROCEDURE spu_DeleteCustomer @iCustID integer

AS

BEGIN TRAN Del
DELETE FROM ImageData WHERE CustID=@iCustID
DELETE FROM FileDescription WHERE CustID=@iCustID
DELETE FROM Customer WHERE CustID=@iCustID
COMMIT TRAN Del

return -1
-----------------------------------------------------------

can anyone help me out, plz?

Taha

 

[Mang007]

Hy!!!

I had the same problem... I solve it using the CommandTimeout of the connection object. If you set it to 0 then it's infinite!

Hope it will help you!!

Mang