Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I have an ASP.net application with 1
- Thread starter alinad
- Start date
-
1
- #2
- Thread starter
- #3
Rhys666
Programmer
- May 20, 2003
- 1,106
- 0
- 0
Fairy snuff really. Anyway, there are two TimeOut settings for SQL Server connections, one in the connection string...
<CODE>
data source=ServerName;initial catalog=DBName;uid=UID;pwd=PWD;Connect Timeout=600
</CODE>
and one for the Sql Command...
<CODE>
// My connection is instantiated in a Connection class
SqlConnection cnSQLLogin = Connection.Connect();
// Instantiate SqlCommand
SqlCommand cmdSQLLoginId = new SqlCommand(strSqlCommand,cnSQLLogin);
// Set Timeout for SqlCommand
cmdSQLLoginId.CommandTimeout = 120;
</CODE>
Have you ensured both have long enough values to allow the sProc's to run?
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
<CODE>
data source=ServerName;initial catalog=DBName;uid=UID;pwd=PWD;Connect Timeout=600
</CODE>
and one for the Sql Command...
<CODE>
// My connection is instantiated in a Connection class
SqlConnection cnSQLLogin = Connection.Connect();
// Instantiate SqlCommand
SqlCommand cmdSQLLoginId = new SqlCommand(strSqlCommand,cnSQLLogin);
// Set Timeout for SqlCommand
cmdSQLLoginId.CommandTimeout = 120;
</CODE>
Have you ensured both have long enough values to allow the sProc's to run?
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
- Thread starter
- #5
- Thread starter
- #7
Rhys666
Programmer
- May 20, 2003
- 1,106
- 0
- 0
Not the machine.config file within .Net, the Internet Services Manager administrative tool in Windows 2000/XP that controls the Settings for Web Applications running on a Web Server.
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
- Thread starter
- #9
- Thread starter
- #11
- Thread starter
- #13
- Thread starter
- #15
Rhys666
Programmer
- May 20, 2003
- 1,106
- 0
- 0
Don't know if it's gonna help, but I think it might just. The below is taken straight from the Transact-SQL help files regarding Lock timeouts...
<QUOTE>
Customizing the Lock Time-out
When Microsoft® SQL Server™ 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. If this causes a deadlock, SQL Server terminates one of the participating transactions (with no time-out involved). If there is no deadlock, the transaction requesting the lock is blocked until the other transaction releases the lock. By default, there is no mandatory time-out period, and no way to test if a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).
Note The sp_who system stored procedure can be used to determine if a process is being blocked, and who is blocking it.
The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 "Lock request time-out period exceeded" is returned to the application.
However, any transaction containing the statement is not rolled back or canceled by SQL Server. Therefore, the application must have an error handler that can trap error message 1222. If an application does not trap the error, it can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction may depend on the statement that was never executed.
Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action for example, automatically resubmitting the statement that was blocked, or rolling back the entire transaction.
To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function, for example:
DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout
GO
See Also
@@LOCK_TIMEOUT
SET LOCK_TIMEOUT
sp_who
Handling Errors and Messages
</QUOTE>
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
<QUOTE>
Customizing the Lock Time-out
When Microsoft® SQL Server™ 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. If this causes a deadlock, SQL Server terminates one of the participating transactions (with no time-out involved). If there is no deadlock, the transaction requesting the lock is blocked until the other transaction releases the lock. By default, there is no mandatory time-out period, and no way to test if a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).
Note The sp_who system stored procedure can be used to determine if a process is being blocked, and who is blocking it.
The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 "Lock request time-out period exceeded" is returned to the application.
However, any transaction containing the statement is not rolled back or canceled by SQL Server. Therefore, the application must have an error handler that can trap error message 1222. If an application does not trap the error, it can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction may depend on the statement that was never executed.
Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action for example, automatically resubmitting the statement that was blocked, or rolling back the entire transaction.
To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function, for example:
DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout
GO
See Also
@@LOCK_TIMEOUT
SET LOCK_TIMEOUT
sp_who
Handling Errors and Messages
</QUOTE>
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
Rhys666
Programmer
- May 20, 2003
- 1,106
- 0
- 0
Also...
<QUOTE>
remote query timeout Option
Use the remote query timeout option to specify the number of seconds that must elapse when processing a remote operation before Microsoft® SQL Server™ assumes the command failed or took too much time to perform (times out). The default is 600, which allows a ten minute wait.
For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.
For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote "EXEC sp" before the remote stored procedure times out.
The setting takes effect immediately (without a server stop and restart).
To set a time limit for remote queries
Enterprise Manager
How to set a time limit for remote queries (Enterprise Manager)
To set a time limit for remote queries
Expand a server group.
Right-click a server, and then click Properties.
Click the Connections tab.
Under Remote server connections, in the Query time-out (sec, 0 = unlimited) box, type or select a value from 0 through 2147483647 to set the maximum number seconds that Microsoft® SQL Server™ will wait before timing out.
Note Remote server connections must be allowed before this value can be set.
Transact-SQL
SQL-DMO
See Also
RECONFIGURE
Rowset Properties and Behaviors
Setting Configuration Options
sp_configure
</QUOTE>
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
<QUOTE>
remote query timeout Option
Use the remote query timeout option to specify the number of seconds that must elapse when processing a remote operation before Microsoft® SQL Server™ assumes the command failed or took too much time to perform (times out). The default is 600, which allows a ten minute wait.
For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.
For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote "EXEC sp" before the remote stored procedure times out.
The setting takes effect immediately (without a server stop and restart).
To set a time limit for remote queries
Enterprise Manager
How to set a time limit for remote queries (Enterprise Manager)
To set a time limit for remote queries
Expand a server group.
Right-click a server, and then click Properties.
Click the Connections tab.
Under Remote server connections, in the Query time-out (sec, 0 = unlimited) box, type or select a value from 0 through 2147483647 to set the maximum number seconds that Microsoft® SQL Server™ will wait before timing out.
Note Remote server connections must be allowed before this value can be set.
Transact-SQL
SQL-DMO
See Also
RECONFIGURE
Rowset Properties and Behaviors
Setting Configuration Options
sp_configure
</QUOTE>
Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!
- Thread starter
- #19
Hello Rhys666! sorry for this late answer
i was trying and trying.
i found something to help me.
because it was not a sql timeout, i used another thread that was sending information every 10 seconds, during sp execution on another thread
it worked!
Thanks for your time and threads ideea!
i was trying and trying.
i found something to help me.
because it was not a sql timeout, i used another thread that was sending information every 10 seconds, during sp execution on another thread
it worked!
Thanks for your time and threads ideea!
- Status
Similar threads
- Locked
- Question