debugging SQL Server stored procedures and user-defined functions

Category: Database management 2005-06-03 13:57 9837 people read reviews (5) favorite reports SQL Server storage vb.net server SQL language

1. Debugging in Query Analyzer

The steps to debug in Query Analyzer are as follows:
SQL 2000 Query Analyzer
--Left Join Object Browser (no press F8)
--In the object item
--Right-click the stored procedure for debugging
--Commissioning
--Input parameters (all parameters must be entered, including default/output parameters
--Click Execute
--a floating tool bar appears
--There are single steps, breakpoint settings, etc.

Pressing F11 is one step.

If you want to run to a specified line, simply move the cursor to the specified line, and then press CTRL+F10

2. Debug by output stored procedure or user-defined function processing result

If you cannot debug a stored procedure using Query Analyzer, you can add print or SELECT statements directly to the stored procedure, outputting the results of the execution of each step of the stored procedure. For user-defined functions, you cannot use print or SELECT statements to output the execution results of each step, to schedule user-defined functions, you can change the user-defined function to a stored procedure, and then change back to the user-defined function after debugging successfully.

3. Commissioning in vb.net

menu, select "Tools"--"Connect to Database"
Select "View"--"Server Explorer"
In Server Explorer, right-click the stored procedure or user-defined function you want to debug-step into.

4, to ensure that the Query Analyzer or vb.net can successfully debug a few things

1. The MSSQLServer service for a remote SQL Server requires a specified user (not a local system user), preferably a administrator boot.
Control Panel--management tool--service--right-mssqlserver--Property--Log in, change the login status to "This account", and set the corresponding user name and password.
2. Users who log on to remote SQL use SA
3. The remote SQL Server requires that the debug feature is not disabled, execute the following statement to turn on the debug feature:
Sp_sdidebug legacy_on
4. Ensure that debugging is consistent with the SQL Server version of the computer being debugged, whether it is a client or a server, it is best to install SQL SP4.
Check that the sq Serverl has no patching method to run in Query Analyzer:
SELECT @ @version
If the version number that comes out is 8.00.2039 or less, the SP4 patch is not installed.
Location of all patches (in the middle part of the download page, you can select a language and you must ensure that the downloaded patch language corresponds to the SQL Server instance language)
Simplified Chinese layout SQL Server 2000 patches that should be installed
Attention:
A. After the patch is downloaded, the execution time is decompression, to perform setup.bat in the extracted directory is the real installation
B. If the operating system is XP, after installing XP SP2, regardless of whether SQL SP4 has been installed before, install it again, and open port 1433 in the firewall, otherwise it cannot be accessed by other computers.
C. SQL Server running the mechanism on Windows 2003 must have a SP3 or higher patch version installed.

debugging SQL Server stored procedures and user-defined functions