• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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