sql permissions: break

Showing posts with label break. Show all posts

Saturday, February 25, 2012

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.

Friday, February 24, 2012

Debugging problem with break points not being picked up

I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

I've moved this thread to the SSIS forum. The core VS Debugger team does not cover this area,

John

|||

An excert from Books Online -

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Note: You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note:

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

In addition -

You cannot debug Script tasks in event handlers.

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

|||I know how to debug a script task, it just doesn't. Clicking on Debug instead of close at the error message that's received brings up the Visual Studio Just in time debugger dialog which says:

An unhandled exception ('System.Runtime.InteropServices.COMException') occured in DTAttach.exe [3148]

It then gives me options to continue debugging in a new instance of Microsoft CLR debugger 2005 or a new instance of Visual Studio 2005. Choosing the CLT debugger brings up the dialog box saying:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Additional information: File position not valid for new breakpoint."

But the CLR debugger for DTAttach just reports that "(Disassembly cannot be displayed in run mode.)"

This seems to be a problem that has been around for a while, see this other post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=889400&SiteID=1

I too get the "Microsoft Visual Studio has lost its link to..." message. I have searched and searched, it seems quite a few other people are having the same problem but so far I haven't found a fix yet which is why I posted here.

Please help, is there a fix, can anyone tell me which file break points are stored in so that I can delete it manually as it could be corrupt?

thanks

Hoots.

|||I got a similar error without reference to the 'DTAttach'. Clicked the Debug button (Debug, Send Error Report, Don't Send) and continued from the break point that caused the error. Hope it works because that's all I got.|||Thanks for the response, unfortunately I think it just depends on what it feels like!! I can't get round it cos the whole debug thing just seems to be really flakey and am having to do it the old fashioned way with lots of msgbox commands. Would really appreciate a comment from a MVP or Microsoft itself on this one.

|||

Was having this problem just now. realized I had a breakpoint in a disabled script task. Once I took it out, not only did it stop throwing an error, it stopped on my other breakpoints.

Hope this helps.

|||Non of my script tasks are disabled. I think it's just a problem with the BIDS debugger. I'd love to have that confirmed and to find out when a fix is going to be available though.

|||

Hi there,

The problem you're seeing can be related to the fact that the precompiled script is out of sync with the code. One way to fix it is to remove the breakpoints on that script, regenerate the script binary ( either by changing the script by adding a comment or by flipping the precompiled property on the task off and back on), add the breakpoints back and save the script task and see if it works.

This assumes that you are not running on Windows Vista for which the debugging is currently broken due to an incompatibility between VSA and the CLR installed with the OS. On Vista the problem should be fixed at a later time but only for SQL Server SP2.

Let me know if refreshing the script binary caused the breakpoints to be valid again.

Thanks,

Silviu

|||Thanks for the info, unfortunately it didn't work. My package is made up predominantly of script components and contains a Foreach Loop and an additional Foreach loop within the other Loop, both are variable enumerators.

I went through each script component, took out any break points and put in additional comments. I then put a single break point in one of the scripts and ran it again but it still comes up with the same problem.

thanks

Hoots.
|||But break points are not supported in SSIS 2005... You have to use message boxes instead. This is a well documented "feature."|||Well documented... Really? Could you point me to the document that describes the features that causes BIDS to come up with the exception:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Seriously, I really would like to be able to read something definitive on break points in any script tasks not being supported for SSIS. After a large amount of searching and finding other people with the same problem, this is the first I've heard of them not being supported.

thanks

Hoots.

|||Have you read through this document? I see it claims support for script tasks, but not script components. I was under the assumption that breakpoints inside either script element were not supported.

http://msdn2.microsoft.com/en-us/library/ms140033.aspx|||Sorry, I meant script task rather than script component although I can't even see anything called a script component, what is it? Just as a note, all my script tasks are all set with precompiled as false.

Hoots.

|||Yep, read it now, I seem to be doing everything right, it just doesn't work.