I have an ASP.net application with 1

[alinad]

I have an ASP.net application with SQL Server 200 database.
I have a stored procedure that takes loger that 1 hour
but after 1 hour my connection (or something else timeouts)
I gae the standard message cannot find server or DNS error
Any ideeas?

 

[Rhys666]

Why are you running a 1 hour sProc from a web page??? What is the user going to do for the hour or so it's running? Shouldn't this sProc ber scheduled...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[alinad]

All SP for reporst last beteew 30 and 200 minutes
and beleive mt it was not my choise to do them on web pages
Any way is an intranet application

 

[Rhys666]

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!

 

[alinad]

Yes
i also modified iis timeouts
can it be a timeout generated by a network setting?

 

[Rhys666]

Not sure. Making the assumption you're running IIS, have you looked at IIS settings on the server?
There is a Connection Timeout setting here also...


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[alinad]

Yes
i also modified machine.config

 

[Rhys666]

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!

 

[alinad]

I've modified timeouts in the Internet Services Manager administrative tool in Windows 2000 also
i think i've modified evrythig. i still got the problem and i don't know where to look anymore

 

[Rhys666]

Does the proc run OK in Query Analyser?
(I know, I know, too many questions and not enough answers, apologies to you I'm just trying to step thru' it)


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[alinad]

yes it works
i even try it on a vb client and it worked

 

[Rhys666]

What about the session state 'element' of the Web.config file...


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[alinad]

i put mode=inproc and timeout = 300

 

[Rhys666]

Gotta be honest, I'm a bit stumped but am curious as to the cause, (and solution). I'll have to have a think as I'm not so hot on networking protocols etc.

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[alinad]

Thanks. you losed quite a time with my problem
i let you know if the solution if i'll ever find it

 

[Rhys666]

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 &quot;Lock request time-out period exceeded&quot; 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]

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 &quot;EXEC sp&quot; 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!

 

[BiniamTek]

I know this is the most obvious place to look, but just in case you forgot: Is SQL Server running?

 

[alinad]

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!