The information in this article applies to:
- Microsoft SQL Server version 6.5
- Microsoft Open Database Connectivity versions 2.5 and 3.0
SUMMARY
With Visual Basic 5.0 Enterprise Edition, you can debug SQL Server stored
procedures. This article contains a list of assorted tips that may help you
configure the Visual Basic 5.0 Enterprise Edition Transact-SQL (T-SQL)
Debugger.
MORE INFORMATION
- You must have the Enterprise Edition of Visual Basic 5.0, not the
Professional or Standard versions, to debug.
- To have all the correct components, you must have applied Service Pack 1
for SQL Server 6.5. SQL Server 6.5 Service Pack 2, which includes
Service Pack 1 for SQL Server 6.5, comes with the Visual Basic 5.0
Enterprise edition. It is located in the Tools\Tsql\Sql65.sp2
directory. In the MSDN edition, it is under the Sqlsrvsp\I386 directory
of the Microsoft Visual Studio 97, Disk Four of the March 1997
subscription.
- SQL Server must run under a user account. When run under the local
account, the behavior of the SQL Server may become inconsistent.
- Install the server side components needed for remote automation. These
components are located in the CliSrv\Tsql\SrvSetup directory of your
Visual Basic Enterprise Edition CD. On Windows NT 4.0, simply run the
Sdi_nt4.exe setup program. For a Windows NT Server 3.51 computer, you
must manually copy and register the necessary files. For complete
instructions on how to do this, see the Readme.txt file located in the
CliSrv\Tsql\SrvSetup directory. After this is complete, ensure that the
Sdi.dll file is located in the same directory as Sqlservr.exe.
- You cannot use the Visual Basic Enterprise edition T-SQL Debugger to
debug a SQL Server that is using the Sdi50.dll debugging component; the
correct Sdi.dll components must be installed as noted above. Each DLL
has a separate interface; hence the unique identifier (UID) used to
register this DLL is different than the one used by Sdi.dll.
- Ensure that the remote procedure call (RPC) services are started on the
server computer. You can do this by opening Control Panel, double-
clicking Services, and checking to make sure the Remote Procedure Call
(RPC) Service and Remote Procedure Call (RPC) Locator services are
running and set to start automatically.
- Make sure that Remote Automation is successfully installed on the client
computers COM (DCOM) installed. This is achieved during installation by
ensuring that the Remote Automation, Connection Designer, Remote Data
Objects (RDO), Control, and SQL Debugging are all selected under the
Enterprise Features.
- If your client computer is running Windows NT 4.0 or later, run DCOMCNFG
and make sure that everyone has execute and access permission for
Vbsdicli.exe. If the client computer is running Windows 95, you can
download this utility from http://www.microsoft.com/oledev/.
- Ensure that the computers can connect to one another and that you have
permission to use the resources on that Windows NT computer. For
example, if the network uses TCP/IP, use the PING utility to confirm
that the client can communicate with the SQL Server. You can also test
this communication by mapping a drive from the client computer (the one
that has Visual Basic installed on it) to the SQL Server.
- To enable the use of breakpoints during a debugging session with the
T-SQL Debugger, use client-side cursors. You can set this option under
the Miscellaneous tab on the properties of the UserConnection object.
- If the SQL Server service is stopped and restarted while the Automation
Manager continues running on the same computer, you will see an
"unexpected error" message in the Windows NT event log on the SQL Server
the next time the Automation Manager attempts to connect to SQL Server.
This error message will have a source of MSDEVSDI. If this is the case,
use Task Manager to stop the Automation Manager (Autmgr32.exe) on the
SQL Server and let the Sdi.dll and Autprx32.dll load Autmgr32.exe on the
server through DCOM.
- Ensure that the remote Automation Manager (Autmgr32.exe) is not started
from the command prompt on the client. Allow the Sdi.dll and
Autoprx32.dll files to load Autmgr32.exe on the server through COM.
- Extended error information may be contained in the Windows NT event log.
Specifically, Event ID 7 relates directly to the third and eleventh
items above.
Event ID #7: An unexpected error (xxxxxxxx) occurred. Debugging will
be turned off for connection %. Server execution failed.
The event entries are made under MSDEVSDI.
- It is not recommended that you debug on a production server. Due to the
added overhead and break-in nature of the debugging product, you may
adversely affect other users.
- You can add global variables like @@ERROR to the watch window.
- Image datatypes are not a datatype option to pass as a parameter. The
Unassigned Parameters dialog box is never displayed. The text datatype
cannot be edited or displayed in the watch window.
- You can use the T-SQL Debugger to debug stored procedures without
writing code. However, when using the T-SQL Debugger to perform
'run-time debugging' on Visual Basic code, it can be performed on an
RDO connection only.
- To configure for run-time debugging, do the following:
1. In the Add-In Manager, under the Add-Ins menu, ensure that Visual
Basic T-SQL Debugger is available.
2. On the Visual Basic Tools menu, click T-SQL Debugger Options.
3. Ensure that the "Automatically step into Stored Procedures through
RDO connections" option is enabled.
- To be able to use the T-SQL Debugger, you must use a SQL Server ODBC
driver version later than 2.65.0203.
|