Debug SQL Server Stored Procedures and user-defined functions

1. debug in the query Analyzer

The steps for debugging in the query analyzer are as follows:
SQL 2000 query Analyzer
-- Left-connected Object Browser (if not, press F8)
-- Object item
-- Right-click the Stored Procedure for debugging
-- Debugging
-- Input parameters (all parameters must be input, including default values/output parameters)
-- Click to execute
-- A floating toolbar appears.
-- One-step execution, breakpoint settings, etc.

F11 is a one-step execution.

If you want to run to a specified row, you only need to move the cursor to the specified row, and then press Ctrl + F10

2. debug by outputting the processing results of stored procedures or user-defined functions

If you cannot use the query analyzer to debug the stored procedure, you can directly add print or select statements in the stored procedure to output the execution results of each step of the stored procedure. For user-defined functions, the execution results of each step cannot be Output Using print or select statements. To schedule user-defined functions, you can change user-defined functions to stored procedures, after successful debugging, change to the User-Defined Function.

3. debug in VB. NET

Select "tool" -- "connect to Database" from the menu"
Select "View" -- "server resource manager"
In the server resource manager, right-click the stored procedure to be debugged or the User-Defined Function-one-step execution.

4. Several items to ensure successful debugging in the query analyzer or VB. NET

1. The MSSQLServer service of the remote SQL server requires the use of a specified user (not a local system user), preferably the Administrator to start.
Control Panel -- Administrative Tools -- service -- Right-click MSSQLServer -- properties -- logon, change the logon identity to "this account", and set the corresponding user name and password.
2. log on to the remote SQL user using SA
3. If the remote SQL server requires that the debugging function is not disabled, run the following statement to enable the debugging function:
Sp_sdidebug legacy_on
4. Ensure that the debugging is consistent with the SQL Server version of the computer to be debugged. It is best to Install SQL SP4.
To check whether sq serverl has been patched, run it in the query Analyzer:
Select @ version
If the version is 8.00.2039 or earlier, the SP4 Patch Is Not Installed.
Location of all patches (you can select a language in the middle of the download page. Make sure that the downloaded patch language corresponds to the SQL server instance language)
Patches to be installed for simplified Chinese SQL Server 2000
Note:
A. After the patch is downloaded, It is decompressed during execution. You need to execute setup. bat in the decompressed directory to install it.
B. if the operating system is XP, you must install XP SP2 again no matter whether or not SQL SP4 has been installed before and enable port 1433 in the firewall, otherwise, it cannot be accessed by other computers.
C. For SQL server running on Windows 2003, SP3 or a later patch version must be installed.