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 SQL SERVER Stored Procedures

I don't see the option to debug a stored procedure. I right click onthe procedure and should'nt it appear in the properties window. Do Ineed to install some extra component to get the debugger.

I don't remember clearly but IMO it was dependant on the VS.NET version. But don't take that one as granted, I really don't remember. Try Google.

Anyways,here is explained what is needed to setup debugging and how to try it out. Andhere'salso a few words how to debug in Query Analyzer and again the instructions visualized for VS.NET.

|||Thanks for the reply.
I went to this link:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxlrfsettingupsqldebuggingenablingsqldebuggingonsql2000desktop.asp
And copied the .dll from the Visual Studio 2003/sqlserver it says put the file in the binn directory. Are they talking about the bin directory of the Asp.net Project.

|||okay so I copied the .dll file into SQL SERVEr Binn folder. But nowwhen I debug the strored procedure it just shows the Arrow => butwhen I press F11 so it can go to the next line It just exits
And prints the message :
// GEEK0001 is the name of my computer
The thread GEEK0001 has exicted with code 0 (0x0)
|||okay I got it to work !
:D

Debugger help

Could anyone please give me a quick head start on the debugger in SQL Query
Analyzer. I right click on my 'sp' and select debug. This shows my code in
the right pane. However I seem unable to step though. The only buttons
highlighted are 'Go' and the 2 breakpoint buttons. Only when I click 'go' d
o
the step through, step over, etc buttons become highlighted by which time it
is too late as my sp is already processing.Set a breakpoint at the beginning of the procedure then click 'Go'.
ML|||Hi
I think that if you click on the procedure declaration your buttons should
become active.
John
"Chubbly Geezer" wrote:

> Could anyone please give me a quick head start on the debugger in SQL Quer
y
> Analyzer. I right click on my 'sp' and select debug. This shows my code
in
> the right pane. However I seem unable to step though. The only buttons
> highlighted are 'Go' and the 2 breakpoint buttons. Only when I click 'go'
do
> the step through, step over, etc buttons become highlighted by which time
it
> is too late as my sp is already processing.

debug

Hi all,
I go into SQL QueryAnalyzer for debugging. Expand Stored Procedures for my
database, right click the SP I want and select Debug. It prompts me for the
parameters and I enter them. It 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.
Sql Server is running under a domain user account, not the local system
account.
Server is SQL Server Enterprise Edition, version 8.00.818 (SP3). Client is
8.00.194, Windows XP, SP2.
I've read Sql Server Tools Troubleshooting, but everything is ok.
Anyone have an idea why I can't debug?Please post the stored procedure so that we can better assist you. Without
being able to see what you are working with, we are just guessing.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"meri2" <meri2@.gmail.com> wrote in message
news:OLFOQzwqGHA.1796@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I go into SQL QueryAnalyzer for debugging. Expand Stored Procedures for my
> database, right click the SP I want and select Debug. It prompts me for
> the parameters and I enter them. It 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.
> Sql Server is running under a domain user account, not the local system
> account.
> Server is SQL Server Enterprise Edition, version 8.00.818 (SP3). Client is
> 8.00.194, Windows XP, SP2.
> I've read Sql Server Tools Troubleshooting, but everything is ok.
> Anyone have an idea why I can't debug?
>
>|||It happens with all the stored procedure.
thanks
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OdmWh1zqGHA.5008@.TK2MSFTNGP05.phx.gbl...
> Please post the stored procedure so that we can better assist you. Without
> being able to see what you are working with, we are just guessing.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "meri2" <meri2@.gmail.com> wrote in message
> news:OLFOQzwqGHA.1796@.TK2MSFTNGP03.phx.gbl...
>> Hi all,
>> I go into SQL QueryAnalyzer for debugging. Expand Stored Procedures for
>> my database, right click the SP I want and select Debug. It prompts me
>> for the parameters and I enter them. It 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.
>> Sql Server is running under a domain user account, not the local system
>> account.
>> Server is SQL Server Enterprise Edition, version 8.00.818 (SP3). Client
>> is 8.00.194, Windows XP, SP2.
>> I've read Sql Server Tools Troubleshooting, but everything is ok.
>> Anyone have an idea why I can't debug?
>>
>

debug

Hi all,
I go into SQL QueryAnalyzer for debugging. Expand Stored Procedures for my
database, right click the SP I want and select Debug. It prompts me for the
parameters and I enter them. It 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.
Sql Server is running under a domain user account, not the local system
account.
Server is SQL Server Enterprise Edition, version 8.00.818 (SP3). Client is
8.00.194, Windows XP, SP2.
I've read Sql Server Tools Troubleshooting, but everything is ok.
Anyone have an idea why I can't debug?Please post the stored procedure so that we can better assist you. Without
being able to see what you are working with, we are just guessing.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"meri2" <meri2@.gmail.com> wrote in message
news:OLFOQzwqGHA.1796@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I go into SQL QueryAnalyzer for debugging. Expand Stored Procedures for my
> database, right click the SP I want and select Debug. It prompts me for
> the parameters and I enter them. It 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.
> Sql Server is running under a domain user account, not the local system
> account.
> Server is SQL Server Enterprise Edition, version 8.00.818 (SP3). Client is
> 8.00.194, Windows XP, SP2.
> I've read Sql Server Tools Troubleshooting, but everything is ok.
> Anyone have an idea why I can't debug?
>
>|||It happens with all the stored procedure.
thanks
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OdmWh1zqGHA.5008@.TK2MSFTNGP05.phx.gbl...
> Please post the stored procedure so that we can better assist you. Without
> being able to see what you are working with, we are just guessing.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "meri2" <meri2@.gmail.com> wrote in message
> news:OLFOQzwqGHA.1796@.TK2MSFTNGP03.phx.gbl...
>

Dear Insert in to database from textbox in asp.net

I have 5 textboxs and 1 botton in web form. I want to click botton and insert all data in textbox to database that using sql server 2005. Please help meYou can use SqlCommand with parameters, as shown in this documentation:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx