September 1, 2004 at 9:48 am
I have a query which when run outside a stored procedure runs fine but when I the same query in a stored procedure it just does not execute just takes forever to execute. Initially I thought maybe the indexes on the tables in my query are messed up but after running DBCC SHOWCONTIG on each of the tables in the query i found the tables do not need reindexing. Can any one plz help me out with this.
I have the same stored procedure working in other databases with similar table structure and it works fine.
September 1, 2004 at 10:04 am
I had a similar problem with SP3a where the stored procedure would work IF it had no parameters, otherwise it would take 50x as long to complete.
I would check your version
September 1, 2004 at 1:29 pm
Can you post the SP?
How are you running the SP? Via a job?
Are there any error messages in SQL Server Error Log or the Windows Event Viewer Logs?
-SQLBill
September 2, 2004 at 1:46 am
Have you tried stepping through the stored procedure in SQL query analyser?
Right click on the offending procedure within the Object Browser and you will see "Debug" at the bottom of the menu.
If your underlying table structure has changed since the stored procedure was written then you will need to do an sp_recompile on the tables that have changed. This will force all dependent stored procedures/views to recompile to take account of the new structure.
Similarly use sp_refreshview on views.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply