Troubleshooting the Transact-SQL Debugger

Troubleshooting SQL Server

previous page next page
Troubleshooting

Troubleshooting the Transact-SQL Debugger

The goal of this topic is to address any problems you may encounter while trying to start and use the Transact-SQL debugger. Potential sources of error include:

  • Incorrect DCOM permission settings

  • DCOM on Windows 98

  • Missing or unregistered DLLs

  • Lack of (or limited) debugger support

This topic describes how to identify and remedy various problems. Before attempting to troubleshoot your debugger configuration, verify the following:

  1. Make sure you have permission to execute the SP_SDIDEBUG extended procedure.

  2. Start the SQL Server Service using an account that has Administrator privileges on that computer.

  3. Check the Event Viewer's Application and System logs for any error messages.
Incorrect DCOM Permission Settings

SQL debugging uses Distributed COM (DCOM) to communicate between your client computer and the database server. You must configure DCOM to allow remote users to attach the debugger to a process on the database server.

By default, the correct DCOM settings are in place when SQL Server is installed. However, because of security considerations for the computer running SQL Server, you may want to restrict debugging access. Use the following as a general procedure for setting up DCOM on the SQL Server computer.

Important  If you plan on running the debugger as any user other than the one running SQL Server, DCOM will need to be correctly configured first. Follow these steps to insure you have execution permission.

On the server:

  1. Run DCOMCNFG.EXE.

  2. In the Distributed COM Configuration Properties window, select the Default Security tab. Under Default Access Permissions, click Edit Default.

  3. If group Everyone does not already have permissions, you may add it by clicking Add. Select the local machine name from the List Name From list. Select Everyone and make sure that Type of Access is set to Allow Access. Then click OK.

    OR

    If you prefer to limit debugging to specific users, you can add individual domain user accounts (e.g. domain\account) with administrator privileges. If you choose to add only domain accounts instead of Everyone, make sure to add the SYSTEM account as well.

  4. Switch to the Applications tab of the Distributed COM Configuration Properties dialog.

  5. Scroll through the Applications list and select SQLDBREG. Then click Properties.

  6. Select the Identity tab and make sure that The interactive user is selected as the user to run this application.

  7. Click OK to close the dialog box.

  8. Restart the SQL Server service.

After applying those  steps, your server's DCOM settings should look like this:

   DCOMCNFG
   |
   |__ Application Tab
   |   |
   |   |_____ SQLDBREG
   |      |
   |      |______ Identity Tab
   |         |
   |         |_______ The interactive user
   |
   |__ Default Security Tab
      |
      |_____ Default Access Permissions
         |
         |______ Edit Default Button
            |
            |_______ Everyone (or domain\account and System)
DCOM on Windows 98

DCOM95 on Windows 98 is not as robust as DCOM on Windows NT. In addition, RPCSS.EXE on Windows 98 can cause problems if it is not started either at shell load time or at boot time. To ensure that RPCSS.EXE is started early enough, modify either of the following registry entries:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\RunServices

Using any name, follow these steps to add a new String Value to the registry at either of those locations, and set its value to 'C:\WINDOWS\SYSTEM\RPCSS':

  1. Run REGEDIT.EXE.

  2. Expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\ CurrentVersion\Run.

  3. Right-click and select New -> String Value.

  4. Type in any name (e.g. RPC).

  5. Double-click on the newly created string.

  6. Type in the value (e.g. C:\WINDOWS\SYSTEM\RPCSS.EXE).

  7. Reboot the computer.

Also, make sure that the following registry keys are set to 'y':

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Ole\EnableDCOM
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Ole\EnableRemoteConnect

You must reboot your computer after making any of these changes.

Missing or unregistered DLLs

If the debugger fails to start, and you encounter any of these error messages, then you may need to manually setup the debugging environment:

  • Error in output window (indicates mssdi98.dll is missing under ..\mssql\binn folder)

ODBC: Msg 0, Level 16, State 1

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL mssdi98.dll, or one of the DLLs it references.

Reason: 126 (The specified module could not be found.).

  • Error in Event Viewer/Application log (indicates sqldbg.dll is not registered or sqldbreg is not registered)

Unable to connect to debugger on <Server Name>

(Error = 0x80004002 No such interface supported ).

Ensure that client side components such as SQLDBREG.EXE are installed and registered on <Server Name>. Debugging disabled for connection 54.

  • Error Message Dialog Box (indicates sqldbg.dll is not registered or sqldbreg is not registered).

Missing or unregistered sdiclnt.dll

Initialization of the debugger failed!

Possible cause:

'sdiclnt.dll' was not installed or registered properly.

Follow these steps to manually install and register missing debugger components when the server and client (i.e. Query Analyzer) are running on the same machine:

  1. Copy file sdiclnt.dll from the \x86\binn folder of your SQL Server 2000 CD-ROM to c:\program files\microsoft sql server\mssql\80\tools\binn, and then register it by executing the following command: 
    regsvr32 sdiclnt.dll

    Note  On Windows 9x, the regsvr32 utility is located under the \windows\system folder.

  2. Create folder c:\program files\common files\microsoft shared\SQL Debugging.

  3. Copy the following files from \x86\other\sdi to c:\program files\common files\microsoft shared\SQL Debugging: 
    sqldbg.dll
sqldbreg.exe

    then, register the files:

    regsvr32 sqldbg.dll
sqldbreg /RegServer
  4. Copy file mssdi98.dll from \x86\other\sdi to where sqlservr.exe resides (e.g. c:\program files\microsoft sql server\mssql\binn).

  5. Restart the SQL Server service.

Follow these steps to manually install and register missing debugger components when the server and client are running on different machines:

On the client:

  1. Copy file sdiclnt.dll from the \x86\binn folder of your SQL Server 2000 CD-ROM to c:\program files\microsoft sql server\mssql\80\tools\binn, then register it: 
    regsvr32 sdiclnt.dll

    Note  On Windows 9x, the regsvr32 utility is located under the \windows\system folder.

  2. Create folder c:\program files\common files\microsoft shared\SQL Debugging.

  3. Copy the following files from \x86\other\sdi to c:\program files\common files\microsoft shared\SQL Debugging: 
    sqldbg.dll
sqldbreg.exe

    then, register the files:

    regsvr32 sqldbg.dll
sqldbreg /RegServer

On the server:

  1. Create folder c:\program files\common files\microsoft shared\SQL Debugging.

  2. Copy the following file from \x86\other\sdi to c:\program files\common files\microsoft shared\SQL Debugging: 
    sqldbg.dll

    then, register the file:

    regsvr32 sqldbg.dll
  3. Copy file mssdi98.dll from \x86\other\sdi to where sqlservr.exe resides (e.g. c:\program files\microsoft sql server\mssql\binn)

  4. Restart the SQL Server service
Lack of (or limited) debugger support
  • Limited support is provided for these data types:
    • table

      Variables of this type are not displayed in the local variables window.

    • sql_variant, text, ntext, image, and cursor

      Variables of these types are displayed within the local variables window, but cannot be modified.

  • Procedures of greater than 64K in size are not supported.

  • Nesting of greater than 32 stored procedure calls is not supported.

  • Passing of more than 1023 arguments to a stored procedure is not supported.

previous page start next page