SQL Server Store Procedures Randomly Quit Working

I have a project that I am working on, where I call SQL Server stored procedures from with C# code. My problem is that the stored procedures will work fine for some random period of time, then all of a suddent they will stop working when invoked from within the C# application.  

If, I go to my Database project or to the SQL Server 2008 R2 Management Studio, I can execute them without any problem.

The only way to get them working again that I have found is to drop them and the create them again. However, that is not acceptable for an application that will be running in a production environment.

Has anyone else encountered this problem. If so, how did you get around the issue.
echarrisonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BuggyCoderCommented:
use sql profiler and check what happens when you execute procedure from your c# program. Make sure you apply required filters there so that you can only view the data that you are interested in....
0
TempDBACommented:
What error message (if any) do you see? What does that stored procedure do. It may be that the stored procedure runs with a parameter that doesn't perform anything.
0
echarrisonAuthor Commented:
There are no error messages that I can see. All of my execution code runs in a try catch block and throws both an SQLexeception and a regular exception which are written to a log file.

Also, BuggCoder mentioned using sql profiler and I would like to know where to find it.

Thanks for the feed back.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

echarrisonAuthor Commented:
All of the response were appreciated, however, the end result of the issue was that the verison of SQL Server 2008 R2 that we had installed did not have all of the service pack changes applied. This caused the common table expression SQL to not work correctly. I changed the stored procedures to use temporary tables and everything is working correctly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
echarrisonAuthor Commented:
None of the solutions provided were able to resolve the problem. It was not discovered what the actual problem was until we had worked with Microsoft for over a month, and even then we went with the solution that was identified in house.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.