Microsoft SQL Server 7.0 storage process Debugging Guide

server| process
Microsoft SQL Server 7.0 storage process Debugging Guide

Summary: Describes how to use the Microsoft Visual InterDev SQL Debugger to clear errors in the Microsoft SQL Server storage process.

Using Visual InterDev to clear errors in stored procedures and triggers
If you are using the Microsoft Visual Studio Enterprise Edition, Visual InterDev contains a SQL debugger. You can use it to clear errors in SQL Server stored procedures and triggers just as you would with other scripts or programs. However, there are some differences about how to set up SQL debugging and how the debugger performs.

To set up SQL debugging
Related software requirements for SQL Setup:

You must have a Visual Studio Enterprise version.
You must execute SQL Server 6.5 containing server Pack 2 or later. We recommend the use of version 7.0.
SQL Server must be executed on a Microsoft Windows NT 4.0 or higher version.
Your workstation must perform Windows 95, Windows 98, Windows NT 4.0, or later.
To use SQL debugging, you must make the appropriate settings for the server and workstation. You should:

Make sure that you have installed the SQL debugging components in SQL Server.
Create a Windows NT user that has administrative privileges on the SQL Server computer.
Set up the Distributed Component Object Model (DCOM) on the server for SQL debugging to use.
Determining that the client's DCOM Settings support SQL Debugging (for Windows 95, 98 workstations only).
Installing SQL Server Debugging components
SQL debugging requires that you install the components in your SQL Server. These components are part of the visual Studio Enterprise version.

Installing the SQL Debugging components
On the computer where SQL Server is installed, start the Setup program for the Visual Studio Enterprise Edition.
The Setup Wizard displays different options depending on whether the server component is installed on your computer.
If the server is already installed, select the server application and tools in the Add/remove options.
If no other components are installed, select Next until you reach the page that provides the Enterprise Settings option. Select server applications and tools.
If no other components are installed, select Next until you reach the page that provides the Enterprise Settings option. Select server applications and tools.
On the next page, select the Start BackOffice Installation Wizard, and then select Install.
When the BackOffice Business Solution Wizard is displayed, select the custom installation, and then select Next.
Continue to the next step until you see a page that provides a list of installation components. Clear the remaining components in addition to the following components:
SQL Server Debugging.
MS Data access component.
Visual InterDev Server.
Press the installation.
Set up a debug user
To use SQL debugging, you must provide an account number and password for a Windows NT user with administrative privileges on the computer that is executing SQL Server.

Set up a user for use with SQL debugging
In the server's Windows console, select Set up a debug user, and then select a service.
Select MSSQL, and then select Start.
The selected login account is a set value. If this option is set to the system account, change it to this account, and enter the correct domain and user account number of the user with administrative privileges in the form of a domain \ account number.
If this setting has been changed, restart the SQL Server.
To set up DCOM for use by SQL debugging
If you can, keep a copy of the machine in your machine, then you can do cross machine debugging without using DCOM.

SQL debugging uses the Distributed Component Object Model (DCOM) to communicate between your client computer and the database server. Therefore, you must set DCOM so that a remote user attaches a handler on the debugger.

By default, the correct DCOM settings are set when you install SQL Server on the server. However, you may want to restrict access to debugging for the sake of security on the SQL Server computer.

To set DCOM on the server for use by SQL debugging
From the server's Windows Start menu, select Run, and then type Dcomcnfg.exe at the prompt that opens the box.
In the Distributed Component Object Model (distributed COM) Settings Content window, select the preset Security page. Select the edit preset under preset access permissions.
If the everyone in the group does not already have permissions, select Add, and then add a domain and user account for the user with administrative privileges in the form of a domain \ account number.
After adding this account, check system. If it does not appear on the manifest, add it using the Add Name and selection list in the Group dialog box.
If you have modified any of the options described in this procedure, restart SQL Server.
Note: If you want to add an account number to a remote server and the account cannot perform debugging, it is possible that the user of that account is performing Visual InterDev on the server computer.

Performing SQL debugging
Unlike the error of clearing other kinds of programs, you cannot clear the stored procedure or the trigger program that is executing. You can open the procedure in the editor and clear the error from there.

In the Data Checking window, right-click on the stored procedure and select Debug.

The editor opens the stored procedure in its window and then selects the Debug command in the Debug menu.

After you open the editor window in debug mode, you can use the debugger pointers as usual. For example, you can set breakpoints in a native window and step through the process. You can view the value of a variable. You can also drag an expression to the Watch window to perform a step-by-step instruction or execute a program for use. The results of SQL print are displayed in the Output window.

However, the SQL debugger differs from the debugger used by the script in the following ways:
The auto and immediate windows are not useful when debugging a stored procedure. Although you can display them, the Auto window will become empty and the Immediate window will not allow you to enter an expression.
You can change the order of execution to specify with the next statement set.

If you are using a simple SELECT statement-those that return only a single value-the returned value is a variable that can be viewed in the native window. However, if a SELECT statement returns a result set, it is not displayed in the debugger. In addition, you can view the result set in the Output window.