debugging with sql query analyzer, cannot get it to break

Using SQL Server 2000 SP3
I go into SQL Query Analyzer, expand Stored Procedures for my database,
right click the SP I want and select Debug...
It prompts me for the parameters. I enter them.
This seems to execute the procedure and give me @.RETURN_VALUE = 0.
I go through the procedure and set breakpoints at every line. I hit F5 for
GO and it runs right through it again and gives me the return value. It
does not break at my break points. The Step Into, Step Over, Step Out, and
Run to Cursor buttons are greyed out and the associated shortcuts don't work
either.
Anyone have an idea why I can't debug my SPs?
Thanks,
JamesIt's usually from a permissions issue. Take a look in BooksOnline under
"troubleshooting SQL Server, Transact-SQL debugger" and make sure to follow
all the steps.
--
Andrew J. Kelly
SQL Server MVP
"James" <capricorn@.nospam.com> wrote in message
news:Ojpa0yGsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> Using SQL Server 2000 SP3
> I go into SQL Query Analyzer, expand Stored Procedures for my database,
> right click the SP I want and select Debug...
> It prompts me for the parameters. I enter them.
> This seems to execute the procedure and give me @.RETURN_VALUE = 0.
> I go through the procedure and set breakpoints at every line. I hit F5
for
> GO and it runs right through it again and gives me the return value. It
> does not break at my break points. The Step Into, Step Over, Step Out,
and
> Run to Cursor buttons are greyed out and the associated shortcuts don't
work
> either.
> Anyone have an idea why I can't debug my SPs?
> Thanks,
> James
>|||Hi James,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.
From your description, I understand that you met with some problem when debugging a stored
procedure.
As Andrew has point out that you should have proper permission when you perform a debug
on stored procedures in QA. The greyed out button symptom are mostly caused by this
permission issue, but I'm really puzzled (maybe it's also a permission issue) that the execution
didn't break at the break points you set in advance. So please ensure yourself the proper/full
permission first to see if you can debug the SP in a normal way.
Based on my experience, the symptom can also be casued by the duplicated store
procedure names.
If you have two stored procedures with the same name, one owned by the database owner
(DBO) and the other owned by a non-DBO user (for example, dbo.test_proc and
xyz.test_proc), when trying to debug the xyz.test_proc procedure, neither the DBO nor the xyz
user can step through the stored procedure using the T-SQL debugger from SQL Server 2000
Query Analyzer.
The stored procedure will execute immediately under the T-SQL debugger when run from
Query Analyzer. Breakpoints can be set after the first execution, but none of the step-through
buttons are available. The problem disappears after the DBO-owned stored procedure is
dropped, and reappears when it is re-created.
If this addresses your problem, you can use any of the following workaround:
1. Rename the stored procedure owned by the non-DBO user.
2. Write a wrapper stored procedure to call the stored procedure owned
by the non-DBO user, and use the T-SQL debugger to step into the called
procedure.
3. Use the debugger from Microsoft Visual Interdev instead of from Query
Analyzer.
James, please let us know if this helps solve your problem. If there is anything more we can
do to assist you, please feel free to post it in the group
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Replying to both Andrew and Billy,
First, I am the system Admin on the machine I am developing on and the
system where the database resides. When I am using Query Analyzer, I am the
same account that is the dbo that is sa and that created the stored
procedures. Andrew, I haven't gone through the list of things there is for
me to do that you pointed out yet, but I can't see how its a permissions
issue given that I have complete permissions on every machine and database
instance involved. I will look up the issue in Books Online that you
pointed out and see if that helps.
Billy, as noted above, I have full permissions when I am debugging in Query
Analyzer. Also, I know for a fact that there are no duplicated SP names.
All stored procedures are created by the same account. I've absolutely
confirmed that this can't be the problem.
I also can't use the Visual Studio .NET debugger to step into the procedure,
now that you bring it up. I get the following error message: "Cannot debug
stored procedure because the SQL Server database is not setup correctly or
user does not have permission to execute master.sp_sdidebug. Run SQL Server
setup or contact database administrator." In response to this error, first,
EVERYONE including guest and public has rights to execute
master.sp_sdidebug, and again, I am the dbo and sa, so permission can't
possibly be the problem. In addition, Visual Studio .NET Enterprise
Architect is setup on the server where SQL Server 2000 is installed WITH all
of the remote debugging options checked. I go through the documentation,
stepping through all the steps, even reinstalling software to let it
configure itself again, and I continue to get this error. This is why I
went to Query Analyzer to try to debug the SP and encountered the problem
that started this thread.
Any help would be greatly appreciated.
James
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:JEb7U0lsDHA.1248@.cpmsftngxa07.phx.gbl...
> Hi James,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
this issue.
> From your description, I understand that you met with some problem when
debugging a stored
> procedure.
> As Andrew has point out that you should have proper permission when you
perform a debug
> on stored procedures in QA. The greyed out button symptom are mostly
caused by this
> permission issue, but I'm really puzzled (maybe it's also a permission
issue) that the execution
> didn't break at the break points you set in advance. So please ensure
yourself the proper/full
> permission first to see if you can debug the SP in a normal way.
> Based on my experience, the symptom can also be casued by the duplicated
store
> procedure names.
> If you have two stored procedures with the same name, one owned by the
database owner
> (DBO) and the other owned by a non-DBO user (for example, dbo.test_proc
and
> xyz.test_proc), when trying to debug the xyz.test_proc procedure, neither
the DBO nor the xyz
> user can step through the stored procedure using the T-SQL debugger from
SQL Server 2000
> Query Analyzer.
> The stored procedure will execute immediately under the T-SQL debugger
when run from
> Query Analyzer. Breakpoints can be set after the first execution, but none
of the step-through
> buttons are available. The problem disappears after the DBO-owned stored
procedure is
> dropped, and reappears when it is re-created.
> If this addresses your problem, you can use any of the following
workaround:
> 1. Rename the stored procedure owned by the non-DBO user.
> 2. Write a wrapper stored procedure to call the stored procedure owned
> by the non-DBO user, and use the T-SQL debugger to step into the called
> procedure.
> 3. Use the debugger from Microsoft Visual Interdev instead of from Query
> Analyzer.
>
> James, please let us know if this helps solve your problem. If there is
anything more we can
> do to assist you, please feel free to post it in the group
>
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hello James,
Thanks for your update and the detailed information.
It seems that the issue is not related to permission and it'salso not a tool issue. Could you help
check the following article in case it addresses your problem:
329282 INFO: Minimum Permissions for Debugging Applications in Visual Studio
http://support.microsoft.com/?id=329282
170496 INF: Tips for Debugging Stored Procedures
http://support.microsoft.com/?id=170496
For more information on how to troubleshoot and debug stored procedures in Visual
Studio.NET, you can reference these step by step articles:
817178 INFO: Troubleshooting Tips for T-SQL Debugger in Visual Studio .NET
http://support.microsoft.com/?id=817178
316549 HOW TO: Debug Stored Procedures in Visual Studio .NET
http://support.microsoft.com/?id=316549
If there is anything more I can do to assist you, please feel free to post it in the group
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.