Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Management Studio (SQL Server 2005) Express how to debug store procedure

Posted on 2009-05-03
12
Medium Priority
?
719 Views
Last Modified: 2013-11-26
I do not see a debug option within sql server manager studio to allow me to break within a stored procedure.  Is it a versioning issue, a setting,  or I am just not finding it?

Thanks in advice,
Howard
0
Comment
Question by:Howard Bash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24290910
I would recommend working in Visual Studio instead of Management Studio.  Among other things, I find the way VS works with SP's to be much more intuitive and much easier to work with.
I believe you can set break points in the SP in VS.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 24291987
That sounds good also.  I am using a gridview with an sqldatasource for selects, edits, and deletes and stepping from the update click into the stored procedure would be great.  What are the steps to round trip from clicking the update button on a grid to breakpointing in a stored procedure which is sitting on  a server waiting to execute?   How do I set the up in my vs2005 project?

Thanks.

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 600 total points
ID: 24309329
I wasn't necessarily talking about walking from the VS Solution into the SP.  What I was saying is that you can open the SP in VS and execute the SP from VS.  It lets you set up the parameters and then, I believe 9it's been a little bit since I was doing it ;-), you can step into the SP and have break points that let you check things.  However, I generally use PRINT to output things along the way and I watch the OUTPUT tab.
I also advise creating and debugging the SP's behind the SELECT, INSERT, UPDATE, and DELETE functionalities prior to actually cranking them up in the Solution's gridview.
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 1

Author Comment

by:Howard Bash
ID: 24311059
In the name of trying to debug from within VS2005,  I have been trying to add an sql server (not my local sql express) as a data connection and cannot seem to get to a dialog for connecting to a remote sql server.  I only see a browse to an sql server data file.

Can you clue me in as to how to connect my sql server to vs2005 to let me debug as you suggest above?

Thanks.
0
 
LVL 15

Assisted Solution

by:MohammedU
MohammedU earned 300 total points
ID: 24414128
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24416782
Talking through my hat -- most likely M$ crippled the SSMS Express just like they crippled Visual Studio Express. The VSE makes you jump through hoops to connect to a full blown remote SQL Server Standard. Probably the same issue here.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24418775
Debugging a SP in a query window is not too bad, just a bit manual...

e.g. suppose I right clicked on my SP and selected Script SP as; Create to; New query Window - I would get new window with my sp something like :

create procedure usp_My_liddle_bewdy(@p1 varchar(60), @p2 int, @p3 datetime)
as
begin

       if @p1 is NULL return

       declare @date datetime
       declare @order int
       declare @customername varchar(60)
       declare @ordervalue money
       declare @orderdiscount money

       set @date = isnull(@p3,getdate())
       set @order = isnull(@p2,0)
       set @customername = @p1

       if exists (select @customername from tbl_frequent_buyer_program)
       begin

             set @ordervalue = (select sum(orderamount) from tbl_my_orders where ordernum = @order and orderdate <= @date)
             set @orderdiscount = case when isnull(@ordervalue,0) < 1000 then @ordervalue * 0.10 else @ordervalue * 0.20 end

       end

       select @orderdiscount as loyalty_discount

end
go


then the way to debug it is pretty straight forward - use the print and/or select command... Comment out the definition of the procedure and insert the commands as necesary, and then it is a straight query...
 
the reason I use the "script sp as create to new query window" is so it will not be recreated if accidentally run (already exists therefore fails"


--create procedure usp_My_liddle_bewdy(@p1 varchar(60), @p2 int, @p3 datetime)
--as
--begin

-- debug
declare @p1 varchar(60), @p2 int, @p3 datetime
set @p1 = 'my test values'

       if @p1 is NULL return

       declare @date datetime
       declare @order int
       declare @customername varchar(60)
       declare @ordervalue money
       declare @orderdiscount money

       set @date = isnull(@p3,getdate())
       set @order = isnull(@p2,0)
       set @customername = @p1

select '>> Step 1 - values <<', @date, @order, @customername

       if exists (select @customername from tbl_frequent_buyer_program)
       begin

select '>> Step 2 - found <<', @customername

             set @ordervalue = (select sum(orderamount) from tbl_my_orders where ordernum = @order and orderdate <= @date)
             set @orderdiscount = case when isnull(@ordervalue,0) < 1000 then @ordervalue * 0.10 else @ordervalue * 0.20 end

select '>> Step 3 - Order Totals <<', @customername, @ordervalue, @orderdiscount

       end

select '>> Step 4 - return values <<"

       select @orderdiscount as loyalty_discount

--end
--go



then when I find the error, can fix, annotate, remove all the debug stuff and then "alter" the procedure.

Bit manual, bit fiddley but easy to do and easy to put in SQL command to helop identify the real problem...
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 600 total points
ID: 24418791
If you can show the stored procedure and describe the problem, might be able to help a bit more.

But stepping through in debug mode is not really an option for you without a lot of pain and headache. Avoid the pain and headache and simply run the procedure as a query with the appropriate diagnostic information.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 24566691
I don't see debugging available in management studio.  Is there some special setup that I must perform to have debugging available in that environment?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24566791
If you really must use the debugger, in SQL2008 have a read of : http://msdn.microsoft.com/en-us/library/cc646024.aspx

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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