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.
Showing posts with label debug. Show all posts
Showing posts with label debug. 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.
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 while running reporting services
I am attempting to debug a customer security dll that is called by Reporting Services. The DLL works great and functions correctly. I am trying to set breakpoints in the code, but they are ignored. I changed the web.config for ReportManager and ReportServer to set debug=true. However, still no luck. The application never stops, it just works.
I have tried this on a Windows XP Pro with no luck. Any suggestions?
Are you debugging the asp.net worker process? Did you configure VS to point to the correct symbols?
-Daniel
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...
>
>
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...
>
>
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
> >> >
> >> >
> >> >
>
>
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 TSQL Stored Procedures
Running SQL Server Express is there a way to debug TSQL stored procedures?I also have Visual Studio .NET 2003, can I use it to debug the TSQL stored procedures?
Thanks in advance,
Mark
No you need VS 2005 to debug SQL 2005.|||T-SQL Stored Procedure in 2005 can only be done with VSS 2005? Aargh... I agree, this is a step backawards. Looking at my machien, I see
our company base install has included VSS, but it seems only for
Integration Services, Analysis Services and Reporting Services.
What do I need to do to get it to include the SQL 'component' into VSS?|||
I think you mis-read Euan's post, he said you need VS 2005, as in Visual Studio. Only one "S"; different that VSS for Visual Source Safe. Different thing altogether.
Mike
|||I know ... you under-estimate the power of the typo and the rushing poster :-)
I was rushing the post, and didn't check what I was typing too clearly - a bad habit! I do know (and have used) Visual Source Safe ... and do have VS 2005 installed.
Now I just need to understand what I need to do to get it to open the SQL solution I have created - when I try to open projects, the extensions listed cover Integration Services, etc. etc. but not SQL project.
Thanks for trying to help me back onto the path - and again, sorry for the mistaken acronyms...
|||If you only have the BI Projects then you just have the BI version of VS, you need the Pro SKU or higher for SQL Projects.|||Euan Garden wrote:
If you only have the BI Projects then you just have the BI version of VS, you need the Pro SKU or higher for SQL
Projects.
Thanks Euan. Going to arrange that now.
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
>
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 triggers, how?
I could find that its possible to debug stored procedures in SQl server
2000, but did not find any place in BOL where it was explained if it was
even possible to debug (step through and verify values obtained as well as
detect programming errors other than syntax errors) triggers. Is it
possible, if so where do I find Info on how?
Thanks fir any help,
BobBob,
I believe the Transact-SQL Debugger does not work directly with triggers.
However, I believe I read somewhere that it will work if the stored
procedure is being debugged and it fires the trigger.
Other possible options may include embedding additional t-shooting code in
the trigger i.e., RAISERROR or SELECT (INSERTED/DELETED).
HTH
Jerry
"Bob" <bdufour@.sgiims.com> wrote in message
news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
> Thanks fir any help,
> Bob
>|||On Fri, 14 Oct 2005 17:13:09 -0400, Bob wrote:
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
>Thanks fir any help,
>Bob
>
Hi Bob,
Jerry is right:
1. Create a procedure that performs an insert, update or delete that
would fire the trigger.
2. Debug the stored procedure
3. Step into the trigger.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Jerry is correct.
You can't debug triggers directly but if you are debugging a stored proc and
a trigger is fired, then it will step in debug mode inside the trigger.
Yosh
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eURZtZQ0FHA.664@.tk2msftngp13.phx.gbl...
> Bob,
> I believe the Transact-SQL Debugger does not work directly with triggers.
> However, I believe I read somewhere that it will work if the stored
> procedure is being debugged and it fires the trigger.
> Other possible options may include embedding additional t-shooting code in
> the trigger i.e., RAISERROR or SELECT (INSERTED/DELETED).
> HTH
> Jerry
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>|||Excellent suggestion Hugo!
This would work perfectly.
Yosh
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qnc0l1d6bke8sv0pcpl9feb1qbkmbjtumr@.
4ax.com...
> On Fri, 14 Oct 2005 17:13:09 -0400, Bob wrote:
>
> Hi Bob,
> Jerry is right:
> 1. Create a procedure that performs an insert, update or delete that
> would fire the trigger.
> 2. Debug the stored procedure
> 3. Step into the trigger.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you all,
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
> Thanks fir any help,
> Bob
>
2000, but did not find any place in BOL where it was explained if it was
even possible to debug (step through and verify values obtained as well as
detect programming errors other than syntax errors) triggers. Is it
possible, if so where do I find Info on how?
Thanks fir any help,
BobBob,
I believe the Transact-SQL Debugger does not work directly with triggers.
However, I believe I read somewhere that it will work if the stored
procedure is being debugged and it fires the trigger.
Other possible options may include embedding additional t-shooting code in
the trigger i.e., RAISERROR or SELECT (INSERTED/DELETED).
HTH
Jerry
"Bob" <bdufour@.sgiims.com> wrote in message
news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
> Thanks fir any help,
> Bob
>|||On Fri, 14 Oct 2005 17:13:09 -0400, Bob wrote:
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
>Thanks fir any help,
>Bob
>
Hi Bob,
Jerry is right:
1. Create a procedure that performs an insert, update or delete that
would fire the trigger.
2. Debug the stored procedure
3. Step into the trigger.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Jerry is correct.
You can't debug triggers directly but if you are debugging a stored proc and
a trigger is fired, then it will step in debug mode inside the trigger.
Yosh
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eURZtZQ0FHA.664@.tk2msftngp13.phx.gbl...
> Bob,
> I believe the Transact-SQL Debugger does not work directly with triggers.
> However, I believe I read somewhere that it will work if the stored
> procedure is being debugged and it fires the trigger.
> Other possible options may include embedding additional t-shooting code in
> the trigger i.e., RAISERROR or SELECT (INSERTED/DELETED).
> HTH
> Jerry
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>|||Excellent suggestion Hugo!
This would work perfectly.
Yosh
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qnc0l1d6bke8sv0pcpl9feb1qbkmbjtumr@.
4ax.com...
> On Fri, 14 Oct 2005 17:13:09 -0400, Bob wrote:
>
> Hi Bob,
> Jerry is right:
> 1. Create a procedure that performs an insert, update or delete that
> would fire the trigger.
> 2. Debug the stored procedure
> 3. Step into the trigger.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you all,
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:OSET%23QQ0FHA.460@.TK2MSFTNGP15.phx.gbl...
>I could find that its possible to debug stored procedures in SQl server
>2000, but did not find any place in BOL where it was explained if it was
>even possible to debug (step through and verify values obtained as well as
>detect programming errors other than syntax errors) triggers. Is it
>possible, if so where do I find Info on how?
> Thanks fir any help,
> Bob
>
Debugging triggers on views in SQL Server 2005?
I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.
Debugging triggers on views in SQL Server 2005?
I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>
Debugging Triggers
I was wondering if it is possible to pop up a dialog box from a SQL trigger.
This would be a great way to debug triggers as you could report variable values in the dialog box.
If not, does anybody have any suggestions as to the best way of reporting out variable values whilst a trigger is running?Sometimes I will feed values and sql strings into a table, sometimes I will use profiler with all of the XXXcompleted events turned on for th Transact-SQL and stored prpcedures classes.|||Install the stored procedure debugging package from the MS-SQL CD? I haven't used it in a while, but it lets you set breakpoints, watches, etc. very much like Visual Studio.
-PatP
This would be a great way to debug triggers as you could report variable values in the dialog box.
If not, does anybody have any suggestions as to the best way of reporting out variable values whilst a trigger is running?Sometimes I will feed values and sql strings into a table, sometimes I will use profiler with all of the XXXcompleted events turned on for th Transact-SQL and stored prpcedures classes.|||Install the stored procedure debugging package from the MS-SQL CD? I haven't used it in a while, but it lets you set breakpoints, watches, etc. very much like Visual Studio.
-PatP
Debugging tool for SP
After writing a Stored Procedure(which is giving correct reasult) how can I go for optimizing it?Is there any tool to debug a stored procedure?You should optimize the sql statements _before_ creating the sp.
Debugging temporary SPs?
Sorry for cross-post, not really sure which group is best...
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
>> Hi
>> Can you post the script
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
>> well I am getting confused here, do you generate the procedure code
>> dynamically using a string variable, because the error message
>> Incorrect syntax near the keyword 'SET'
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>|||>> Incorrect syntax near the keyword 'SET'
By removing the SP and putting the statement directly in code, the SQL
that ODBC/SQLServer generates is definitely wrong.
Here is what I send to prepare then try to execute:
Select Distinct (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like ? and LOCATION_LEVEL_2 LIKE ? and
LOCATION_LEVEL_3 = '' and LOCATION_LEVEL_2 <> ''
and here is what ODBC/SQLServer generates (my ***emphasis***):
declare @.P1 int
set @.P1=NULL
declare @.P2 char(6)
set @.P2=' '
exec sp_prepexec @.P1 output, N'@.P1 char(6),@.P2 char(6) OUTPUT', N'Select
Distinct (LOCATION_LEVEL_2 + '' - '' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like @.P1 and LOCATION_LEVEL_2 LIKE @.P2
***OUTPUT*** and LOCATION_LEVEL_3 = '''' and LOCATION_LEVEL_2 <> ''''
Why it adds the 'OUTPUT' part to the middle of the 'where' clause seems
hard to fathom. I am looking into how they are bound...but it any case,
the ODBC/SQLServer interface is producing the wrong results.
Thanks for the help & suggestions...if you have any more thoughts, they
are always welcome.
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
>> Hi
>> Can you post the script
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
>> well I am getting confused here, do you generate the procedure code
>> dynamically using a string variable, because the error message
>> Incorrect syntax near the keyword 'SET'
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>|||>> Incorrect syntax near the keyword 'SET'
By removing the SP and putting the statement directly in code, the SQL
that ODBC/SQLServer generates is definitely wrong.
Here is what I send to prepare then try to execute:
Select Distinct (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like ? and LOCATION_LEVEL_2 LIKE ? and
LOCATION_LEVEL_3 = '' and LOCATION_LEVEL_2 <> ''
and here is what ODBC/SQLServer generates (my ***emphasis***):
declare @.P1 int
set @.P1=NULL
declare @.P2 char(6)
set @.P2=' '
exec sp_prepexec @.P1 output, N'@.P1 char(6),@.P2 char(6) OUTPUT', N'Select
Distinct (LOCATION_LEVEL_2 + '' - '' + LOCATION_DESC) as
LOCATION_2_INFO, LOCATION_LEVEL_2, LOCATION_LEVEL_1 From LOCATION_VW
Where LOCATION_LEVEL_1 Like @.P1 and LOCATION_LEVEL_2 LIKE @.P2
***OUTPUT*** and LOCATION_LEVEL_3 = '''' and LOCATION_LEVEL_2 <> ''''
Why it adds the 'OUTPUT' part to the middle of the 'where' clause seems
hard to fathom. I am looking into how they are bound...but it any case,
the ODBC/SQLServer interface is producing the wrong results.
Thanks for the help & suggestions...if you have any more thoughts, they
are always welcome.
>> making me the thing you creating the code dynamically
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>
Debugging temporary SPs?
Sorry for cross-post, not really sure which group is best...
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>
Is there any way to debug and or see the temporary SPs generated by
SQL/Server?
I'm seeing many problems that can only relate to these SPs since, eg,
preparing and executing:
Call somefunc(?,?)
results in
Incorrect syntax near the keyword 'SET'
and since 'somefunc' does not include the keyword SET, Im guessing it's
SQL/Server that is the culprit.Hi
Can you post the script
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:ILc4i.9138$tp3.37866@.nasal.pacific.net.au...
> Sorry for cross-post, not really sure which group is best...
> Is there any way to debug and or see the temporary SPs generated by
> SQL/Server?
> I'm seeing many problems that can only relate to these SPs since, eg,
> preparing and executing:
> Call somefunc(?,?)
> results in
> Incorrect syntax near the keyword 'SET'
> and since 'somefunc' does not include the keyword SET, Im guessing it's
> SQL/Server that is the culprit.|||vt wrote:
> Hi
> Can you post the script
>
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
And the call is:
{Call LK_LocLvl2(?,?)}
where each parameter is bound as an INPUT parameter.|||What I *think* I really need to know is: can I see the SP that
SQL/Server generates? I think Im seeing a lot of weird behaviour because
of these...and Id like to be sure one wqay or the other.|||Hi
well I am getting confused here, do you generate the procedure code
dynamically using a string variable, because the error message
Incorrect syntax near the keyword 'SET'
making me the thing you creating the code dynamically
if that the case then the error might be in where part
let me know if this correct
to find out what sqserver is doing use sql profiler
Procedure is:
Create Procedure LK_LocLvl2(
@.ll1 Varchar(12),
@.ll2 Varchar(12))
as
Begin
Select Distinct
(LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
LOCATION_LEVEL_2,
LOCATION_LEVEL_1
From LOCATION_VW -- This is a view
Where
LOCATION_LEVEL_1 Like @.ll1+'%'
and LOCATION_LEVEL_2 Like @.ll2+'%'
and LOCATION_LEVEL_3 = ''
and LOCATION_LEVEL_2 <> ''
;
End;
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:465170EE.8000801@.rhyme.com.au...
> vt wrote:
> Procedure is:
> Create Procedure LK_LocLvl2(
> @.ll1 Varchar(12),
> @.ll2 Varchar(12))
> as
> Begin
> Select Distinct
> (LOCATION_LEVEL_2 + ' - ' + LOCATION_DESC) as LOCATION_2_INFO,
> LOCATION_LEVEL_2,
> LOCATION_LEVEL_1
> From LOCATION_VW -- This is a view
> Where
> LOCATION_LEVEL_1 Like @.ll1+'%'
> and LOCATION_LEVEL_2 Like @.ll2+'%'
> and LOCATION_LEVEL_3 = ''
> and LOCATION_LEVEL_2 <> ''
> ;
> End;
> And the call is:
> {Call LK_LocLvl2(?,?)}
> where each parameter is bound as an INPUT parameter.
>|||vt wrote:
> well I am getting confused here, do you generate the procedure code
> dynamically using a string variable, because the error message
> Incorrect syntax near the keyword 'SET'
> making me the thing you creating the code dynamically
No; I am using ODBC and *it* (combined with SQL/Server) does the
automatic creation (according to the docs). It used to be possible to
turn that "feature" off, but as of 2000, it always creates temporary
SPs. And, as far as I can see, sometimes creates buggy ones (or at least
ones that highlight bugs).
The SP I am calling is permanent, and the code I prepare and execute via
ODBC is as I stated in the prior messages.
What I need is access to what ODBC/SQLServer are doing behind the
scenes. The ODBC log does not show the SQLServer side of things...
The profiler may shed some light, but so far it has not shown me any
temp SPs being created...so I guess Ill keep playing.
Thanks for the help, any further insights would be appreciated.|||Hi
In SQL profiler
Errors and Warning->Exception
Objects->objects@.created
stored procedures-> sp:starting, sp:stmtstarting
TSQL->sql:stmtstarting and sql:batchstarting
Hope this might help you
I still think the problem is at the WHERE clause,
execute the sp in query analyser like
exec LK_LocLvl2 xx,xxx
where xx and xxx with valid parameter and let see what happens
regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Philip Warner" <pjw@.rhyme.com.au> wrote in message
news:46518BE2.4010807@.rhyme.com.au...
> vt wrote:
> No; I am using ODBC and *it* (combined with SQL/Server) does the
> automatic creation (according to the docs). It used to be possible to
> turn that "feature" off, but as of 2000, it always creates temporary
> SPs. And, as far as I can see, sometimes creates buggy ones (or at least
> ones that highlight bugs).
> The SP I am calling is permanent, and the code I prepare and execute via
> ODBC is as I stated in the prior messages.
> What I need is access to what ODBC/SQLServer are doing behind the
> scenes. The ODBC log does not show the SQLServer side of things...
> The profiler may shed some light, but so far it has not shown me any
> temp SPs being created...so I guess Ill keep playing.
> Thanks for the help, any further insights would be appreciated.
>
Debugging Stored Procedures with Visual Studio.net has stopped working
Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed.
I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated.
I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.
I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated.
I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.
Thanks in advance
LeeIf any-one else has the same problem, we've solved it. Here's how and why it happened.
We installed Service pack 3 of SQL server and overlooked some of the release notes. This installation was the culprit. To get debugging back working again we needed to execute the statement.
sp_sdidebug legacy_on
via Query Analyzer on our sql server.
Cheers
Lee|||Hi, i try to debug a store procedure in SQL Server. I do an example.
1.Open Server Explorer.
2.Under the Servers node in Server Explorer, expand the SQL Server machine name, expand the SQL Servers node, expand the SQL Server instance, expand the Northwind database node, and then expand the stored procedures node.
3.Right-click the CustOrderHist stored procedure and then click Step Into Stored Procedure.
4.The Run stored procedure dialog box opens, which lists the parameters of the stored procedure. Type ALFKI as the value for the @.CustomerID input parameter and then click OK.
5.In the Visual Studio design environment, a window opens that displays the text of the stored procedure. The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion.
6.In the Output window, the following message is displayed, which indicates successful execution:
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0)
But in step 5, the first executable line of the stored procedure is not highlighted, so I can't press F11 to step through.
Please help me
Debugging stored procedures in SQL 2000
Please can someone tell me how to get this working. It seems impossible.
I just want to debug my stored procedures remotely, from a client PC.
Surely I don't have to physically sit at the server to debug the stored
procedures?
Owen
http://www.binarybaby.co.uk :: home-made electronic musicCan't you term in?
At our office we use terminal services or remote desktop - of course it
depends on your network setup and permissions. You have to set up the
server to allow such connections as well.
Perhaps you could clarify what your specific situation is network wise.
I just want to debug my stored procedures remotely, from a client PC.
Surely I don't have to physically sit at the server to debug the stored
procedures?
Owen
http://www.binarybaby.co.uk :: home-made electronic musicCan't you term in?
At our office we use terminal services or remote desktop - of course it
depends on your network setup and permissions. You have to set up the
server to allow such connections as well.
Perhaps you could clarify what your specific situation is network wise.
debugging stored procedures
I have a user that is db_owner for the a database in development on a SQL 7
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?I hope you have DB_DDLAdmin permissions on the database,
Just add your user account in the master database and
grant em Execute permissions to SP_SDIDEBUG system
procedure
HTH
Saleem Hakani
>--Original Message--
>I have a user that is db_owner for the a database in
development on a SQL 7
>SP 4 server. The user is attempting to debug a stored
procedure and getting
>the error:
>Server: Msg 229, Level 14, State 5, Procedure
sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE
permission denied on
>object 'sp_sdidebug', database 'master', owner 'dbo'.
>I saw this error listed on the support site at
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;328173&Product=sql2k
>.. The site says:
>This behavior is a design change in SQL Server 2000 SP3
to enhance security.
>This design change includes the following changes:
> a.. A database users can only step into stored
procedures that they own.
> b.. A database owner (DBO) can debug any stored
procedure in the database
>that the DBO owns. (A DBO owns the database and,
therefore, all its stored
>procedures.)
> c.. Members of the SysAdmin server role can debug any
stored procedure in
>any database on the server. (A member of the SysAdmin
server role owns the
>server and, therefore, all its databases.)
>For more information about Transact-SQL Debugging, see
the "Using
>Transact-SQL Debugger" and "Troubleshooting the Transact-
SQL Debugger"
>topics in SQL Server Books Online.
>Does the user need to be the dbo ( creator) of the
database to get the
>debugger to work and not just a member of db_owner role
(this does not
>appear to work)? Is there a work around so that my user
can debug his
>stored procedures without me having to debug every stored
procedure for the
>several development servers in house?
>
>.
>|||Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> > I have a user that is db_owner for the a database in development on a
SQL
> 7
> > SP 4 server. The user is attempting to debug a stored procedure and
> getting
> > the error:
> >
> > Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
> on
> > object 'sp_sdidebug', database 'master', owner 'dbo'.
> >
> > I saw this error listed on the support site at
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> > . The site says:
> >
> > This behavior is a design change in SQL Server 2000 SP3 to enhance
> security.
> > This design change includes the following changes:
> > a.. A database users can only step into stored procedures that they
own.
> > b.. A database owner (DBO) can debug any stored procedure in the
> database
> > that the DBO owns. (A DBO owns the database and, therefore, all its
stored
> > procedures.)
> > c.. Members of the SysAdmin server role can debug any stored procedure
> in
> > any database on the server. (A member of the SysAdmin server role owns
the
> > server and, therefore, all its databases.)
> > For more information about Transact-SQL Debugging, see the "Using
> > Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> > topics in SQL Server Books Online.
> >
> > Does the user need to be the dbo ( creator) of the database to get the
> > debugger to work and not just a member of db_owner role (this does not
> > appear to work)? Is there a work around so that my user can debug his
> > stored procedures without me having to debug every stored procedure for
> the
> > several development servers in house?
> >
> >
> >
>
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?I hope you have DB_DDLAdmin permissions on the database,
Just add your user account in the master database and
grant em Execute permissions to SP_SDIDEBUG system
procedure
HTH
Saleem Hakani
>--Original Message--
>I have a user that is db_owner for the a database in
development on a SQL 7
>SP 4 server. The user is attempting to debug a stored
procedure and getting
>the error:
>Server: Msg 229, Level 14, State 5, Procedure
sp_sdidebug, Line 1
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE
permission denied on
>object 'sp_sdidebug', database 'master', owner 'dbo'.
>I saw this error listed on the support site at
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;328173&Product=sql2k
>.. The site says:
>This behavior is a design change in SQL Server 2000 SP3
to enhance security.
>This design change includes the following changes:
> a.. A database users can only step into stored
procedures that they own.
> b.. A database owner (DBO) can debug any stored
procedure in the database
>that the DBO owns. (A DBO owns the database and,
therefore, all its stored
>procedures.)
> c.. Members of the SysAdmin server role can debug any
stored procedure in
>any database on the server. (A member of the SysAdmin
server role owns the
>server and, therefore, all its databases.)
>For more information about Transact-SQL Debugging, see
the "Using
>Transact-SQL Debugger" and "Troubleshooting the Transact-
SQL Debugger"
>topics in SQL Server Books Online.
>Does the user need to be the dbo ( creator) of the
database to get the
>debugger to work and not just a member of db_owner role
(this does not
>appear to work)? Is there a work around so that my user
can debug his
>stored procedures without me having to debug every stored
procedure for the
>several development servers in house?
>
>.
>|||Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> > I have a user that is db_owner for the a database in development on a
SQL
> 7
> > SP 4 server. The user is attempting to debug a stored procedure and
> getting
> > the error:
> >
> > Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
> on
> > object 'sp_sdidebug', database 'master', owner 'dbo'.
> >
> > I saw this error listed on the support site at
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328173&Product=sql2k
> > . The site says:
> >
> > This behavior is a design change in SQL Server 2000 SP3 to enhance
> security.
> > This design change includes the following changes:
> > a.. A database users can only step into stored procedures that they
own.
> > b.. A database owner (DBO) can debug any stored procedure in the
> database
> > that the DBO owns. (A DBO owns the database and, therefore, all its
stored
> > procedures.)
> > c.. Members of the SysAdmin server role can debug any stored procedure
> in
> > any database on the server. (A member of the SysAdmin server role owns
the
> > server and, therefore, all its databases.)
> > For more information about Transact-SQL Debugging, see the "Using
> > Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> > topics in SQL Server Books Online.
> >
> > Does the user need to be the dbo ( creator) of the database to get the
> > debugger to work and not just a member of db_owner role (this does not
> > appear to work)? Is there a work around so that my user can debug his
> > stored procedures without me having to debug every stored procedure for
> the
> > several development servers in house?
> >
> >
> >
>
Debugging Stored Procedures
Hello,
I've recently upgraded to SQL2005 standard edition and
I'm struggling to find how to debug stored procedures.
I fire up the accompanying Visual Studio and goog-ahem
MSN search suggests that I need to start up a Database
Project. This is missing in my VS. What do I need to do to
get this template? Or is there another mechanism similar to
SQL2k's debug functionality?
Thanks,
John
Tibor Karaszi wrote:
> I believe you need a certain edition of VS, possibly Professional.
Hmm this is what I feared... how frustrating.
Thanks Tibor!
John
|||John Nolan wrote:
> Tibor Karaszi wrote:
>
> Hmm this is what I feared... how frustrating.
> Thanks Tibor!
> John
Does the Developer edition of SQL Server 2005 come with a VS
that includes the Database template?
I don't want to buy VS out right as its not my development
platform.
|||Tibor Karaszi wrote:
> I'm pretty certain that it doesn't. The only project types you get
> from the SQL Server installation are "Business Intelligence", things
> like Reporting Services, SSIS and Analysis Server. I can't say 100%
> as I do have "real" VS installed, but, again, I'm pretty certain of
> it...
Just to confirm that I've installed SQL Server 2005
Developer Edition and the Visual studio that comes with it
does not include the Database template.
It seems that in their wisdom Microsoft have deemed that
debugging database stored procedures out of the scope of
database developer.
I've recently upgraded to SQL2005 standard edition and
I'm struggling to find how to debug stored procedures.
I fire up the accompanying Visual Studio and goog-ahem
MSN search suggests that I need to start up a Database
Project. This is missing in my VS. What do I need to do to
get this template? Or is there another mechanism similar to
SQL2k's debug functionality?
Thanks,
John
Tibor Karaszi wrote:
> I believe you need a certain edition of VS, possibly Professional.
Hmm this is what I feared... how frustrating.
Thanks Tibor!
John
|||John Nolan wrote:
> Tibor Karaszi wrote:
>
> Hmm this is what I feared... how frustrating.
> Thanks Tibor!
> John
Does the Developer edition of SQL Server 2005 come with a VS
that includes the Database template?
I don't want to buy VS out right as its not my development
platform.
|||Tibor Karaszi wrote:
> I'm pretty certain that it doesn't. The only project types you get
> from the SQL Server installation are "Business Intelligence", things
> like Reporting Services, SSIS and Analysis Server. I can't say 100%
> as I do have "real" VS installed, but, again, I'm pretty certain of
> it...
Just to confirm that I've installed SQL Server 2005
Developer Edition and the Visual studio that comes with it
does not include the Database template.
It seems that in their wisdom Microsoft have deemed that
debugging database stored procedures out of the scope of
database developer.
debugging stored procedures
I have a user that is db_owner for the a database in development on a SQL 7
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/defaul...3&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/defaul...3&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
SQL
> 7
> getting
> on
>
http://support.microsoft.com/defaul...3&Product=sql2k
> security.
own.
> database
stored
> in
the
> the
>
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/defaul...3&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/defaul...3&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
SQL
> 7
> getting
> on
>
http://support.microsoft.com/defaul...3&Product=sql2k
> security.
own.
> database
stored
> in
the
> the
>
Subscribe to:
Posts (Atom)