| Author |
Topic |
|
schuhtl
Posting Yak Master
102 Posts |
 Posted - 2004-05-24 : 17:02:52
|
| I have a stored procedure that will keep running and running. I can copy the sql and paste it into query analyzer delcare the variables, set parameter values and the query returns the result in seconds. Any idea what the problem could be? The stored procedure has worked fine for several months and all of a sudden it stopped working. I have tried to drop it and re-create it and still nothing.Thanks |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-24 : 17:22:26
|
| Can you post the code? That might point us in the right direction.Also, have any service packs been installed recently?What error message do you get, or do you just get no rows back?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 18:38:39
|
| You might want to setup a trace in SQL Profiler to figure out what is going on. Compare the results from your applicatin calling the stored proc and from you calling it from Query Analyzer.Tara |
|
|
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2004-05-25 : 09:55:29
|
| Tara,I am really not sure what to look for when I set up a trace? I set up two traces: The first trace I removed all of the default selected event classes and added all of the available Stored Procedure event classes. The second I removed all of the default event classes and added all of the available TSQL event classes. If I execute the sp from query analyzer the sp keeps running for more than 15minutes before I kill it. If I copy the sql from the sp and paste it into query analyzer and declare and set the same parameter values as I ran in the previous step I get results in several seconds. The only difference I can see when I compare the traces is just the declaration of the variables and then setting values. I am so confused?????? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-25 : 12:22:58
|
| So in both scenarios, you are running the stored procedure from Query Analyzer? One inside a stored procedure, another with the code pulled out? If so, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. See if that resolves your problem. If not, drop and recreate the sproc again.Tara |
|
|
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2004-05-25 : 12:58:33
|
Tara,The answer to your question is yes but I tried what you suggested and it worked!  . You are awsome! What does DBCC DROPCLEANBUFFERS do? I tried looking it up but couldn't find any useful info.Thank you again! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-25 : 13:04:01
|
| FREEPROCCACHE forces recompilation of the stored procedures since the execution plan no longer exists in cache. DROPCLEANBUFFERS clears out the cache.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-25 : 14:05:22
|
quote:
Originally posted by schuhtl
I have tried to drop it and re-create it and still nothing.
I don't believe that's the cause...mostly because s/he already recompiled it....Where is the sproc running from?quote:
stored procedure has worked fine for several months and all of a sudden it stopped working.
Damn Miracle thing again....Something changed...and unless the sproc got modified, I'm thinking the app was changed....Brett8-) |
|
|
|
|
sp wont run but the same sql in query analyzer wil