Solved

SQL Server Store Procedures Randomly Quit Working

Posted on 2012-03-17
6
303 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now