Debug SP called from .NET application

[RRinTetons]

SQL Server 2008

I've got an SP that is called from a .NET application. When I test it in SSMS it give correct results, when I test it from the application it doesn't. The application's code is NOT accessible to me.

I can put a breakpoint in the code and step through it in SSMS, but I don't see a way to do that such that I can step through the execution when it's called from the application.

Is there a way to do that?

-
Richard Ray
Jackson Hole Mountain Resort

 

[bborissov]

Start SQL Profiler.
Run the application and see what query it sends to SQL Server.
Then copy and paste it in New Query Window and debug it.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[RRinTetons]

The SP code is mine, I wrote it. It's the .NET code I can't get at. I need to see what's happening differently between its behavior when I call it vs. its behavior when the application calls i.

-
Richard Ray
Jackson Hole Mountain Resort

 

[bborissov]

That is what I'm saying.

With SQL Profiler you can get HOW the Application calls your SP.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[RRinTetons]

OK, I see where it will be helpful to see if the SPis being called with the wrong inputs, or at the wrong time. It does make sense that if the code performs correctly when called correctly in SSMS, then it's not getting called correctly by the application.

That said , I'd *still* like to be able to break and step through the code when it's called by the app, but I can scaffold it to get some idea of what's going on internally, too. That will help.

-
Richard Ray
Jackson Hole Mountain Resort

 

[bborissov]

It doesn't matter WHEN the code is called from application.
The thing that matter is HOW SP is called.
And with SQL Profiler you can see HOW the application call your SP. You then can STOP trace copy and paste this code in "New Query" window and DBEUG thr code sent by application to SQL Server.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[TysonLPrice]

There are several ways to step through a stored procedure at execution time and your DB access can affect them. This might be a start:

http://support.microsoft.com/kb/316549

 

[TheBugSlayer]

You cannot step through the SP code when it' s called by the application because you do not have the application' s source code. The only two ways I know to debug SPs are explained in the link Tyson shared with you. Combine that with Boris' suggestion and you should get to the bottom of your problem. I.e setup a SQL Server Profiler trace, capture the call to your SP (pause or stop trace and copy statement); now, from VS call your SP with the parameters in the call you captured previously and step through it.

Since you don' t have the source code to your .NET app, even if the code is massaging the parameters into the wrong value, there is nothing you can do about it, but at least you will be able to see which values are being passed and compare actual vs. expected.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[RRinTetons]

I'd figured that out from reading the link Tyson provided.

I think I've narrowed it down via SQL Profiler, though. Can you suggest any useful settings for the trace events & columns to get in the trace file? I've got what I want, but I've also got a ton of stuff I don't want. It's a very active server and there's a LOT going on.

-
Richard Ray
Jackson Hole Mountain Resort

 

[philhege]

The easiest thing to do is determine what process ID the app is using to connect to SQL (via Activity Monitor), or, if the ID changes every time, the application name, and filter the respective columns on those values.

HTH,
PH

-------++NO CARRIER++-------

 

[TysonLPrice]

Here's is another link on stepping through a stored procedure but as it was mentioned if you can't change the code you are suck anyway.

http://www.codeproject.com/KB/database/SQLDebuggingVSNET.aspx

One thing I've done before is create a table with generic columns for the stored procedure and capture the variables, steps in the SP, and various outputs. Run it standalone and from the application and see what is different.