Unable to debug SQL from within the .NET IDE

  • Thread starter Thread starter Andrew Gaskell
  • Start date Start date
A

Andrew Gaskell

Hi All
When I try to step into a stored procedure in the Northwind database, in
MSDE installed locally, using the .NET IDE, I get the error message:

"cannot debug stored procedures because the SQL Server database is not
set up correctly or user does not have permission to execute
master.sp_sdidebug. Run SQL server setup or contact database
adminstrator."

I'm an administrator (local) on the machine.

Please help!

Thanks
Andrew
 
Problem
Given an application that uses a SQL Server stored procedure that is causing
errors, you need to debug the stored procedure.

Solution
Use Visual Studio .NET to debug SQL Server stored procedures (in both
standalone mode and from managed code).

Discussion
Debugging a stored procedure in standalone mode
You can debug a stored procedure in standalone mode from Visual Studio .NET
Server Explorer by following these steps:

1.. Open the Server Explorer window in Visual Studio .NET by selecting it
from the View menu.

2.. Create a connection to the database or select an existing connection.

3.. Select and expand the node for the database that contains the stored
procedure.

4.. Expand the Stored Procedures node.

5.. Right-click on the stored procedure to be debugged and select Step
Into Stored Procedure from the popup menu.

6.. If requested, supply the parameter values on the Run Stored Procedure
dialog.

Alternatively, if the stored procedure is already open in a source window in
Visual Studio .NET:

1.. Right-click on the stored procedure to be debugged and select Step
Into Stored Procedure from the popup menu.

2.. If requested, supply the parameter values on the Run Stored Procedure
dialog.

Debugging a stored procedure from managed code
To debug a stored procedure from managed code, SQL debugging must be enabled
for the project. Follow these steps:

1.. Open the solution.

2.. In the Solution Explorer window, select the project and right-click.
Select Properties from the popup menu.

3.. In the Property Pages dialog, select Debug from the Configuration
drop-down list box.

4.. Select the Configuration Properties folder in the left pane and choose
Debugging.

5.. In the Debuggers section of the right pane, set Enable SQL Debugging
to true.

6.. Click OK to close the dialog.

Table 9-2 lists the components that must be installed for SQL Server
debugging.

Table 9-2. SQL Server debugging components Component
Installation location

SQLLE.DLL
Client

SQLDBG.DLL
Client and server

MSSDBI98.DLL
Server in the \binn directory of the SQL Server instance

SQLDBREG2.EXE
Client



There are some other significant limitations to SQL Server Debugging:

a.. It is not possible to debug SQL statements that are outside of a
stored procedure.

b.. It is not possible to step into a stored procedure from managed or
unmanaged code, or into managed or unmanaged code from a stored procedure.
Set a breakpoint at entry point in the stored procedure or in the reentry
point in the code as required. Alternatively, open the code or stored
procedure and right-click on the line to break on. Select Run to Cursor from
the shortcut menu to reach the desired line without setting a breakpoint.

c.. The database connection from your application must be established with
the .NET data provider for SQL Server before debugging a mixed-language
application. After that, you can open stored procedures and set breakpoints
in the same way as for other applications.

d.. When connection pooling is enabled, debugging a stored procedure
called from native or managed code might not work after the first time. When
a connection is obtained from the pool rather than created, SQL debugging is
not reestablished.

e.. Changes to locals or parameter variables that are cached by the SQL
interpreter are not automatically modified and there is no way to force the
cache to refresh. SQL Server caches variables when the execution plan
determines that they will not be dynamically loaded for each statement
execution or reference.

For more information about debugging SQL stored procedures, see the topic
"Debugging SQL" in the MSDN Library.
 
Hi Tony,
thanks for that, but none of those points fixed the issue. I did
rummage about in the troubleshooting docs some more and I managed to
fix it. For the benefit of the ng, here is what i did:
1. Copy mssdi98.dll from the default location C:\Prog files\Microsoft
Visual Studio .NET 2003\sqlserver to the binn folder of my instance of
SQL Server (I'm running MSDE and it wasn't there - thanks MS!)
2. Add myself to the list of users allowed to access in dcomcnfg (odd
since I'm the owner anyway!)

Cheers
Andrew
 
Back
Top