ADO closes a recordset before any data is read from a stored procedure

Recently I was working on a legacy VB Script file which has following code:

dim conn
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = connTimeOut
conn.CommandTimeout = connCmdTimeOut
conn.Open connString

dim dbCmd
set dbCmd = CreateObject("ADODB.Command")
dbCmd.CommandTimeout = 300
dbCmd.CommandType = adCmdText
dbCmd.ActiveConnection = Conn

sqlText = "ReportScheduler '" & currentDate & "' "
dbCmd.CommandText = sqlText
dim rsScheduledReports
set GetScheduledReports = dbCmd.Execute

if not rsScheduledReports.EOF then

...


Each time when the code is executed to this line, an error is generated :

ADODB.Recordset: Operation is not allowed when the object is closed

This is strange as there are actually many records come from the database and this is proved by executing the sql in Management Studio mannually.

The VBScript code worked on SQL Server 2000, recently we upgraded to version 2005 then it breaks.

I spent a lot of time googling on the Internet and finally a trick fixed it. Just add this line to the beginning of the stored procedure: SET NOCOUNT ON.

This makes me thinking what's difference between SQL Server 2000 and 2005 version when adding set nocount on statement in a stored procedure. The behaviour must have changed for new version.

How to debug VB Script in Visual Studio 2005

It has been a biggest pain to maintain a bunch of VB Script files since I work for this company. We have many daily tasks that were developed in early years when Sql Server SSIS hasn't been available. At the time of development it looked like VB Script was a good choice. And then the previous developer wrote many VB Script files sitting there for some business critical daily processes like sending invoices to customers, extracting data from our ERP system and running some stored procedures.

Some script files are very simple and contain less than 50 lines of code. For simple script files I have no problem of maintaining them, but I was horified by some monster script files like the one sending invoices to customers. There are pages of code in a file. They are definitely not for faint-hearted.

One of the major problem I had was unable to debug into script files. If we have some problems to fix I had to read each line of code and guess what was happening. And there is not enough comment in code for me to understand.

This situation lasted for several months. And some day when I was struggling again with them, I suddenly got this idea: I could put these VB Script inside ASP files and debug with ASP! Yes it's possible to do it because both Windows Script and ASP script use the same Windows Script Host.

Then I made a testing ASP page and copy it to local wwwroot folder. In the page I copied code from one of script files and inclosed the script with the <% %> symbol. Then I opened my IE and browsed to the address and I got problem with file permission immediately. This was resolved by setting proper permission to the folder under Windows. Then the ASP page run successfully!

This was a big step. Then I decided to debug the asp page using Visual Studio 2005. To debug in Visual Studio is simple. I just need to open the asp file, and attach to the process that loading the asp application.

Then the final step was to identify the process that loading the ASP script. When I browse to the ASP page and there was a bug in the file, I got this dialogue asking me which debugger to use:



When I selected a new instance of Visual Studio 2005 and click OK. The I got this security alert box:



This dialogue box told me the process is c:\windows\system32\dllhost.exe. I don't need this so I just clicked Don't Attach button.

Then I opened the asp file in Visual Studio 2005 and attached the process in menu Debug\Attach to Process and all worked fine with break points and variable value watching!