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 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.

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.

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 SQL Server 2005 Stored Proc with Visual Studio 2005

Hello,

we have a SQL server 2005 with Visual studio Prof. 2005 in the

employment.

The debuggers function only in Visual studio correctly, as long as no

code on the SQL server must be implemented.

If a BREAK POINT in a Stored Procedure is set, this is not activated,

since this cannot be bound.

Does someone know, what it lies and can like one it eliminate?

Thank you for your assistance in advance.

Yours sincerely

Big_Ben_31

---

This entry was translated automatically with the translation

service babel.altavista.com from the German into English.

I will have to ask the obvious question about your setup. Have you ensured that it is complete? The new topics in BOL and MSDN describe the steps in details including troubleshooting ones. Please start with the links below:

http://msdn2.microsoft.com/en-us/library/kkyhd4yb.aspx

http://msdn2.microsoft.com/en-us/library/s4sszxst.aspx

Debugging SQL Server 2005 Stored Proc with Visual Studio 2005

Hi all,

I have a big ol' stored proc (about 6,000 lines), written in T-SQL. I want to debug this stored proc using something other than a bunch of PRINT statements. I tried using the Visual Studio debugger (Server Explorer - Database - Stored Proc - "Step Into Stored Procedure"), but this is behaving erratically.
The "Current statement" yellow cursor is rarely on the line that is currently being executed. It is usually between 2 and 100 lines above the actual current statement. It seems that the further down the code I get, the bigger the distance (in lines) between the yellow cursor and the actual current statement. Maybe something related to comments or multi-line statements?
I have heard of a similar problem with older versions of Visual C++ related to line feeds and return carriages (CR, LF vs. CRLF) but this doesn't seem to be the problem.
Has anyone had similar problems?

Thank you,

Vince

Might be experiencing something similar. I am used to working in SQL Server. I create my stored procedures in the query editor in SQL Server 2005. Sometimes I want to debug the procedure, so I have to go into VS, open up the object explorer, make sure that the debugging setting it on, find the stored procedure and step into it.

Well... I am seeiing erradic behavior as well; about 75% of the time, the yellow cursor disappears. Like right now... It thinks it is still debugging but the cursor is gone and the only option available for debuuging is to stop debugging. oh, and the task bar says its running... I can't seem to find out why this is happening, since I am seeing no other reports of this happening to people. Your post is the closest so far. Did you resolve your problem?

|||

While I dont have an exact remedy for you, this is not that uncommon. I have used the tools (both VS and SQL) for about two years now since the first public beta whenever that was :)...

I have seen this behavior over the months several times, but not as of late. It usually had to do with referring to old versions of code. Make sure you are referencing the correct Server/DB/Proc and refresh then debug. And check you DB connection properties.

Hope this helps,

Derek

|||

I do think it does have something to do with saving the procedure using Visual Studio 2005. It *seems* that if I save it to a project and try debugging it I have less problems...

This leads me to ask about best practices for managing T-SQL and databases. I'm sure my circumstances are not uncommon; I have a single, rather large database that is utilized by multiple applications (many of which can be in a beta development stage requireing frequent changes to the database). I was to implement source control practices for myself and my teams and I favor subversion for source control. When I create a new application, or upgrade an application to VS 2005, how can I best set up my database for change management?

I have the impression that Microsoft wants me to change the way that I work to handle my database, but it is very unclear HOW they want me to work with their products. Sometimes I write stored procedures that are not part of a single application, but that are used by multiple applications - both web and desktop. So, in this case would it be best to create some kind of database project that is seperate from my applications? And if so, how could I organize it to manage change and quality? Ugh.

|||

Hi Ryan,

I think you will find SQL Server source control integration a good first step towards acommplishing change management in the database. There have been several 3rd party attempts at the problem of managing change in the database, but those I have used were fair at best.

In general, your database can and should be a seperate entity for all but the most simplest projects aka "mom and pop websites/apps". My dev. team uses VSS (not used Team System so you may want to check into that as well) and each project is self-contained in the repository. We do not mix and match projects of different types. So maybe very simplistically speaking your VSS repository structure...

FRONTEND

-Web

-Desktop

MIDDLETIER

-Classes

-Web Services

DATABASE

-Database X

--Stored Procs

--Functions

--Triggers

DML

DDL

-Database Y

-Database Z

etc...you may enjoy a recent post on my blog regarding the usage of mangement studio's VSS integration...

http://derekcomingore.iuplog.com/default.asp?item=167168

Derek

Debugging or a parody on it?

Hi, I am new here.

I tried to debug stored procedures in the Visual Studio and you know… it was a real shock. Like going in time 10 or 20 years back.

The stored procedures I deal with contain a lot of @.tab and #tab, but it appears that I can not evaluate the values of these objects! The only type of object I can evaluate is a variable. But SQL is designed for the table data, right? So the most important type of data is not accessible!

How can I understand, what records have been affected by Update or inserted by Insert? Using prints or debug selects? Is it an era of FORTRAN or ALGOL?

To make it worse, to enter a debugger, I need to provide values of all parameters. It might be simple for the outermost procedure, but for inner stored procs, and where some parameters are some short-living identity values of some records, created in a transaction, it is very difficult to prepare all conditions to make a correct run.

So my question is, may be I had overlooked something? Is it a real debugger or just a joke from Microsoft? Sorry, but I am really angry after wasting a lot of time last week on it.

If you want to trace changes on the record level it might be better to enable auditing in SQL Server and examine the log files with Profiler. Here is one article about enabling auditing in SQL 2000:

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

|||

Thank you. But agree with me, it is far from simplicity of the debugging process we have with C# code.

Ok, I'll google if there any 3rd party tools.

Debugging In Business Intelligence Studio

I've been trying to get debugging working in BIS, but I can't seem to do it. If I am using the full blown version of Visual Studio I don't have any problems. I have read that TSQL debugging is limited to Visual Studio Professional or higher, however, there is no indication where Business Intelligence Studio is in the Visual Studio continuum. I don't know if it is considered lower or higher than Professional.

Anyway, if it is possible to use BIS for debuggin TSQL, then how does one go about doing it? I've have been competely unsuccessful to date. I have attempted to install and use the Remote Debugger, but that hasn't worked for me. So, I'm left to conclude that BIS doesn't support TSQL debugging.

Microsoft, Can you please give the final word here? This would be very nice to know one way or another.

Thanks,

DCB99

Hi dcb99,

The 'official' word (but not final) is the material you read with respect to TSQL debugging being limited to VS Professional (or higher) is correct. BI or No BI plays no part in the equation. A debugger (not the TSQL debugger of years passed) ships with VS Pro and above.

The final word is; we have it on our plate, for the next shipping version of SQL Server to get a robust debugging experience baked into the DBA - BIS experience.

Terrence Nevins

SQL Server Program Manager

|||

Terrance,

Thanks for your response. However, it doesn't quite answer my question I don't think. If I do not install a version of Visual Studio apart from installing SQL Server 2005, does BIS support debugging? I guess I'm reading that your email says it does not support debugging.

Thanks in advance.

dcb99

Debugging In Business Intelligence Studio

I've been trying to get debugging working in BIS, but I can't seem to do it. If I am using the full blown version of Visual Studio I don't have any problems. I have read that TSQL debugging is limited to Visual Studio Professional or higher, however, there is no indication where Business Intelligence Studio is in the Visual Studio continuum. I don't know if it is considered lower or higher than Professional.

Anyway, if it is possible to use BIS for debuggin TSQL, then how does one go about doing it? I've have been competely unsuccessful to date. I have attempted to install and use the Remote Debugger, but that hasn't worked for me. So, I'm left to conclude that BIS doesn't support TSQL debugging.

Microsoft, Can you please give the final word here? This would be very nice to know one way or another.

Thanks,

DCB99

Hi dcb99,

The 'official' word (but not final) is the material you read with respect to TSQL debugging being limited to VS Professional (or higher) is correct. BI or No BI plays no part in the equation. A debugger (not the TSQL debugger of years passed) ships with VS Pro and above.

The final word is; we have it on our plate, for the next shipping version of SQL Server to get a robust debugging experience baked into the DBA - BIS experience.

Terrence Nevins

SQL Server Program Manager

|||

Terrance,

Thanks for your response. However, it doesn't quite answer my question I don't think. If I do not install a version of Visual Studio apart from installing SQL Server 2005, does BIS support debugging? I guess I'm reading that your email says it does not support debugging.

Thanks in advance.

dcb99

Debugging database errors with SQL Server Express

I am trying to debug a classic ASP application using Visual Web Developer Express and SQL Server Express editions. When my application tries to execute a stored procedure, I get the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.

Is there a way for me to trace queries that are getting executed in SQL Server Express so I can see which stored procedure is failing? I seem to remember being able to do this with SQL Server 2000. Any other ideas on how to debug SQL Server errors like these using SQL Server Express?

TIA for the help.

Michael

Hi Michael,

There are a number of KB articles that mention this error, you should go to http://support.microsoft.com and search on your error to see if any of the existing articles address the issue. I found the following, fairly generic problem that produces this error, but without more informaiton it's just a shot in the dark:

269495 PRB: "Multiple-step OLE DB operation generated errors" when opening ADO connection
http://support.microsoft.com/default.aspx?scid=kb;EN-US;269495

You may also find more pople who can help with this type of problem in the ASP.net forums. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

For the record, SQL Profiler is not included in SQL Express. This was probably the tool you were using in SQL 2000.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

Debug Stored procedures in Visual Studio.net

Hi everyone,

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

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

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

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

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

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

Am I missing somethign?

Thanks,

John

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

|||

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

Debug Stored Procedure - Visual Studio 2003 - Possible?

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

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

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

Debug a stored procedure

hello,
I want to know if it is possible to debug a stored procedure using visual studio 2003 and SQL server 2000.
if yes can u help me how to do this?

Thanks.

It is possible to turn on SQL debugging, however there is a simpler means:

Add an additonal argument to the stored procedure arguments like this:

ALTER PROCEDURE dbo.BlahBlah
(
@.DEBUG Bit = 0
)
AS
SET NOCOUNT ON
IF @.DEBUG = 1
PRINT 'Debug display'
RETURN

You can then run the stored procedure within query analyser with

EXEC BlahBlah 1

|||

Thanks for your reply, but I didn't understand your example,
Could you explain more what do u mean.

What about "It is possible to turn on SQL debugging" how can i do this??
thanks.

|||

I am running on VS2005 not VS2003 on my current machine, hence I cannot give you the step the turn SQL debugging within VS2003. However when I was using VS2003 and SQL2000, I found it very much simpler to debug within query analyser using print statements. If you condition the print statement as in the example, then you can leave them in place in production. You may have I had once a 2000 line s.p. to debug, you could follow the flow by adding at say line

IF @.DEBUG = 1 PRINT 'Line 100'

If you declare a variable like

DECLARE @.PRINTLINE VARCHAR(150)

you can then display the value of @.FRED by

IF @.DEBUG = 1 BEGIN
SELECT @.PRINTLINE = 'FRED=' + CONVERT(VARCHAR(20), @.FRED)
PRINT @.PRINTLINE
END

|||That's a good idea to use PRINT for debugging. However I'd rather using SQL Profiler to capture a trace when the stored procedure is being executed, as from the trace I can see what's happening to SQL when the stored procedure is executed.|||

SQL Profiler is very good at identifying performance bottlenecks however for tracking the value of variable within a long stored procedure, a series of print statements becomes very useful. In once particular case, I had a stored procedure that was about 2000 lines long and a particular variable was getting set to null. I used some TSQL like:

IF @.DEBUG = 1 BEGIN

IF @.VAR IS NULL PRINT 'Step 12A @.VAR IS Null'

END

Once in place, I was able to rapidly locate the point at which the null value was being generated and fix the problem.