how can effectively debug stored procedures in visual studio

views:

472

answers:

how can effectively debug stored procedures in visual studio

ive been debugging sql 2005 stored procedures in visual studio 2005 and find it really frustrating that i can't run a sql query while stepping through the code.

specifically my main issues are...

if my SP inserts/updates to temp tables then i cant query those tables while debugging. would be cool if i could use the immediate window to run sql.
if my SP is run under a transaction i then again cant query any tables it effects while debugging. even if i switch over to sql management studio and run a query for uncommitted data.

only thing i can check in the debugger are local variables used in the SP which isnt all that helpful.

anyone know of a better way to debug? is this any better in VS.net 2008 or 2010?

+2 A:

Add lots of diagnostic SELECTS and PRINT statements.

Hey, it's what all of us programmers had to do back in the day before Debuggers. It's not that bad once you get used to it.

Also, this is one more, Very Good reason to keep the procedures small.

I have used real debuggers with SQL (other versions) and the truth is that well-written SQL benefits very little from a traditional debugger anyway. Declarative, Set-based code just doesn't fit that model well. Now poorly-written SQL (procedural, imperative, etc.) does, but the better you design the procedure, the less reason you have for a debugger anyway.

A better solution, IMHO, if you need that what a debugger could do for you, is to use the SQL Profiler, which has the advantage of being able to tell you what a procedure is actually doing in production, instead of just what it does in you artificial tests.

RBarryYoung 2009-07-17 21:50:49

it's the year 2009. we're meant to have flying cars soon but still cant even manage to get decent sql debugger off the ground.

MakkyNZ 2009-07-17 22:39:47

A true professional does not blame their tools. They learn how to make do with what's available.

RBarryYoung 2009-07-17 23:34:20

+1 A:

Here is an article on how to do it with Visual Studio 2005. Havent tried it myself, but 4GuysFromRolla have provided good info for me in the past.

DaveE 2009-07-17 22:01:03

+1 for the link I found to debugging Server 2000 in the linked article. A personal WTF?! moment came in discovering that as I've wanted it a few times and never discovered it.

Adrien 2009-07-17 22:12:34

ive read that article before. it only really talks about how to 'start' debugging. not much about what tools you have to analyse data with once your stepping through it. only thing it mentions is..."Once in the debugger we can step through the stored procedure's statements and view and edit the parameter values and variables through the Watch window" i want to be able to query not just view the variables.

MakkyNZ 2009-07-17 22:31:54

and you've SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?

DaveE 2009-07-17 22:47:13

DaveE 2009-07-17 22:56:33

yeah i run SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before running my query but it doesn't work.

MakkyNZ 2009-07-18 06:12:38

Better go through the this Debug proc

I got the idea from here.Hope this will help you too. This is the same way for VS 2008

anishmarokey 2009-07-18 07:08:02

ansaurus

tags:

views:

answers:

how can effectively debug stored procedures in visual studio

related questions