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 |
|
lkmoody
Starting Member
1 Post |
 Posted - 2009-12-07 : 12:08:55
|
| Recently I created a new stored procedure that gets called from an SSRS report. Since I made the report available it will run for a few days then it will start timing out. If I drop the stored procedure and re-create it, it stops timing out at least for a few days. When the report is timing out I’ve gone into management studio and run the stored procedure and it takes minutes to run. But if I run the statements in the stored procedure without calling the sp itself it runs in seconds. I have several other reports that are similar that have similar stored procedures but I have not had the same issue with any of the other reports. I thought perhaps something got corrupted when I registered the report in SSRS so I deleted it and re-added it to SSRS but that hasn’t fixed it. I’ve started a SQL trace to see when that stored procedure is getting called, maybe that will reveal something. Other than that I’m at a loss of what to try next. Any suggestions would be great. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-07 : 12:12:44
|
| care to post the code?could be parameter sniffing if the sp is using a bad plan on subsequent runs.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
|
|
|
Stored Procedure takes longer and longer to run.