Solved

Debugging SQL Stored Procedures in SQL 2005

Posted on 2010-09-02
13
1,355 Views
Last Modified: 2012-05-10
I used to utilize this feature a lot in 2000 but have never used it in 2005.  Can anyone give me step-by-step instructions for doing so please?  I need to step into the SP on the server from a VB.net WinForms app on my client computer in Visual Studio 2008 Professional edition.  

Currently the breakpoints in the SQL stored procedure are hollow circles with the message "the breakpoint will not be currently hit" "no symbols have been loaded for this document".

0
Comment
Question by:FoxProMari
  • 7
  • 6
13 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33588702
if you open your stored procedure from the server explorer in VS2008, can you step into it?
is the user of your ADO.net connection string is a sysadmin on SQL server?
0
 

Author Comment

by:FoxProMari
ID: 33592348
Question:   If you open your stored procedure from the server explorer in VS2008, can you step into it?"  
Answer:      No I cannot.  That's where the hollow breakpoints with the previous message of "the breakpoint will not be currently hit" and "no symbols have been loaded for this document" is displaying.

Question:   Is the user of your ADO.net connection string is a sysadmin on SQL server?"
Answer:     "Integrated Security=True" in the connection string.  I checked the Logins and Sysadmin is checked-marked for my account on the Server.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33592442
can you try to run this on the server: Exec sp_sdidebug 'legacy_on'
also in your project properties, ensure that SQL Server debugging is checked (from the debugging tab)
0
 

Author Comment

by:FoxProMari
ID: 33593064
SQL Server Debugging was not checked marked but it is now.  I'm still getting the "breakpoint" and "no symbols loaded" messages.

When I attempted to execute the sp_sdidebug I received the following message:
"Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_sdidebug'."

When I attempted to grant permissions to my account I received this message:
"Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'sp_sdidebug', because it does not exist or you do not have permission."
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33594894
from the server explorer, right-click your connection and select "Allow SQL/CLR debugging"
0
 

Author Comment

by:FoxProMari
ID: 33621294
I have done so and I am still getting the message stating "no symbols loaded".

One additional observation I've made is that the option to Step-Into-Stored-Procedure doesn't even appear on the shortcut menu (right-click on SQL procedure name) when using the credentials that the application is executing under to create a Data Connection in Visual Studio.

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33621376
so it is a credentials problem.
0
 

Author Comment

by:FoxProMari
ID: 33623412
It would appear to be a credentials problem.  I've created a new SQL account and started using it in my development environment and part of my code started working but I still can't step into SQL while the application is executing.  After working briefly, the app has began displaying the same problem.

New Message displaying:
"The breakpoint will not be currently hit.  Unable to bind SQL Breakpoint at this time.  Object containing the breakpoint not loaded."

The specific problem that I'm trying to debug is that the data in the application is being saved to the dataset tables but for some reason is not consistently being saved to the SQL tables.  

Every now and again, the data shows up in SQL but since I can't trace execution all of the way into the SQL stored procedure, I can't really be sure what the method that calls the stored procedure is doing.  It appears to be executing the UPDATE command on the SQL DataAdapter, and if I manually supply parameter values to the SQL stored procedure it works, but I can't get it to break execution in the stored procedure with the actual passed parameters which is really what I need in order to verify that the front-end and back-end are communicating properly.  

The only other thing I have noticed is that the security software sometimes interrupts execution and I have to give the client app permission to continue.  And recently I received a message that says "There is no source code available for the current location" when I stepped into the line containing the class method to execute the UPDATE command in order to save any property changes.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33625378
>>The specific problem that I'm trying to debug is that the data in the application is being saved to the dataset tables but for some reason is not consistently being saved to the SQL tables.  
Start a Profiler on SQL Server. You will see every request sent to it.
>>And recently I received a message that says "There is no source code available for the current location" when I stepped into the line containing the class method to execute the UPDATE command in order to save any property changes
Because the Update method calls code that is not your own.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 450 total points
ID: 33625382
Also, have you tried installing SQL Server on your development PC? It is much more easier to set up.
I also think that there is an option when you install SQL Server to install the debugger.
0
 

Author Comment

by:FoxProMari
ID: 33679630
Sorry for the delay,  but things have been really hectic.

SQL Server is installed on my development PC and that's the only way I have to connect to the SQL Server.  The company does not want copies of the data on local machines though.

I still can't use the debugger but I really appreciate you taking the time to troubleshoot this to the extent that you have.  I don't have a problem giving you the points but I did that once before when a member spent a lot of time trying to help me even though the specific problem was never resolved and someone else complained.  Loudly.

Please advise on the best way to resolve this.

Thanks again for all of your assistance.

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33680523
I have no problem asking support to close this question without assigning points.
0
 

Author Closing Comment

by:FoxProMari
ID: 33795662
I believe all of the advise given was accurate but that I was not being allowed to implement it.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now