| Author |
Topic |
|
vemulavijay
Starting Member
32 Posts |
 Posted - 2004-12-06 : 18:52:30
|
| I am executing an stored proceedure which takes minimum half an hour to execute through Query Analyer, But the same if i Execute through my .NET windows Application it is giving a time out message as follows,Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Any help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-06 : 18:54:51
|
| Well, the default timeout value is 30 seconds. You can add the timeout option in your connection string. You'll obviously need to add a very large value. We don't put ours any higher than one minute though.What is the stored procedure doing that it takes 30 minutes to execute? Cursors and dynamic SQL?Tara |
 |
|
|
vemulavijay
Starting Member
32 Posts |
Posted - 2004-12-06 : 18:59:24
|
| It basically Excutes few other Proceedures which are working on a huge data. So for for 30 mins what could be our time out time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-06 : 19:00:23
|
| It just depends what the maximum execution time of your stored procedure is.Tara |
|
|
|
vemulavijay
Starting Member
32 Posts |
Posted - 2004-12-06 : 19:16:42
|
| Tara, Tahnks.I have given timeout=1800. But it is shooting a time out message in less than 1 minute. Am I missing something.Praveen. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-06 : 19:19:23
|
| There must be a setting somewhere else in your code then. The option in the connection string is just one of the place's to set it. As I am not a programmer, I can not help you out with the details.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-06 : 19:26:02
|
| If you know the query is going to take 30 minutes then there's little point in having it run that way through the interface. It would be better to put the query in a job and save its results to a table. This solves the timeout problem since the connection only needs to be open long enough to start the job (a few seconds at most) When it's done the user can open the query results table. The job can even send a notification when it's done. |
|
|
|
vemulavijay
Starting Member
32 Posts |
Posted - 2004-12-06 : 19:45:06
|
| Robvolk,Can u refer me for some material how to do this.Praveen. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-06 : 20:00:09
|
| Books Online will have more details under "jobs" and "sp_start_job". You'd change your Windows app to call sp_start_job. Here's a starter:1. In Enterprise Manager, open the server, go to Management, SQL Server Agent, Jobs folder2. Create a new job, enter a name for it3. On the Steps tab, add a new step as a Transact-SQL type4. In the command window, enter the name of the stored procedure you want to run. Make sure to select the proper database name5. Click OK until you're back to the jobs window. You'll see the new job you just created6. In your Windows code, where you now have a call to the stored procedure, change it to "EXEC msdb..sp_start_job 'myJobName'"Starting the job will NOT return the query results when it's completed. You will need to modify the stored procedure to insert the results into a separate table, and change your application to query that table instead of running the stored procedure. |
|
|
|
vemulavijay
Starting Member
32 Posts |
Posted - 2004-12-06 : 20:05:35
|
| Thanks Robvolk,It should work. Thanks a lot.Praveen. |
|
|
|
vemulavijay
Starting Member
32 Posts |
Posted - 2004-12-06 : 20:07:06
|
| But how can i know that the job is completed.PRaveen. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-06 : 20:20:12
|
| You can add notifications to the job to tell you when it's completed. These can be via email, pager, or net send message, but otherwise they're pretty limited. You can also add steps to the job that perform other types of notifications like writing to a log file or a queue table. Something like this would let your app query for a status on the completed job, and then provide the results or a link to them. |
|
|
|
|
Expiry time