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.

debugging sp sql 2000

Hi All.
I'm running SQL Server 2000 sp3 over a Windows 2003 Server, and my client
machine is a XP Pro sp2, with SQL Clients sp3.
I cannot debug stored procedure from my client machine and i recieve this
error:
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on servername (Error = 0x80070005). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on PC_CECCATO. Debugging
disabled for connection 89.
does anyone have a solution?
Many thanks.
StefanoSee "Troubleshooting the Transact-SQL Debugger" in BOL. Also, see if this
helps.
The T-SQL Debugger is turned off by default for earlier clients after you
install SQL Server 2000 Service Pack 3
http://support.microsoft.com/defaul...kb;en-us;328151
AMB
"stefano ceccato" wrote:

> Hi All.
> I'm running SQL Server 2000 sp3 over a Windows 2003 Server, and my client
> machine is a XP Pro sp2, with SQL Clients sp3.
> I cannot debug stored procedure from my client machine and i recieve this
> error:
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on servername (Error = 0x80070005). Ensure that client-side components, su
ch
> as SQLDBREG.EXE, are installed and registered on PC_CECCATO. Debugging
> disabled for connection 89.
> does anyone have a solution?
> Many thanks.
> Stefano
>
>|||Thanks for your message, but i already tried all the workarouds published in
MSDN...
i remember that with the same server i can debug if the client machine in a
windows 2000 professional... maybe is a problem of my Windows XP sp2
machine!
nothing about this'
regards
stefano
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEF974A8-85F7-4512-AA13-27D0E8860A51@.microsoft.com...
> See "Troubleshooting the Transact-SQL Debugger" in BOL. Also, see if this
> helps.
> The T-SQL Debugger is turned off by default for earlier clients after you
> install SQL Server 2000 Service Pack 3
> http://support.microsoft.com/defaul...kb;en-us;328151
>
> AMB
> "stefano ceccato" wrote:
>

Debug SQL User Functions

Does anyone know if there's a way to debug T-SQL user functions using sql
server 2000 sp3 on Windows Small Business Server 2003 from a client computer
running an Access 2003 mdb front-end on Windows XP SP2?
JayNot directly. You could debug the code if you create it as a procedure inste
ad.
ML|||ML,
I was afraid that would be the answer. The reason I wrote it as a function
is so that I could put it in a server-side query as in: "SELECT
MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". Is
there a way to do the same thing with a procedure?
Jay
"ML" wrote:

> Not directly. You could debug the code if you create it as a procedure ins
tead.
>
> ML|||All you need in such a case is a sample of input parameters - for instance i
n
a table variable - and then you execute the code you intend to use in the
function with each set of parameters in a loop.
Or maybe you can post your DDL and get a 'second opinion'.
ML|||Try this out:
declare @.output2 varchar(30)
exec testbysandeep 1,2,@.output1=@.output2 OUTPUT
select @.output2
output1 is the field which is the output of the function. You assign this
value to the output2 variable.Then by doing a select statement you display
the value.
Hope this helps.
jains
"jay" wrote:
> ML,
> I was afraid that would be the answer. The reason I wrote it as a functi
on
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;". I
s
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Jay,
You can debug a UDF from QA by making a small SP that calls the UDF. The QA
debugger steps into the UDF with F11 the same as VS.
Cheers
Doug Forster
"jay" <jay@.discussions.microsoft.com> wrote in message
news:E2A1224F-03FA-4422-BD86-215675844400@.microsoft.com...
> ML,
> I was afraid that would be the answer. The reason I wrote it as a
> function
> is so that I could put it in a server-side query as in: "SELECT
> MyFunc(SomeField, @.SomeInputVariable) AS SomeLabel FROM dbo.SomeTable;".
> Is
> there a way to do the same thing with a procedure?
> Jay
> "ML" wrote:
>|||Hi Doug Forster,
Is there any kind of configuration setting involed at server side ?
With warm regards
Jatinder|||Well I do this ON the server with admin rights and it just works. Maybe
someone else knows if it is possible to debug from another box, though I
notice the docs caution against debugging on a production server.
Cheers
Doug Forster
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...
> Hi Doug Forster,
> Is there any kind of configuration setting involed at server side ?
> With warm regards
> Jatinder
>|||Hi Forster ,
The problem is that it is not working (debuggin) even on Server .
It says that you are logged as 'Local Account' . Do I have to Logon the
service as administrator woul that effect other clients?
With warm regards
Jatinder Singh
Doug Forster wrote:
> Well I do this ON the server with admin rights and it just works. Maybe
> someone else knows if it is possible to debug from another box, though I
> notice the docs caution against debugging on a production server.
> Cheers
> Doug Forster
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1122878941.648594.17860@.g47g2000cwa.googlegroups.com...|||Hi I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks