Debugging T-SQL Stored Procedures in VS 2005

Debugging T-SQL Stored Procedures in VS 2005

• RustyJCNC

  You can not debug into SQL Server 2000 stored procedures or anything else within Visual Studio 2005. Even though this was a capability they supported in VS 2003, they deceided to take this away because: "it was too difficult to do with the increased security of the newer service packs and software releases... -- Microsoft Technical Support"

  I spent $245 on an incident with Microsoft to find this out.

  You can debug into SQL Server 2005 database objects with Visual Studio 2005. Humm.... Maybe I should upgrade my database to a new license for SQL Server 2005.
  

  
  

• Mike Gledhill

  Azeem,

  Thanks for this information.  I was having a similar problem (VS 2003 would step-into/debug a SQL 2000 stored proc fine, but my VS 2005 would "skip" the breakpoint for the very same SQL 2000 Stored Proc).  I would get a disabled breakpoint with the "objects not loaded" message.  And yet my VS 2005 would debug other SP's on other SQL 2000 servers just fine.

  I've been searching all over the internet for the fix, and finally came across your post (04-07-2006 12:39 AM). Upon review, my mssdi98.dll & sqldbg.dll were an older version.  I've updaed tham and now both VS 2005 & VS 2003 debug MY SQL stored Procedures correctly! Great post.

  Thanks again,

  Michael Froman

  
  

• Tom Arnold

  Mark,

  Could you check the version of the file mssdi98.dll on your box that is next to sqlservr.exe It should be 8.11.50727.42. If you have an earlier version of this file then it would explain the behavior you are seeing. You should have a copy of the VS 2005 version on your machine in a subdir under Program Files\Microsoft Visual Studio 8. If not, you should be able to get it off the VS 2005 CD. Ensure that the file sqldbg.dll under Program Files\Common Files\Microsoft Shared\SQL Debugging is upto date as well. If this needs to be updated then be sure to register it after copying it over using "regsvr32 sqldbg.dll". Thanks.

  
  

• srinivas_234

  It ran straight the procedure without stopping at the break point and output the following:

  No rows affected.

  (0 row(s) returned)

  No rows affected.

  (0 row(s) returned)

  Running [dbo].[HDSP_select_all_appGUIDOrganizations].

  OrgPK OrgParent OrgName CreationDate LastUpdateTime Comments bDeleted OwnerGUID

  -------------------------------------- -------------------------------------- -------------------------------------------------- ----------------------- ----------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------------------------------------

  No rows affected.

  (1 row(s) returned)

  @RETURN_VALUE = 0

  Finished running [dbo].[HDSP_select_all_appGUIDOrganizations].

  The thread 'DMAZ1CHAMS(63)' (0x800014d0) has exited with code 0 (0x0).

  The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).

  Event Log

  There were no Event Log entries in either System or Application

  
  

• blackspider

  J-Mac,

  I responded to the thread you started as your issue could be different from Mark's.

  In any case debugging stored procedures on a SQL Server 2000 using VS 2005 is supported. What is disabled by default is application debugging since it has security implications. Application debugging is when you debug an application and then try and hit breakpoinst in stored procedures called by that application.

  The eventlog of interest is the one on the machine where SQL Server is running. You don't say if SQL Server is on the same machine as VS or another machine.

  For SQL Server 2000 debugging of stored procedures you need to have exec permissions on sp_sdidebug in the master db, own the db/sproc and setup DCOM settings correctly (search for enable sql debugging on msdn).

  Thanks.

  
  

• Eddie Hulme

  Azeem,

  I checked both of the files you mentioned, they're both where you said they'd be, and they both are at the version you mentioned. I tried unregistering and registering the sqldbg.dll, but this had no effect. I still can't get VS 2005 to stop in debug mode in a stored procedure.

  Any other ideas Thanks for your help.

  Mark Korenkiewicz

  Mitretek Systems

  College Park, MD

  
  

• GeorgesZ

  Mark,

  Could you let me know what it is you see in the ouput window and in the eventlog when you try and step into a stored procedure

  Thanks.

  Azeem.

  Visual Studio Debugger.

  
  

• Robert D Misiak

  Anyone,
  
  You would think that if this is a known issue, and the fix is such that somebody at MS would have the initiative to write a utility to enable this for us, without us having to preform these tasks ourselves, now that would be a "Productivity gain" !!
  
  
  
  

• MasonUSA

  Mark, I read the How To: article on MSDN and actually spent about an hour wrestling with constant lock ups and memory problems. I guess that's why it currently has a rating of 1. If 0 were an option that would have my choice.

  The ONLY way I have managed to get this to work is to open up a database project first, open the database in Server Explorer and then "step into" seems to work.

  Scott Rudy
  EDS
  Rockville, MD

  
  

• davisp2

  In response to your earlier posting Azeem, when I click the "Step Into Stored Procedure" right-click menu item the stored procedure I'm attempting to debug just runs to completion. Even if I've set a break point in the stored procedure, it never gets into debug mode. When I do click this menu item, I do get the dialog box asking for parameter values for the stored procedure.

  I've check all the permissions and settings regarding DCOM and they all appear to be correct. As I indicated on my original posting, I can debug stored procedures without a problem in VS 2003.

  The SQL Server I'm accessing is on my local machine, therefore, both VS 2005 and SQL Server 2000 are on the same machine. I am not attempting to debug stored procedures called while my application is running.

  The database connection I'm using in Server Explorer is using the 'sa' account so it should have all the access it needs inside of SQL Server.

  I'm still thinking that its something that's different between VS 2003 and VS 2005 that is not allowing the SQL Debugger to run in VS 2005.

  
  
• Debugging T-SQL Stored Procedures in VS 2005