Solved

SQL Server Store Procedures Randomly Quit Working

Posted on 2012-03-17
6
326 Views
Last Modified: 2012-11-28
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
Comment
Question by:echarrison
  • 3
  • 2
6 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37734573
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 37734669
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
 

Author Comment

by:echarrison
ID: 37734949
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37735160
0
 

Accepted Solution

by:
echarrison earned 0 total points
ID: 38626463
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
 

Author Closing Comment

by:echarrison
ID: 38639531
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question