Solved

Debugging SQL Stored Procedures in SQL 2005

Posted on 2010-09-02
13
1,362 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 70

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 70

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 70

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
 
LVL 70

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 70

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 70

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 70

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

856 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