Link to home
Start Free TrialLog in
Avatar of Thread7
Thread7

asked on

SQL Server Timeout when running long stored procedure

I keep trying to run a stored procedure and after 30 seconds I get the error.
SQL Execution Error
Executed SQL statement: EXEC SearchAllTables 'Hello World'
Error Source: .Net SqlClient Data Provider
Error Message: Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not repsonding.

I've found other EE articles that say to go into SQL Management Studio  under:
TOOLS | OPTIONS | DESIGNERS
and change your timeout value to a higher number.  I've done this but it seems to ignore that setting.  I've started and stopped the SQL Server Service but it still doesn't use that setting.
I am just running the stored procedure in a table Window in SQL Management 2005 Studio.  Maybe I should be doing it somewhere else, but I don't seem to have the Query Designer available that used to be in older versions of SQL Server.
The code I run in the table window is just:
EXEC SearchAllTables 'Hello World'

I know the stored procedure works because it does in a smaller database.  But the current database is much larger so it takes to long to run this procedure (which is basically doing a text search of every nvarchar field in the entire db).

Image2.jpg
Avatar of jerryscole
jerryscole

Good question. This is actually set inside of your .NET code as shown in the snippet below:

                Dim cmd As New sqlCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "SFADTAP.H_NXTNUM"
                cmd.CommandTimeout = 120

The command timeout is set in seconds. You can also set a connection timeout as well if you have a slow process on that end.

Avatar of Thread7

ASKER

Is there a way to do it right within SSMS?  I just had this stored procedure so I could detect SQL Injections and wasn't looking to set up a .NET page for it.
Avatar of Guy Hengel [angelIII / a3]
check out your sql command oject in the SSIS, it should also have a CommandTimeout property.
set that to 0, for example.
Avatar of Thread7

ASKER

I tried SSIS.  That takes a learning curve to get working.  There is no way to set CommandTimeout in SSMS?
menu: tools->options
 query execution -> execution timeout. should be 0 for infinite timeout.
Avatar of Thread7

ASKER

Hmmm, it is set to 0 but I still have the problem.
ASKER CERTIFIED SOLUTION
Avatar of Thread7
Thread7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial