Decoding a Maintenance Plan

How can I "decode" exactly what is done during a scheduled Maintenance Plan
(or its job/step)?
The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID <uuid>
etc.
I tried sp_help_maintenance_plan, but received the error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_help_maintenance_plan'.
I am a member of the sysadmin fixed role.
TIA,
Tore.Tore
In EM go to your maint plan (in the management folder) and
look at the properties. This will show you what it is
doing and you can make changes there.
Regards
John|||Thanks. It isn't there, either.
Another post pointed me to the "Database Maintenance Plans" under the
Management folder in EM. (I'm not blind, just focused... :->).
Tore.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23MUito2WDHA.652@.TK2MSFTNGP10.phx.gbl...
> Hi Tore,
> sp_help_maintenance_plan exists in the msdb database, not in the master
> database, which means that you either have to be in msdb when you run it,
or
> prefix it with msdb.. to run it from any database:
> EXEC msdb..sp_help_maintenance_plan will work.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:OUGPWf2WDHA.212@.TK2MSFTNGP12.phx.gbl...
> > How can I "decode" exactly what is done during a scheduled Maintenance
> Plan
> > (or its job/step)?
> >
> > The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> <uuid>
> > etc.
> >
> > I tried sp_help_maintenance_plan, but received the error
> >
> > Server: Msg 2812, Level 16, State 62, Line 1
> > Could not find stored procedure 'sp_help_maintenance_plan'.
> >
> > I am a member of the sysadmin fixed role.
> >
> > TIA,
> > Tore.
> >
> >
>|||I'm not blind, just focused... :->
Thanks,
Tore.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:8ed101c35b6b$305067f0$a001280a@.phx.gbl...
> Tore
> In EM go to your maint plan (in the management folder) and
> look at the properties. This will show you what it is
> doing and you can make changes there.
> Regards
> John

Debugging?

Hi,
Is there a way to debug an SQL Server stored procedure? I call a number of sp's from my .net app and need a way to step through them.
Thank you,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtskasqldebuggingexample.asp
|||Thanks. Your link also pointed to the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vchowapplicationdebugging.asp
that shows, supposedly, how to debug a stored procedure inline with your app. This is what I'm looking for, because I need to be able to see what the stored procedure is receiving in it's parameters. I followed the steps in the link above, put a breakpoing in the stored procedure and another one in my app just before the stored procedure, and, contrary to the claims of the article, I was not able to step into the procedure. Any help getting this to work would be appreciated!
Thanks.|||Is the SQL Server on your local machine? If not you have toinstall the remote debugging components. This article tells youhow to install and configure remote debugging (as well as various othercool debugging tips):
http://www.dbazine.com/sql/sql-articles/cook1
|||It is on my local machine.

debugging tools

i would like to know if there is a way to 'step through sql server t-sql
commands'
using one of the sql server 200 tools, sql server managment studio, or
viusal studio .net 2005 professtional version? If so can you give me
directions on how to 'step through' (debug) the t-sql commands. if not, what
tool that can be used to debug the t-sql commands?
thanks!
Wendy Elizabeth wrote:

> i would like to know if there is a way to 'step through sql server t-sql
> commands'
> using one of the sql server 200 tools, sql server managment studio, or
> viusal studio .net 2005 professtional version? If so can you give me
> directions on how to 'step through' (debug) the t-sql commands. if not, what
> tool that can be used to debug the t-sql commands?
If nothing else, you could highlight and execute a few lines
at a time, and/or intersperse some PRINT statements.
|||Visual Studio (Pro and higher) has TSQL debugging. Create a project of "Database Project" type
(found in "Other Project Types\Database" folder). Specify the desired data source.
View, Server Explorer.
Double-click a stored procedure.
Set breakpoints.
Right-click the proc and select "Step Into".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in message
news:AE3CB536-778F-47AD-AD64-9CA0B6827073@.microsoft.com...
>i would like to know if there is a way to 'step through sql server t-sql
> commands'
> using one of the sql server 200 tools, sql server managment studio, or
> viusal studio .net 2005 professtional version? If so can you give me
> directions on how to 'step through' (debug) the t-sql commands. if not, what
> tool that can be used to debug the t-sql commands?
> thanks!
|||hi Wendy,
Wendy Elizabeth wrote:
> i would like to know if there is a way to 'step through sql server
> t-sql commands'
> using one of the sql server 200 tools, sql server managment studio, or
> viusal studio .net 2005 professtional version? If so can you give me
> directions on how to 'step through' (debug) the t-sql commands. if
> not, what tool that can be used to debug the t-sql commands?
in addition to Tibor's answer, please have a look at
http://msdn2.microsoft.com/en-us/library/zefbf0t6(VS.80).aspx as well..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||i will try
"Ed Murphy" wrote:

> Wendy Elizabeth wrote:
>
> If nothing else, you could highlight and execute a few lines
> at a time, and/or intersperse some PRINT statements.
>
|||Hi Wendy,
I suggest you try the tool SQL Ultimate Debugger:
http://lakesidesql.com/downloads/PFD/1_0_2007_809/PFDebugger_Setup_08-09-2007.zip.
This tool is a post factum debugger and allows you to trace and
debug obejcts (SP's Triggers, FN's, etc.) that had been executed. It's
waaaay better than the T-SQL debugger in VS. Also, since this software has
not yet been officially realeased, I would not recommend (YET) to use it in
a production environment.
You can learn more about it here:
http://208.75.249.50/products/sql-ultimate-debugger/screenshots.html. If you
want a copy of its documentation. shoot me an email and i can send it to
you.
I hope that helps.
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in
message news:AE3CB536-778F-47AD-AD64-9CA0B6827073@.microsoft.com...
>i would like to know if there is a way to 'step through sql server t-sql
> commands'
> using one of the sql server 200 tools, sql server managment studio, or
> viusal studio .net 2005 professtional version? If so can you give me
> directions on how to 'step through' (debug) the t-sql commands. if not,
> what
> tool that can be used to debug the t-sql commands?
> thanks!

Debugging stored procedures in SQL2005

I am getting following error message while trying to step into stored procedue:

"Unable to start T-SQL Debugging. Could not connect to computer 'xxx'. The referenced account is currently locked out and may not be logged on to"

I use VS 2005 Professional edition. SQL Server Authentication is used to create data connection in Server Explored. SQL/CLR debugging is enabled for the connection. I followed all steps from this article:

Setting Up SQL Debugging http://msdn2.microsoft.com/en-us/library/s4sszxst(VS.80).aspx

Any help?

Thanks

Slava

It sounds like your password has expired. You can change your password using SQL Server management studio, or you can ask a system administrator to do it for you.

debugging stored procedures

Hello,

I am doing a lot of work with stored procedures at work now and am wondering
if there is a way that I can step through the code line by line and set
breakpoints on it like I do in VB/VBA to test variables/parameters.

Regards,

JayneOn Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:

>I am doing a lot of work with stored procedures at work now and am wondering
>if there is a way that I can step through the code line by line and set
>breakpoints on it like I do in VB/VBA to test variables/parameters.

Hi Jayne,

In Query Analyzer, hit F8 to bring up the object browser. Find the
stored procedure, right-click it and select "Debug".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> On Tue, 08 Mar 2005 21:38:46 +0000, Little PussyCat wrote:
>>I am doing a lot of work with stored procedures at work now and am
>>wondering if there is a way that I can step through the code line by
>>line and set breakpoints on it like I do in VB/VBA to test
>>variables/parameters.
> In Query Analyzer, hit F8 to bring up the object browser. Find the
> stored procedure, right-click it and select "Debug".

That's the theory.

In practice it appears that there is always something that prevents it
from working. To start with SQL Server must be running from a domain
account, and not local server. If you have Windows XP SP2 on the client,
you need at least 8.00.944 or the beta of SQL 2000 SP4, and you must
apply it on server and client. On top of that you must open port 135
in Windows firewall for the SQL box. (Do NOT open this port generally.)

And when all is done, you sysadm may get the idea that the Windows users
under which SQL Server runs is not permitted access to the workstations.
This how the last attempt ended in our shop.

I should add if you run SQL Server on your own machine, debugging
usually works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can also step into T-SQL stored procs from the VB or .NET development
environment. But personally, I have not found stepping into stored procs to
be all that useful since I could not see a way to examine the result sets of
queries. I have found using SQL profiler to be extremely useful for seeing
what parameters the calling app is passing in and which statements are
getting executed within the procedure. However, I think you need SA rights
to run profiler. To examine the result sets of queries, I usually just run
the guts of the procedure in Query Analyzer and use select statements to
dump the intermediate results. .

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:ccm1g2-n1c.ln1@.tiger.sphynx...
> Hello,
> I am doing a lot of work with stored procedures at work now and am
wondering
> if there is a way that I can step through the code line by line and set
> breakpoints on it like I do in VB/VBA to test variables/parameters.
> Regards,
> Jayne|||Miss Livvy (XeveryidiwantistakenX@.yahoo.com) writes:
> You can also step into T-SQL stored procs from the VB or .NET
> development environment. But personally, I have not found stepping into
> stored procs to be all that useful since I could not see a way to
> examine the result sets of queries.

Yeah, I agree. Occassionally if a I have procedure with lot of procedural
logic, single-stepping through it can be helpful. The same is true if
want to look at the values of some variables.

But often I find too much hassle to start the debugger, so I rather
modify the procedure with some debug SELECT:s in strategic places.

Also, if your procedure raises an error and you want to debug that
happens after the error, the debugger does not appear to be very
co-operative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 procedures in Visual Studio.net

Hi everyone,

I dont know if I am posting this to the right area but here goes: I know there is a way you can step into a stored procedure from VS.net, but I dind't know how. I searched google and found this MS KB Article:http://support.microsoft.com/kb/316549

However, on step two, "Under the Servers node in Server Explorer, expand the SQL Server Machine name, expand the SQL Servers Node, ..."

The first and only item I have in the tree is my local machine name. If I expand that I do not have a SQL Servers node, all I have are: Crystal Reports Services, Event Logs Management Classes, Management Events, Message Queues, Performance Counters and Services.

The instance of SQL Server I am using is on my local machine and I am able to communicate with it. I am using TCP/IP

I figured to check the SQL Server Config Manager and see what protocols the db was using. The client protocols are Shared Memory, TCP/IP and Named Pipes (these are enabled)

I then looked at the SQL Server 2005 Services. The following are running: SQL Server Integration Services, SQL Server FullText, SQL Server, SQL Server Analysis Services, SQL Server Reporting and SQL Server Agent (This was stopped but I started it) SQL Server Browser is NOT running and I can't start it. I dont know if that has anything to do with it.

Am I missing somethign?

Thanks,

John

Hi John,
Try this:
Adding New Servers in Server Explorer

|||

My Server (the local machine) is listed, SQL Server is just not listed there.

Debug Stored Procedure - Visual Studio 2003 - Possible?

I can not step into stored procedures that I have created using the VS IDE.
This is the error message that I receive when attempting to debug the stored
procedure:
"Cannot debug stored procedures because the SQL Sserver database is not
setup correctly or user does not have permission to execute
master.sp_sdidebug. Run SQL Server setup or contact database admininstrator."
I am the database administrator! I have sp_sdidebug set to legacy_on. Not
sure what else to try. My boss does not want to pay for SQL Server Dev
edition, so I would really like to get this working.
Any suggestions will be greatly appreciated.
Thank you,
Michael Garcia
If your boss doesn't want to shell out $49 for the Developer Edition,
perhaps you could helpfully point out that many experienced people
have spent countless hours attempting to get debugging working from
the VS IDE, and have failed to do so. To obtain actual evidence, do a
google groups search on the topic and read threads from other
frustrated users. Ask him to consider how much he is paying you by the
hour, counting benefits, and then ask him to do the math. Caveat --
you can't use the Developer edition as a production server, but it's
indispensable if you care anything about creating a secure and robust
database app.
--Mary
On Tue, 19 Oct 2004 09:19:03 -0700, "Mike"
<Mike@.discussions.microsoft.com> wrote:

>I can not step into stored procedures that I have created using the VS IDE.
>This is the error message that I receive when attempting to debug the stored
>procedure:
>"Cannot debug stored procedures because the SQL Sserver database is not
>setup correctly or user does not have permission to execute
>master.sp_sdidebug. Run SQL Server setup or contact database admininstrator."
>I am the database administrator! I have sp_sdidebug set to legacy_on. Not
>sure what else to try. My boss does not want to pay for SQL Server Dev
>edition, so I would really like to get this working.
>Any suggestions will be greatly appreciated.
>Thank you,
>Michael Garcia
|||Your point is noted; however, I got it working yesterday.
For all those that are having problems getting debugging working in VS for
MSDE try this!
First find the file mssdi98.dll in C:\Program Files\Microsoft Visual Studio
..NET 2003\sqlserver
Copy it to the binn directory of your MSDE instance.
Second run dcomcnfg.
Component Services -> Computers -> My Computer
Select properties for My Computer. Go to the Default COM Security tab.
Edit the default settings for access permissions. Add yourself and system
if it is not there and make sure allow is checked.
This got it working on both my systems.
Good luck!
Michael Garcia
"Mary Chipman" wrote:

> If your boss doesn't want to shell out $49 for the Developer Edition,
> perhaps you could helpfully point out that many experienced people
> have spent countless hours attempting to get debugging working from
> the VS IDE, and have failed to do so. To obtain actual evidence, do a
> google groups search on the topic and read threads from other
> frustrated users. Ask him to consider how much he is paying you by the
> hour, counting benefits, and then ask him to do the math. Caveat --
> you can't use the Developer edition as a production server, but it's
> indispensable if you care anything about creating a secure and robust
> database app.
> --Mary
|||You're lucky -- others have hit the wall configuring DCOM and dealing
with SP2 issues. Please have your boss get you the Dev edition anyway
-- there are many things you simply cannot do from the VS IDE unless
you want to write a lot of script.
--Mary
On Wed, 20 Oct 2004 09:39:02 -0700, "Mike"
<Mike@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Your point is noted; however, I got it working yesterday.
>For all those that are having problems getting debugging working in VS for
>MSDE try this!
>First find the file mssdi98.dll in C:\Program Files\Microsoft Visual Studio
>.NET 2003\sqlserver
>Copy it to the binn directory of your MSDE instance.
>Second run dcomcnfg.
>Component Services -> Computers -> My Computer
>Select properties for My Computer. Go to the Default COM Security tab.
>Edit the default settings for access permissions. Add yourself and system
>if it is not there and make sure allow is checked.
>This got it working on both my systems.
>Good luck!
>Michael Garcia
>"Mary Chipman" wrote:

Debug SProcs

I have both SQL2005 and Express installed on my local development machine. I
am able to step in / debug stored procedures in dbs on Express just fine.
Stepping into a stored procedure from within Visual Studio 2005 under the
full version of SQL on the local machine pops up the "run stored procedure"
dialog box under which I enter my parameters but then things just seem to
hang, never reaching my breakpoint. When I click on the "Stop Debugging"
button, control is returned to VS.
Any ideas?
The output window contains the following:
Auto-attach to process '[1168] [SQL] webdev1' on machine 'webdev1'
succeeded.
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
Running [dbo].[CartToOrder] ( @.BrowserID = 5).
(Click on the stop debugging button).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The program '[1168] [SQL] webdev1: webdev1' has exited with code 0 (0x0).
Batch execution is terminated because of debugger request.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[CartToOrder].
Andrew RobinsonHello Andrew,
You may want to run the SP directly in SSMS to see if it hangs. Also, you
may try to create a new simple SP on a new database to test the situation.
To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroup. I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.

debug in sql server 2000

Hello guys!
I am trying to debug a sql procedure inside query analyzer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Can you post the procedure you're talking about? And which version and edition are you using?

debug in sql server 2000

Hello guys!
I am trying to debug a sql procedure inside query analyzer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Can you post the procedure you're talking about? And which version and edition are you using?

debug (step into) sql stored proc from managed code

I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?

Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549

Thanks.Please use the word "bump" or something such as, "Any help?" so that the moderators recognise the purpose of your single-character message. Otherwise, the message may be deemed meaningless, and will be deleted.|||Sorry, this is really a Visual Studio.NET question. I have no idea.

Terri

Debug - How to Step into

I have a stored procedure, in the name Proc_UpdateProductionShare. This
procedure has got a select statement and a couple of update statements. I
wish to debug this stored procedure. By debug i mean that i wish to step
into the procedure code line by line and debug it. I also have used some
local variable and setting the values to these variables based on the
actions in the procedure. I also need to trace the value of these local
variables.
For this i had used the T-SQL Debugger. In Query Analyzer i had opened the
Object Browser. In the object browser i had selected the database in which
the stored procedure resides. Then i had selected the particular stored
procedure and right clicked on the stored procedure and selected the "Debug"
option. A small dialog window opened mentioning the stored procedure i had
selected and also listing the parameters for this stored procedure, it's
data type, type of the parameter (input or output) and the value text field.
Since in this particular procedure i had a input parameter (int datatype) to
be passed on, it was listed. I specified the value for this parameter and
then clicked "Execute" button.
This opened the source code of the procedure, in a new pane at the top. But
when press "F5" or click the "Go" button in this pane's toolbar, the
procedure executes immediately and completes execution. Iam not able to step
into each line of the stored procedure and execute it one by one and
checking the result of the execution of each and every line. Then i also
tried by setting breakpoints at 2, 3 lines of the code and then clicked the
"Go" button. But this time too the procedure completed execution without
stopping execution at the breakpoints.
All i need is to use the "F11 (step into)" and "F10 (step out)" buttons to
check the execution. These buttons were enabled only for a fraction of
second after the "Go" button is clicked. But before i start using them, the
procedure execution completes and these buttons get disabled. How do i do
this task '
I searched for help in BOL. But i was not able to find any useful info on
this part. Can you tell me the steps to be followed to debug a stored
procedure or mention some links where, i can get info regarding this.
Thanks in Advance.
With Regard,
SQL UserThere's a troubleshooting section about the debugger in Books Online. Did
you find it? Also, make sure you have the updated Books Online:
http://www.microsoft.com/SQL/techinfo/productdoc/2000/default.asp
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MS User" <sqlman@.sql.com> wrote in message
news:eKeMw%23KpDHA.2776@.tk2msftngp13.phx.gbl...
> I have a stored procedure, in the name Proc_UpdateProductionShare. This
> procedure has got a select statement and a couple of update statements. I
> wish to debug this stored procedure. By debug i mean that i wish to step
> into the procedure code line by line and debug it. I also have used some
> local variable and setting the values to these variables based on the
> actions in the procedure. I also need to trace the value of these local
> variables.
> For this i had used the T-SQL Debugger. In Query Analyzer i had opened the
> Object Browser. In the object browser i had selected the database in which
> the stored procedure resides. Then i had selected the particular stored
> procedure and right clicked on the stored procedure and selected the
"Debug"
> option. A small dialog window opened mentioning the stored procedure i had
> selected and also listing the parameters for this stored procedure, it's
> data type, type of the parameter (input or output) and the value text
field.
> Since in this particular procedure i had a input parameter (int datatype)
to
> be passed on, it was listed. I specified the value for this parameter and
> then clicked "Execute" button.
> This opened the source code of the procedure, in a new pane at the top.
But
> when press "F5" or click the "Go" button in this pane's toolbar, the
> procedure executes immediately and completes execution. Iam not able to
step
> into each line of the stored procedure and execute it one by one and
> checking the result of the execution of each and every line. Then i also
> tried by setting breakpoints at 2, 3 lines of the code and then clicked
the
> "Go" button. But this time too the procedure completed execution without
> stopping execution at the breakpoints.
> All i need is to use the "F11 (step into)" and "F10 (step out)" buttons to
> check the execution. These buttons were enabled only for a fraction of
> second after the "Go" button is clicked. But before i start using them,
the
> procedure execution completes and these buttons get disabled. How do i do
> this task '
> I searched for help in BOL. But i was not able to find any useful info on
> this part. Can you tell me the steps to be followed to debug a stored
> procedure or mention some links where, i can get info regarding this.
> Thanks in Advance.
> With Regard,
> SQL User
>|||It's already going when that last window opens
Note the yellow arrow is present.
So just hit StepOver to run just the first line.
Bye,
Delbert Glass|||Thanks for pointing to the right direction.
It was permission to execute the SP_SDIDEBUG extended procedure AND
It is disabled for security reasons. To enable Transact-SQL Debugger for
legacy clients, a member of the sysadmins server role, such as "sa," must
explicitly enable debugging by running the following code:
Exec sp_sdidebug 'legacy_on'
You must repeat this procedure whenever you restart the server. It is not
recommended to use Transact-SQL Debugger on a production server.
To disable Transact-SQL Debugger for legacy clients after you enable it, as
a member of the sysadmins server role, you can run the following code:
Exec sp_sdidebug 'legacy_off'
HTH
Shamim
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23q2yjVLpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> There's a troubleshooting section about the debugger in Books Online. Did
> you find it? Also, make sure you have the updated Books Online:
> http://www.microsoft.com/SQL/techinfo/productdoc/2000/default.asp
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:eKeMw%23KpDHA.2776@.tk2msftngp13.phx.gbl...
> > I have a stored procedure, in the name Proc_UpdateProductionShare. This
> > procedure has got a select statement and a couple of update statements.
I
> > wish to debug this stored procedure. By debug i mean that i wish to step
> > into the procedure code line by line and debug it. I also have used some
> > local variable and setting the values to these variables based on the
> > actions in the procedure. I also need to trace the value of these local
> > variables.
> >
> > For this i had used the T-SQL Debugger. In Query Analyzer i had opened
the
> > Object Browser. In the object browser i had selected the database in
which
> > the stored procedure resides. Then i had selected the particular stored
> > procedure and right clicked on the stored procedure and selected the
> "Debug"
> > option. A small dialog window opened mentioning the stored procedure i
had
> > selected and also listing the parameters for this stored procedure, it's
> > data type, type of the parameter (input or output) and the value text
> field.
> > Since in this particular procedure i had a input parameter (int
datatype)
> to
> > be passed on, it was listed. I specified the value for this parameter
and
> > then clicked "Execute" button.
> >
> > This opened the source code of the procedure, in a new pane at the top.
> But
> > when press "F5" or click the "Go" button in this pane's toolbar, the
> > procedure executes immediately and completes execution. Iam not able to
> step
> > into each line of the stored procedure and execute it one by one and
> > checking the result of the execution of each and every line. Then i also
> > tried by setting breakpoints at 2, 3 lines of the code and then clicked
> the
> > "Go" button. But this time too the procedure completed execution without
> > stopping execution at the breakpoints.
> >
> > All i need is to use the "F11 (step into)" and "F10 (step out)" buttons
to
> > check the execution. These buttons were enabled only for a fraction of
> > second after the "Go" button is clicked. But before i start using them,
> the
> > procedure execution completes and these buttons get disabled. How do i
do
> > this task '
> >
> > I searched for help in BOL. But i was not able to find any useful info
on
> > this part. Can you tell me the steps to be followed to debug a stored
> > procedure or mention some links where, i can get info regarding this.
> >
> > Thanks in Advance.
> >
> > With Regard,
> > SQL User
> >
> >
>

debug

Hello guys!
I am trying to debug a sql procedure inside Server Explorer but I can seem to step into each line.
When I run the procedure it doesn't stop in the breakpoint I set.

By the way I am running in a client pc..

Please help guys!

Is this a T-SQL proc, or a SQLCLR proc?

If it is a T-SQL proc, make sure you ahve opened the proc in VS before you step into it.

If it is a SQLCLR proc there are a couple of things to check:
1. Have you enabled SQLCLR debugging on the connection: Server explorer, right click the connection you use, Allow SQLCLR debugging
2. Have the debug symbols been uploaded to the database. If you use VS SQL Server Project to deploy the assemblies it happens automatically. If you deplou manually (CREATE ASSEMBLY ...) you have to do it yourself: ALTER ASSEMBLY name ADD FILE FROM path_to_pdb_file

Niels|||

Thanks for your reply nielsb!
It is a T-SQL proc. I have opened the procedure inside the VS Server explorer but still I
can't step into the proc.It ends right away.I have also tried doing it in the query analyzer
but still the same result.

Please help.

|||Hmm - ok so below is a proc script. Run this script in a database from SQL Server Management Studio

create procedure testDbg @.x int
as
declare @.y int
set @.y = 9
set @.x = @.x + @.y
select @.x

After you have run the script:
1. Open VS
2. Open Server Explorer
3. Create a connection against the database where you created the proc (if you already have a connection delete it and recreate).
4. Drill down to Stored Procedures and open the proc
5. Set a breakpoint at: set@.y = 9
6. In server explorer right click on the proc name and choose Step Into Procedure
7. You should now see a dialog, where you can set parameter values. Change the <default> to a value
8. Click OK. You should now hit the break point. If you don't check the Output Window and see if you have any error messages.

Niels|||

Hi!

First of all I don't have SQL Server Management Studio installed in my pc.

Anyway, I run the procedure in the query analyzer. And I opened VS.

I set a breakpoint and I click step into. When I entered the value and click ok nothing happens.(The breakpoint has a question mark inside it.)

There is no error inside the output window.When I point my mouse in the breakpoint I
set it just says that the breakpoint set cannot be read.And i hit F8 or F5 and still nothing happens.

I don't know why this happens..:( Please help.

|||I'm sorry, but I have no clue why it doesn't work.

What version of VS are you running, VS Express, Standard, Pro ... (I assume you are running VS 2005)?

Niels|||

Sorry to hear that...:(

I'm using VS .net 2003 Framework 1.1 though....

Could it be a permission setting or something in the server side?

|||

Are you trying to debug a stored procedure in SQL Server 2005 using VS 2003? That is not possible.

Thanks,

-Vineet.