Solved

Debugging SQL Stored Procedures in SQL 2005

Posted on 2010-09-02
13
1,366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

739 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