?
Solved

Debugging SQL Stored Procedures in SQL 2005

Posted on 2010-09-02
13
Medium Priority
?
1,413 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:Mariyam
  • 7
  • 6
13 Comments
 
LVL 71

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:Mariyam
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 71

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:Mariyam
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 71

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:Mariyam
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 71

Expert Comment

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

Author Comment

by:Mariyam
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 71

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 71

Accepted Solution

by:
Éric Moreau earned 1350 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:Mariyam
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 71

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:Mariyam
ID: 33795662
I believe all of the advise given was accurate but that I was not being allowed to implement it.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

589 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