Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sathishkumar
Starting Member
21 Posts |
 Posted - 2002-01-03 : 02:03:01
|
| Hi,I am calling a stored procedures inside the called stored procedure. this inner procedure job is delete records from specified table. it getting time out after some time (around 40 sec). There is any setting to allow inner procedure to run until the job completed or why it getting time out.with regardssathish |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-03 : 02:23:38
|
| Firstly, y dont u write the code for deleting in that stored procedure itself, if its not very large code and if you are not reusing it mulitple times. coz if you write the code in the stored procedure it will be faster to execute ,the server doesnt have to spend time on calling the stored procedure and returning.where are you calling this stored procedure from. if you are calling it from VB/Asp , try setting the connection object's timeout propertyconnectionObj.CommandTimeout=50 or more.If you have access to the server . In Enterprise manager, click on the property on the server and chose Connectiontab and increase the timeout setting.HTH----------------------------Anything that Doesn't Kills you Makes you StrongerEdited by - Nazim on 01/03/2002 02:30:11 |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-01-03 : 09:45:41
|
quote:
Firstly, y dont u write the code for deleting in that stored procedure itself, if its not very large code and if you are not reusing it mulitple times. coz if you write the code in the stored procedure it will be faster to execute ,the server doesnt have to spend time on calling the stored procedure and returning.
I don't think that one procedure calling another takes much time at all. There's very little overhead in one procedure calling another. And it's a great way to modularize code and make is easier to read, use and debug.As to the second point, the timeout is part of what is calling the SQL, it's not part of SQL Server itself. What are you using to call SQL Server?===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-03 : 17:21:39
|
| Whenever somebody mentions a timeout error, my first thought is to review the specific code they are using. Is your delete stored procedure written (as I would normally assume) just as a DELETE FROM tableX WHERE... statement? Or does it have a cursort in it? If it's a standard set-based DELETE statement, then how complex is the WHERE clause? How big is this table? Set-based DELETE's should not take very long to do. If it does, then perhaps you need to create an Index on the columns used in the WHERE clause. Is it possible that you're having some sort of deadlock conflict?--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
nested stored procedures