RE: Stepping through stored procedures not working
From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 05/31/04
- Next message: Maik Siegel: "Re: Place "Exec sp.*****" in Cursor ??"
- Previous message: Damien Laffan: "Re: DATEDIFF / DATEPART inconsistent for DW ?"
- In reply to: Rodger Dusatko: "Stepping through stored procedures not working"
- Next in thread: Rodger Dusatko: "Re: Stepping through stored procedures not working"
- Reply: Rodger Dusatko: "Re: Stepping through stored procedures not working"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 31 May 2004 23:29:28 GMT
vishalsu@online.microsoft.com
ISSUE:
=======
For some reason I am not succeeding in stepping through my stored procedures
(I'm using VB.NET with Windows Server 2003).
Instead of allowing me to go step by step, the procedure is executed.
RESOLUTION;
==============
INF: Tips for Debugging Stored Procedures from Visual Basic
View products that this article applies to.
This article was previously published under Q170496
For a Microsoft Visual Basic .NET version of this article, see the
following article in the Microsoft Knowledge Base:
316549 HOW TO: Debug Stored Procedures in Visual Studio .NET
SUMMARY
With Visual Basic 5.0 Enterprise Edition or later, 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 or later Enterprise Edition
Transact-SQL (T-SQL) Debugger.
MORE INFORMATION
Use the following information for debugging:
For information on problems that may arise when using the debugger with SQL
Server 2000, click the article number below to view the article in the
Microsoft Knowledge Base:
310650 INFO: Known Issues with the Visual Basic T-SQL Debugger and SQL
Server 2000
To debug, you must have the Enterprise Edition of Visual Basic 5.0 or
later, not the Professional or Standard versions.
To have all the correct components, you must have SQL Server version 6.5 or
SQL Server 6.5 Service Pack 2 or later. SQL Server 6.5 Service Pack 4 ships
with Visual Studio 6.0 and is found on Disk Two under the Sql\Servpak
directory. You can obtain the latest SQL Server service pack from the
following Web address:
http://msdn.microsoft.com/support
SQL Server must run under a Windows NT user account, the local Windows NT
computer's Administrator user account or group is recommended. For best
performance, run the SQL Server under a domain account that is part of the
local administrators group. The debugger will not work correctly when
executing SQL Server under the local system account.
Before the distributed component object model (DCOM) protocol was
available, Remote Automation was the legacy model used to communicate
between platforms. Remote Automation is implementated to faciliate
communication between the Visual Basic T-SQL Debugger and SQL Server. To
ensure all the Remote Automation components are correctly put in place
during installation make sure that the Remote Automation, Connection
Designer, Remote Data Objects (RDO) Control, and SQL Debugging are all
selected under the Enterprise Features on the client.
NOTE: The T-SQL Debugger that ships with SQL Server 7.0 or Visual Interdev
6.0 is built upon the newer DCOM protocol and mechanisms. To install these
components with SQL Server 7.0 make sure that the Debugger interface
options presented during setup are selected.
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 version 5.0 CD and under the sqdbg_ss\ directory
on the Visual Basic Enterprise Edition version 6.0 CD Two. For Visual Basic
6.0, run the setup.exe found under the sqdbg_ss\ directory. For Visual
Basic 5.0, on Windows NT 4.0, 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, please see
the Readme.txt file located in the CliSrv\Tsql\SrvSetup directory. After
this is complete, make sure 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 or Sdi60.dll debugging component;
the correct Sdi.dll components must be installed as noted previously. 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.
Make sure 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.
If your client computer is running a client that has DCOM capable platform,
like Windows 95, Windows 98 and Windows NT 4.0 or later, run DCOMCNFG and
make sure that 'everyone' has Execute and Access permissions for the
Vbsdicli.exe file. If the client computer is running Windows 95, you can
download the DCOM Configuration utility from the following:
http://msdn.microsoft.com/library/default.htm
If possible, test the T-SQL debugger on the same computer that the SQL
Server is installed upon, to make sure it is working correctly.
Check to see 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) 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
the DCOM protocol mechanism.
Make sure 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 the COM service control
manager mechanism (rpcss.exe).
Extended error information may be contained in the Windows NT event log.
Specifically, Event ID 7 relates directly to the preceding third and
eleventh items. The text for the error follows:
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.
Do not debug on a production server. When stepping through the debugger
there are resources that can be locked that others need, therefore
adversely affecting other clients performance and operation.
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 never displays. You cannot edit or display
the text datatype 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:
In the Add-In Manager, under the Add-Ins menu, make sure that Visual Basic
T-SQL Debugger is available.
On the Visual Basic Tools menu, click T-SQL Debugger Options.
Make sure that the "Automatically step into Stored Procedures through RDO
connections" option is enabled.
To use the T-SQL Debugger, you must use a SQL Server ODBC driver version
later than 2.65.0203.
REFERENCES
LINKS:
=======
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q170/4/96.asp&NoWebContent=1
HTH
This posting is provided "AS IS" with no warranties, and confers no rights.
- Next message: Maik Siegel: "Re: Place "Exec sp.*****" in Cursor ??"
- Previous message: Damien Laffan: "Re: DATEDIFF / DATEPART inconsistent for DW ?"
- In reply to: Rodger Dusatko: "Stepping through stored procedures not working"
- Next in thread: Rodger Dusatko: "Re: Stepping through stored procedures not working"
- Reply: Rodger Dusatko: "Re: Stepping through stored procedures not working"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
