• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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.
0
echarrison
Asked:
echarrison
  • 3
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now