starting long running stored procedures

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

I have a windows service that starts a stored procedure that could take many
hours to run, and the code below starts it, however the stored procedure
does not finish. When I run it through query analyzer it runs all the way
through ok so there's nothing wrong with the sp. Any reason why it would
stop prematurely? I'm assuming it is timing out or something.

SqlHelper.ExecuteScalar(appvars.strConn, CommandType.StoredProcedure,
"spMyStoredProc")
 
Max,

Does your connection string include a value for Connect Timeout? If it
does, you could try increasing the time there, or add Connect Timeout to
the string, as in the following example:

myConnection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30";

The value is in seconds, so you could probably do a conversion (perhaps
with some additional time as a buffer) based on the running time in
Query Analyzer.

- Mike
 
I did some further tests and I am getting the timeout error. However I'm not
certain if connection timeout really does what it implies. I'm hoping
there's something I can change within the connection string though to keep
the connection alive while the stored procedure operates?

-Max
 
There's a property of the SqlCommand object (which the SqlHelper class
creates for you) called "CommandTimeout".

You can set this to 0 to get an infinite timeout value (which is usually not
recommended).

But if you're going to use the SqlHelper class, you'll have to modify that
property.
Check out the method with the signature:...

public static object ExecuteScalar(SqlTransaction transaction, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)

...and add a line after the PrepareCommand() call like this:

cmd.CommandTimeout = 0;

That should do it. (Note: it's _not_ recommended though, because any other
code using the SqlHelper will be affected. You probably want to write your
own Stored Proc code to call that SP in this case.)


-Andrew
 
I set the commandtimeout very high and it works thanks!

But what is the scope or life of commandtimeout? I thought it only affected
the connection you send the command through? Does it affect other open
connections or future connections? I mean, do I need to reset it after I'm
done with that stored proc?

Thanks,

Max
 
Back
Top