Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1395
  • Last Modified:

Debugging SQL Stored Procedures in SQL 2005

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
Mariyam
Asked:
Mariyam
  • 7
  • 6
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
MariyamAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
MariyamAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
from the server explorer, right-click your connection and select "Allow SQL/CLR debugging"
0
 
MariyamAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
so it is a credentials problem.
0
 
MariyamAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
MariyamAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
I have no problem asking support to close this question without assigning points.
0
 
MariyamAuthor Commented:
I believe all of the advise given was accurate but that I was not being allowed to implement it.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now