VBScript Debug
-
Good afternoon,
I am running SQL 7 desktop edition on Windows 2000 Pro. I would like to be able to debug VBScript used in DTS packages and then deploy the package on our sever. I found an article on MSDN that describes how to use the Script Debugger when using SQL 2000. No mention of SQL 7.
Is it possible to use the script debugger in SQL 7 DTS?
Thanks!
-
Yes even with SQL 7 as long as you have the script debugger you can do it.
quote:
There will, of course, be times when you're at a loss to understand why one of your scripts isn't working (it happens to me all the time!). The only solution is to apply some serious debugging analysis. Fortunately, Microsoft has some excellent tools in this area.The NT4 Option Pack includes a script debugger that works with DTS ActiveX Script. If you've lost your NT4 Option Pack CD, you can download the latest version of this tool from the Microsoft Windows Script Technologies Web site at http://msdn.microsoft.com/scripting. The download is small, free, and installs itself very neatly.
To debug a script, you need to set a breakpoint. If you're coding in VBScript, then insert a Stop statement; in Jscript, the equivalent statement is Debugger. When the script executes and reaches the breakpoint, the Script Debugger will automatically open and display the script code with the breakpoint highlighted. BOL mentions a possible issue over DTS Packages that execute tasks in parallel by spawning additional processing threads. If you wish to debug scripts in several tasks at once, then you need to temporarily prevent this. For each task to be debugged, right-click on it, select Workflow Properties, and switch on the option to "Execute on Main Package Thread."
In Script Debugger, you can't edit the script, but some standard debug functions are available, such as stepping through code and managing further breakpoints. It also has an Immediate Command window in which you can inspect or set the values of variables used in the script. To display a value, type "?" (for PRINT) followed by the expression and hit Enter. To set a variable, simply type an assignment–for example, DTSDestination("FirstName") = "Paul." You can also reference any object in the COM hierarchy by typing the appropriate Set expression and then querying it.
Don't forget that if the script is part of a column transformation, then the script will be invoked–and debugged–for every row in the data source. Use the Stop Debugging button to avoid continuing through all the repetitions.
Microsoft's Visual InterDev, available both as a standalone Web application development tool and as part of Visual Studio, offers more sophisticated debugging capabilities, such as the ability to do "just-in-time" debugging. Then, when a runtime error is detected in your script, if Visual InterDev isn't already running, it will ask you whether you want to start the debugger to handle it. Think carefully before taking this option. If the script has a runtime error, you won't be able to step over the statement in the debugger, and the only way to stop the script is to end debugging. Unfortunately, this closes the process that's connected to the debugger–usually the Microsoft Management Console under which SQL Server is running. Closing this process will terminate MMC's Enterprise Manager and DTS Designer without prompting you to save any open packages or other documents. You've been warned!
Despite this, Visual InterDev gives you some great debugging capabilities. As well as the Immediate Command window, in which you can assign and query variables, there's a window for browsing through all variables and object properties. To add an object to this window, just issue a Set command in the Immediate Command window. There's also a Watch window that shows all of the property values for selected variables as they change.
-
Thanks for your incredibly helpful reply!
One question: Can I instal the NT 4 Option pack on my 2000 Pro box?
Thanks again!
-
No, but in the Windows Components Wizard in Add/Remove Programs under Control Panel you will find it there on the install disk. Just go in and add it.
-
quote:
Think carefully before taking this option. If the script has a runtime error, you won't be able to step over the statement in the debugger, and the only way to stop the script is to end debugging. Unfortunately, this closes the process that's connected to the debugger–usually the Microsoft Management Console under which SQL Server is running. Closing this process will terminate MMC's Enterprise Manager and DTS Designer without prompting you to save any open packages or other documents. You've been warned!To prevent this drastic measure, choose "detach all processes" before selecting "stop debugging." But I would still do a "SAVE" before starting
-
ALways save before testing. Keep in Mind DTS has a built in versioning and if something doesn't work you can always go back without much fuss.
-
Thanks everyone once again! I made sure the debugger was installed and made the following registry tweek:
[HKEY_CURRENT_USER\Software\Microsoft\Windows Script\Settings]
"JITDebug"=dword:00000001
To deactivate just-in-time debugging, run a *.reg file with the following
lines:
REGEDIT4
[HKEY_CURRENT_USER\Software\Microsoft\Windows Script\Settings]
"JITDebug"=dword:00000000
This allows the debugger to start at a "stop" statement. I have made more progress on my scripts today than in the past week.
Jonathan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply