| Author |
Topic |
|
crayzd
Starting Member
4 Posts |
 Posted - 2008-08-06 : 18:38:21
|
| Hey all,I have a .NET 2.0 C# application running with a SQL 2005 Express backend. We are taking a proprietary flat file and running in through our application to add information to the database. We are having an issue at ONE of our client sites where it will run through 50 records (exact same point every time) successfully inserting records through the stored procedure then we get an error. "System.Data.SqlClient.SqlException: Could not find stored procedure 'InsertUser'." It then shows the same error for every record thereafter.One of the parent applications has code on startup that will put the stored proc back in the database. So, we verify the stored proc is not in the database, run the parent app, it puts the procedure back, we verify that the procedure is in the database, rerun the secondary app to import this flat file, get through 50 records and then get the error. We can run the cycle all day, it is perfectly consistent.Problem is, there is no code in any of the apps to drop the stored proc.We have these applications installed around the world and it is only this one client that is having this problem.Any ideas on what I am missing here or what I should be looking for?What have I gotten myself into? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
crayzd
Starting Member
4 Posts |
Posted - 2008-08-06 : 18:45:12
|
| It is on a client's XP machine that we can only access via pcAnywhere over dialup. How do you run the profiler under SQL 2005 Express? I have never been able to do it.What have I gotten myself into? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-06 : 18:51:50
|
| To run it, you'd need to install the SQL Server 2005 client tools on a machine that has direct access to the the XP machine. Typically for performance reasons, you don't run it on the same machine you are tracing. But since you are just tracking down a bug, you should just install it on the same machine.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
crayzd
Starting Member
4 Posts |
Posted - 2008-08-07 : 13:05:36
|
| Looks like we are going to be getting a copy of SQL 2005 Developer Edition to send to the client in order to upgrade their management tools. That will allow us to get the trace info that we need to troubleshoot the problem. Thanks for the info, I will post back when I know more.------------------What have I gotten myself into? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rademenes
Starting Member
2 Posts |
Posted - 2008-09-17 : 08:33:18
|
| hey,Did you manage to find the reason why your procedure was dropped? Actually i have similar problem and wonder where to start looking for the solution. Seems like when an exception is thrown within C# code, the stored procedure which has been executed just before the exception is dropped. And of course there is no code responsible for dropping it...rademenes |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-17 : 09:42:41
|
quote:
Originally posted by rademenes
hey,Did you manage to find the reason why your procedure was dropped? Actually i have similar problem and wonder where to start looking for the solution. Seems like when an exception is thrown within C# code, the stored procedure which has been executed just before the exception is dropped. And of course there is no code responsible for dropping it...rademenes
how do you infer that? have you used profiler to trace out what is happening on background? |
|
|
|
rademenes
Starting Member
2 Posts |
Posted - 2008-09-17 : 10:02:31
|
| Well, forget it, found the reason. The db script was generated with including 'IF EXIST' statements. Someone from my team must have messed with the script later and... included the 'IF EXIST... DROP' in another's procedure body. Hard to find when you look at the whole script, looks quite OK at first glance :)rademenes |
|
|
|
crayzd
Starting Member
4 Posts |
Posted - 2008-10-07 : 12:00:02
|
| Sorry for the late update but we did find it.In the script file it was missing the "GO" statement between the end of the stored proc and the beginning of the next "IF EXIST THEN DROP" statement.So what happened was that the drop statement was getting appened to the end of whatever stored proc was above it in the script. So when the software ran the stored proc it would drop whatever stored proc was below it in the script.It seems so obvious to us now but didn't make any sense at the time. We found it running the SQL profiler against a customer's database that was having the problem in the field.------------------What have I gotten myself into? |
|
|
|
|
Stored Procedure Disappearing