• Replies 6 replies
  • Subscribers 339 subscribers
  • Views 39 views
  • Users 0 members are here
Related

Debugging Stored Procedures

Matthew Hodge
$organization

One for the geeks out there ...

What's the best way to debug stored procedures in SQL Server 2005? I heard that it can be done in Visual Studio 2005, but I haven't quite been able to get it to work. I'm not sure if that's because I've got it set up the wrong way, but I couldn't insert any breakpoints or access any sort of Step Into function, which made it difficult.

What do other people use?

Parents
  • $organization

    Hi Matthew,

    You can debug sp in SQL 2000 and SQL 2008, but  not in SQL 2005.


    ----code need to be checked:
    declare @myCustomer_no int
    set @myCustomer_no=12345
    select * from t_customer where customer_no=@myCustomer_no

    return ---if you want to stop the sp in the middle of sp.


    ---debug code
    print'@myCustomer_no is '
    print  @myCustomer_no
    select * into a# t_customer from t_customer where customer_no=@myCustomer_no


    ---check out
    ---check the Messages &
    select * from a# t_customer

    I attached impresario in SQL 2008 STD. but I never feel I have to use it debug sp. So I just leave it there.

    have fun

    Ben 

Reply
  • $organization

    Hi Matthew,

    You can debug sp in SQL 2000 and SQL 2008, but  not in SQL 2005.


    ----code need to be checked:
    declare @myCustomer_no int
    set @myCustomer_no=12345
    select * from t_customer where customer_no=@myCustomer_no

    return ---if you want to stop the sp in the middle of sp.


    ---debug code
    print'@myCustomer_no is '
    print  @myCustomer_no
    select * into a# t_customer from t_customer where customer_no=@myCustomer_no


    ---check out
    ---check the Messages &
    select * from a# t_customer

    I attached impresario in SQL 2008 STD. but I never feel I have to use it debug sp. So I just leave it there.

    have fun

    Ben 

Children
  • $organization in reply to Ben Gu

    Thanks guys. I'll keep the print command as a good cheat.

    In the end, it turned out that the problem I was having was that the procedure in question hadn't been run since late 2009, but for a variety of reasons, I thought it had something wrong with it. However, it turns out that there was nothing wrong with it whatsoever and I can now move on to something else...

    Nice photo change, by the way, Naomi.

  • $organization in reply to Matthew Hodge

    SSMS for SQL Server 2008 has a very nice toolset for debugging that operates very similar to VS debugging in other languages, as long as the database being used is also SQL 2008.

    -Ryan

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge
    Sent: Thursday, March 04, 2010 10:42 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] Debugging Stored Procedures

     

    Thanks guys. I'll keep the print command as a good cheat.

    In the end, it turned out that the problem I was having was that the procedure in question hadn't been run since late 2009, but for a variety of reasons, I thought it had something wrong with it. However, it turns out that there was nothing wrong with it whatsoever and I can now move on to something else...

    Nice photo change, by the way, Naomi.

    From: Ben Gu <bounce-bengu4278@tessituranetwork.com>
    Sent: 3/4/2010 9:22:53 PM

    Hi Matthew,

    You can debug sp in SQL 2000 and SQL 2008, but  not in SQL 2005.


    ----code need to be checked:
    declare @myCustomer_no int
    set @myCustomer_no=12345
    select * from t_customer where customer_no=@myCustomer_no

    return ---if you want to stop the sp in the middle of sp.


    ---debug code
    print'@myCustomer_no is '
    print  @myCustomer_no
    select * into a# t_customer from t_customer where customer_no=@myCustomer_no


    ---check out
    ---check the Messages &
    select * from a# t_customer

    I attached impresario in SQL 2008 STD. but I never feel I have to use it debug sp. So I just leave it there.

    have fun

    Ben 




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!