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 user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgs
Create a stored procedure which alls the function and debug the stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs
|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by step
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:

> Create a stored procedure which alls the function and debug the stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>
>
|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
[vbcol=seagreen]
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
|||Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
have the latest update of Books Online...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...[vbcol=seagreen]
> Hi,
> i have problem testing the function , though i have put a breakpoint but it
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
|||will do tks
"Tibor Karaszi" wrote:

> Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
>
>

debugging user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgsCreate a stored procedure which alls the function and debug the stored proce
dure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by ste
p
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:

> Create a stored procedure which alls the function and debug the stored pro
cedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>
>|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
[vbcol=seagreen]
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by s
tep
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
>|||Did you check out the troubleshooting section for the TSQL debugger in Books
Online? (Make sure you
have the latest update of Books Online...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...[vbcol=seagreen]
> Hi,
> i have problem testing the function , though i have put a breakpoint but i
t
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
>|||will do tks
"Tibor Karaszi" wrote:

> Did you check out the troubleshooting section for the TSQL debugger in Boo
ks Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
>
>

debugging user defined function in query analyzer

Hi Anyone ,
how do i actually debug a User Defined Function ?
rdgsCreate a stored procedure which alls the function and debug the stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> Hi Anyone ,
> how do i actually debug a User Defined Function ?
> rdgs|||Maxzsim
You could put in PRINT Statements at certain points in the code of the
function. Execute it from isqlw and test it. If Im not mistaken, step by step
debugging is possible from Visual Interdev.
Cheers!
SQLCatZ
"Tibor Karaszi" wrote:
> Create a stored procedure which alls the function and debug the stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> > Hi Anyone ,
> >
> > how do i actually debug a User Defined Function ?
> >
> > rdgs
>
>|||Hi,
i have problem testing the function , though i have put a breakpoint but it
never goes into the debug window , what could be the problem ?
From the Query Analyzer i can debug but all the button such as step inot ,
step over are all greyed out
i am using a LocalSystem account
rdgs
"SQLCatz" wrote:
> Maxzsim
> You could put in PRINT Statements at certain points in the code of the
> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> debugging is possible from Visual Interdev.
> Cheers!
> SQLCatZ
>
> "Tibor Karaszi" wrote:
> > Create a stored procedure which alls the function and debug the stored procedure.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> > > Hi Anyone ,
> > >
> > > how do i actually debug a User Defined Function ?
> > >
> > > rdgs
> >
> >
> >|||Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
have the latest update of Books Online...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
> Hi,
> i have problem testing the function , though i have put a breakpoint but it
> never goes into the debug window , what could be the problem ?
> From the Query Analyzer i can debug but all the button such as step inot ,
> step over are all greyed out
> i am using a LocalSystem account
> rdgs
> "SQLCatz" wrote:
>> Maxzsim
>> You could put in PRINT Statements at certain points in the code of the
>> function. Execute it from isqlw and test it. If Im not mistaken, step by step
>> debugging is possible from Visual Interdev.
>> Cheers!
>> SQLCatZ
>>
>> "Tibor Karaszi" wrote:
>> > Create a stored procedure which alls the function and debug the stored procedure.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
>> > > Hi Anyone ,
>> > >
>> > > how do i actually debug a User Defined Function ?
>> > >
>> > > rdgs
>> >
>> >
>> >|||will do tks
"Tibor Karaszi" wrote:
> Did you check out the troubleshooting section for the TSQL debugger in Books Online? (Make sure you
> have the latest update of Books Online...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:86B22286-0E2D-4820-9156-EA885C2B4909@.microsoft.com...
> > Hi,
> >
> > i have problem testing the function , though i have put a breakpoint but it
> > never goes into the debug window , what could be the problem ?
> >
> > From the Query Analyzer i can debug but all the button such as step inot ,
> > step over are all greyed out
> >
> > i am using a LocalSystem account
> >
> > rdgs
> >
> > "SQLCatz" wrote:
> >
> >> Maxzsim
> >>
> >> You could put in PRINT Statements at certain points in the code of the
> >> function. Execute it from isqlw and test it. If Im not mistaken, step by step
> >> debugging is possible from Visual Interdev.
> >>
> >> Cheers!
> >> SQLCatZ
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > Create a stored procedure which alls the function and debug the stored procedure.
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://www.solidqualitylearning.com/
> >> >
> >> >
> >> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> > news:228E583C-AB71-47D8-AB08-8A8F9833CBEA@.microsoft.com...
> >> > > Hi Anyone ,
> >> > >
> >> > > how do i actually debug a User Defined Function ?
> >> > >
> >> > > rdgs
> >> >
> >> >
> >> >
>
>

Debugging T-SQL Codes

Hi,
How can I use query analyzer to debug my codes and stored procedures. I need
something like debug tools of VB(watching line by line execution). Are such
tools available for SQL server?
Thanks,
AminYes, for stored procedures., In Query Analyzer, Object Browser, right-click
the procedure and you'll find the debugger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23gTTEiECEHA.3788@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I use query analyzer to debug my codes and stored procedures. I
need
> something like debug tools of VB(watching line by line execution). Are
such
> tools available for SQL server?
> Thanks,
> Amin
>|||In Query Analyzer, open the object browser. In the object
browser, go down to the stored procedures in a database,
select a stored procedure, right click and select debug.
-Sue
On Fri, 12 Mar 2004 19:12:08 +0430, "Amin Sobati"
<amins@.morva.net> wrote:

>Hi,
>How can I use query analyzer to debug my codes and stored procedures. I nee
d
>something like debug tools of VB(watching line by line execution). Are such
>tools available for SQL server?
>Thanks,
>Amin
>

Debugging SQL Server 2000 using Query Analyzer

I'm trying to debug some procedural functions in SQL Server 2000 using Query Analyzer, and not having much luck.

Any help would be appreciated!

Thanks,

Doug

I have seen people trying to do that but I think it does not exist but SQL Server tells you were the error is, and can show you execution cost, but if you have a problem stored proc check out the profiler at the top of the Query Analyzer. Hope this helps.|||

I just wrote a quick Stored Proc and called the function from that..

it stepped through the function fine..

dd

|||

That would mean all the issues list below by Microsoft are fixed.

http://support.microsoft.com/default.aspx?scid=kb;en-us;280101

Debugging Stored Procedure

Some days ago i asked here why my debugger in query analyzer never stops at code when i tried to debug a sp. I found the same question to be asked here again and again without any decent answer.
I found out my problem and would like to share it here :

hhtp://support.microsoft.com/?kbid=328151

My sql server was updated with Service Pack 3 but the client wasn't updated.
What i did was to turn on debugging on server for "older" clients with:

exec dbo.sp_sdidebug 'legacy_on'

Hope this will help other ppl too.Thanks for the link!

Debugging SP On server from workstation using Query Analyzer

Hai .....,
I 'm able to debug a Stored Procedure directly on the server, but I 'm not able to debug the same stored procedure from my machine. What should i do in order to debug the stored procedures on the server from my workstation.

(I can debug the stored procedures of my local sql server)

Please take a look at the "Troubleshooting the Transact-SQL Debugger" topic in BOL. The url is below:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\trblsql.chm::/tr_servtools_5cfm.htm

There are also additional KB articles that you can find in Technet on this topic.|||Hello Umachandran,
The Sql Server 2000 (on the server SP3 is not installed) runs on Win XP. Using the help file address that you have given me, I made the following adjustment but still the cursor does not wait on the first statement, Instead it waits for some time (Displaying wait for user input in the status bar) and returns with @.RETURN_VALUE = 0. I have did the following adjustment;

Start -> Run -> dcomcnfg -> Component Services -> Computers -> My Computer -> DCOM Config -> sqldbreg -> Right clicked and selected properties -> Security Tab and for Access Permission -> Edit -> Added System and Everyone and gave them both Local and Remote Access.

Still no luck. Any other suggestion|||Please take a look at the KB article below

http://support.microsoft.com/default.aspx?scid=kb;en-us;280101

This has additional information than BOL. You should also try this from a different client so you can start with a default configuration and see.

Debugging Query Analyzer & dates

Two of the sprocs input parameters are datetime. When I try to debug the
sproc SQL Server errors with
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
I've tried everything (except of course what might actually work).
Help!!!
regards
Frank Ashley
Try any of the ODBC styles, as defined in:
http://msdn.microsoft.com/library/en...on_03_04l0.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Two of the sprocs input parameters are datetime. When I try to debug the
> sproc SQL Server errors with
>
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
>
> I've tried everything (except of course what might actually work).
>
> Help!!!
> regards
> Frank Ashley
>
|||That worked first time.
Thanks
Frank Ashley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OZ0mPj4gEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Try any of the ODBC styles, as defined in:
> http://msdn.microsoft.com/library/en...on_03_04l0.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
>

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks
1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>
|||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

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:
> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||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

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||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

Debugging a stored procedure

When I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debugerWhen I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debuger|||try this code in sql new query editor & execute

sp_Configure 'clr enabled',1
Go
Reconfigure
Go|||This belongs in the SQL Server forum, so this thread will be moved.

moderator|||Please do not double post.


Changed title and merged duplicate threads.

debugger problems in query analyzer

I am receiving the folowing error when trying to use the stored procedure
debugger in query analyzer: [Microsoft][ODBC SQL Server Driver][SQL
Server]Unable to connect to debugger on [machinename] (Error = 0x800401f3).
Ensure that client-side components, such as SQLLE.DLL, are installed and
registered on [machinename]. Debugging disabled for connection 55.
This is using SqlServer 2000 installed on the same machine running windows
2000. I have read the troubleshooting document, and have verified that DCOM
is set up correctly and that all DLLs mentioned in the document are present
in their correct location and registered on the machine. The DLL mentioned in
the error, sqlle.dll, is only present on the machine under a vstudio6\common
folder and a vstudio8\common folder; the vsstudio6\common copy is the one
that is registered. This seems strange to me, as I would expect sqlle.dll to
be installed with other sqlserver tools and dll's, and not to depend upon
visual studio being installed upon the same machine. Is it possible that
installing vs has somehow replaced the original sqlle.dll? I know that
debugging did work in the past, although it has been several months since it
was last used, and it is possible that vs was installed during that time.
Anyway, any suggestions on how to fix this? I know that sp debuggung can be
done from vs, but would prefer to do it from query analyzer. I have system
administrator privileges on the machine and within Sqlserver. Thanks.
MunchingBill
MunchingBill (munchingbill@.newsgroups.nospam) writes:
>The DLL mentioned in
> the error, sqlle.dll, is only present on the machine under a
> vstudio6\common folder and a vstudio8\common folder; the
> vsstudio6\common copy is the one that is registered. This seems strange
> to me, as I would expect sqlle.dll to be installed with other sqlserver
> tools and dll's, and not to depend upon visual studio being installed
> upon the same machine.
This is also the SQLLE.DLL which is registered on my machine. SQL Debugging
seems to work on my machine.

> I know that debugging did work in the past, although it has been several
> months since it was last used, and it is possible that vs was installed
> during that time.
This is precisely why I have more or less completely abandoned the
SQL debugger. I used only occasionally, and it seemed that every time I
had to fight a battle to get it to work. That was mainly at work, where
I tried to debug on a common development server. I thought it would normally
work on a local server - but apparently I was wrong.
It's simply to easy to use debug SELECTs and PRINTs to make it worth
all the hassle with the debugger. If stuff are really hairly, the Profiler
can help too.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hello Bill,
I would like to know whether you are debuging a stored procedure from the
terminal client computer through a terminal server session.
You cannot debug a stored procedure from the terminal client computer
through a terminal server session.
For additional information about how to solve this problem, click the
following article number to view the article in the Microsoft Knowledge
Base:
280100 BUG: Transact-SQL Debugger Is Not Available Through Terminal Server
Session
Here also some articles for your reference
280101INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for
SQL Server 2000
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280101
Hope it helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks for the reply. No, I am not using a terminal server. I am using Query
Analyzer from the same machine that hosts the SqlServer 200 instance. I have
read the documents you mentioned, but the problem persists. Any other ideas?
MunchingBill
"Wei Lu [MSFT]" wrote:

> Hello Bill,
> I would like to know whether you are debuging a stored procedure from the
> terminal client computer through a terminal server session.
> You cannot debug a stored procedure from the terminal client computer
> through a terminal server session.
> For additional information about how to solve this problem, click the
> following article number to view the article in the Microsoft Knowledge
> Base:
> 280100 BUG: Transact-SQL Debugger Is Not Available Through Terminal Server
> Session
> Here also some articles for your reference
> 280101INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for
> SQL Server 2000
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280101
> Hope it helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hello Bill,
First, I would like to know whether you have applied the latest service
pack for SQL 2000.
How to obtain the latest SQL Server 2000 service pack
http://support.microsoft.com/?id=290211
Also, there is an article maybe helpful:
839280FIX: SQL debugging does not work in Visual Studio .NET after you
install Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;839280
Second, please let me know if you have installed any anti-virus software or
firewall. If so, please disable those software and try to debug it again.
If you still could not debug the stored procedure, please let me get your
Application Event log for further troubleshoot.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
Just want to say Hi, and I was wondering how everything is going. If
anything is unclear, please let me know. It is my pleasure to be of
assistance.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
================================================== ===
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Debugger Interface Problem in QA

Hello,
We've setup SQL Debugging on our server, but when I try and test in Query
Analyzer on the server I get the following error:
"The debugger interface is not installed. Please re-run setup, select "add
components to your existing installation' and make sure you select
'Development tools'\'Debugger Interface'."
However, this is already installed when I run setup. I am running SQL SP3,
and noticed some articles about that, but they didn't seem to apply to my
specific problem. I get the same error whether through terminal services or
at the console.
We're having a lot of problems making this work, if anyone can assist I'd
greatly appreciate.
Regards,
DerekI'm seeing the same issue, did you ever get a resolution?
Ron
"Derek" wrote:
> Hello,
> We've setup SQL Debugging on our server, but when I try and test in Query
> Analyzer on the server I get the following error:
> "The debugger interface is not installed. Please re-run setup, select "add
> components to your existing installation' and make sure you select
> 'Development tools'\'Debugger Interface'."
> However, this is already installed when I run setup. I am running SQL SP3,
> and noticed some articles about that, but they didn't seem to apply to my
> specific problem. I get the same error whether through terminal services or
> at the console.
> We're having a lot of problems making this work, if anyone can assist I'd
> greatly appreciate.
> Regards,
> Derek
>
>

Debug tsql

I tried to debug a procedure from query analyzer, but i could not able to step in or step out.
only few actions i could able to perform on the code,like execution,set break points.
step in and step out is not enabled.Is there any option to enable it..There is no "debug" of stored procs or in query analyzer. You will need to cut it down, exectue it line by line and use print statements, etc to see what is going on.

Are you getting an error?

If you post the stored proc here, someone might help you.|||

>>There is no "debug" of stored procs or in query analyzer

Are you sure?
Hit F8 this will open up the Object Browser, navigate to the proc, right click on the proc and select debug

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||I do not have a debug option on mine. Maybe you have the .NET development installed that gives you that?

Also, I might be wrong, but I think he said in one of the prior questions, he was using SQL 2000.|||

I am talking about query analyzer (SQL 2000)

Here I took a screen shot of it

http://tinypic.com/view/?pic=44hvq7a

Right click on the procedure and the last thing in the menu should be debug

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Ohhhh. You are correct. I had never tried that method.

That does give a simple debugger version that might do what he wants.|||Even if you were wrong about the existance of the debugger, I far prefer your original technique :)|||what is the solution for my problem..

Debug Stored Proc's in MSSQL 2005

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

You can not debug Sp from SQL Server enviornment in 2005.... use Visual Studio

Ref :http://msdn2.microsoft.com/en-us/library/zxsa8hkf(VS.80).aspx

Madhu

|||

Thanks, I had seen that one before but didn't want to write it as managed code, so I stop short looking further. I did however, follow a few more links at the bottom and it brought me to the article:

How To: Step into an Object Using Server Explorer
http://msdn2.microsoft.com/en-us/library/ms165041(VS.80).aspx

Thanks!

Debug Stored Procs in MSSQL 2005

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

I found the answer to my question.

How To: Step into an Object Using Server Explorer
http://msdn2.microsoft.com/en-us/library/ms165041(VS.80).aspx

Debug stored procedure in query analyzer

Trying to use Debug feature in query analyzer -- on some systems I get the
message
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
There's nothing wrong with the stored procedure. If I have this problem, it
happens with all stored procedures except those that have no parameters. If
there are no parameters, the message doesn't show, but debug still doesn't
work properly.
Carole
You may have to specify date parameter values in a specific format. For
example, see:
http://groups-beta.google.com/group/...f1164645c4a4f9
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"CDuttlinger" <h2oPower@.newsgroup.nospam> wrote in message
news:6E2C1A97-A747-4BC6-979B-CE090C67B6A4@.microsoft.com...
> Trying to use Debug feature in query analyzer -- on some systems I get the
> message
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
> There's nothing wrong with the stored procedure. If I have this problem,
> it
> happens with all stored procedures except those that have no parameters.
> If
> there are no parameters, the message doesn't show, but debug still doesn't
> work properly.
> --
> Carole
|||That doesn't explain why debug works on some computers and not others, with
the same stored procedures.
Carole
"Narayana Vyas Kondreddi" wrote:

> You may have to specify date parameter values in a specific format. For
> example, see:
> http://groups-beta.google.com/group/...f1164645c4a4f9
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "CDuttlinger" <h2oPower@.newsgroup.nospam> wrote in message
> news:6E2C1A97-A747-4BC6-979B-CE090C67B6A4@.microsoft.com...
>
>
|||Perhaps the debugger does something to the input, where what it does is dependent on Regional
Settings in windows?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CDuttlinger" <h2oPower@.newsgroup.nospam> wrote in message
news:ED00A9CE-A366-4F9D-B60A-7DE5F090302E@.microsoft.com...[vbcol=seagreen]
> That doesn't explain why debug works on some computers and not others, with
> the same stored procedures.
> --
> Carole
>
> "Narayana Vyas Kondreddi" wrote:

Debug Sql Server 2005 Stored Procedure in Sql Server Management St

I am trying to figure out how to debug a Stored Procedure (SP) from Sql
Server Management Studio. Like in Query Analyzer in Sql Server 2000, where I
could right click an SP and then click debug on it will let me debug the SP.
Of course, I would execute sp_sdidebug 'legacy_on' before doing that.
In Sql Server Management Studio I do not see an option for debugging an SP
when I right click the SP nor do I see debugging SP option in the menu. I
read BOL, and it mentions to run sp_sdidebug. I tried to run sp_sdidebug but
I get "Could not find stored procedure sp_sdidebug". I tried this step in
master DB, user DB, and msdb but all give me the same error.
The user who is trying to debug the SP is already a member of symin role.
I can debug the SP from Visual Studio 2005, but I would like the same
capability I had in Sql 2000 to be in SMSS. Besides, the Results windows in
SMSS gives me resultset, which I cannot get from the Visual Studio, if I use
VS for debugging.
I have also read that many of the features, such as debugging, xp_cmdshell
etc. come disabled by default in SQL 2005, which is okay. But could somebody
tell me how to make the entire Stored Procedure debugging process work from
within SSMS?
Reply appreciated.
Thanks>I am trying to figure out how to debug a Stored Procedure (SP) from Sql
> Server Management Studio.
Can you please post to the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1