-1

I'm having many databases in my server Management studio. In one of the database I'm using a stored procedure. I want to execute it using VBScript and store the result using record set into a text file. Is there any possible way to do it? Please help me with this.

Here is my code:

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=CIBCWT12;Initial Catalog=dbFXCashCommerceManager;Trusted_Connection=yes;"

SET objFSO = CREATEOBJECT("Scripting.FileSystemObject")
CurrentDirectory = objFSO.GetParentFolderName(WSCript.ScriptFullName)
folder_name = CurrentDirectory & "\ModifReport\" & Replace(Date, "-", "_")

arr = split(folder_name, "\")
path = ""

For Each dir In arr
  If path <> "" Then path = path & "\"
  path = path & dir
  If objFSO.FolderExists(path) = False Then 
     objFSO.CreateFolder path
  End If
Next

'creating the file
set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile(folder_name & "\ModifReport" & "_" & Replace(Date, "-", "_") &".csv")

Set myConn = CreateObject("ADODB.Connection")
Set myRS = CreateObject("ADODB.recordSet" )
Set cmd = CreateObject("ADODB.Command" )

Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile(CurrentDirectory & "\listfile.txt",1)
SQLStr1 = objFileToRead.ReadAll()
objFileToRead.Close
Set objFileToRead = Nothing

myConn.Open DB_CONNECT_STRING

cmd.ActiveConnection = myConn
cmd.CommandText = SQLStr1
cmd.CommandType = 4
'cmd.Execute

'myRS.CursorType = 1
'myRS.Open SQLStr1, myConn, 0, 1, 4

Set myRS = myConn.Execute(SQLStr1,4)

outFile.Writeline "FXO_Warehouse_Code;TrackingNumber;1. Submitted;2. Processing;1-2 Hr;1-2 BHr;Processing Flag;3. Order Released;2-3 Hr;2-3 BHr;Released Flag;4. Order Ack by Branch;3-4 Hr;3-4 BHr;5. Order Completed;4-5 Hr;4-5 BHr;ExpectedDelivery;Order Returned to Intria;Transit;firstname;lastname;Currency Code;FGN Amount;Branch Flag;1-2BD;2-3BD;3-4BD;4-5BD;1-4BD;Brink Del Days;SLA Flag"
msgbox myRS.state 
msgbox myConn.state 

Do Until myRS.eof  
   outFile.Writeline myRS.Fields("FXO_Warehouse_Code") & ";" & myRS.Fields("TrackingNumber") & ";" & myRS.Fields("1. Submitted") & ";" & myRS.Fields("2. Processing") & ";" & myRS.Fields("1-2 Hr") & ";" & myRS.Fields("1-2 BHr") & ";" & myRS.Fields("Processing Flag") & ";" & myRS.Fields("3. Order Released") & ";" & myRS.Fields("2-3 Hr") & ";" & myRS.Fields("2-3 BHr") & ";" & myRS.Fields("Released Flag") & ";" & myRS.Fields("4. Order Ack by Branch") & ";" & myRS.Fields("3-4 Hr") & ";" & myRS.Fields("3-4 BHr") & ";" & myRS.Fields("5. Order Completed") & ";" & myRS.Fields("4-5 Hr") & ";" & myRS.Fields("4-5 BHr") & ";" & myRS.Fields("ExpectedDelivery") & ";" & myRS.Fields("Order Returned to Intria") & ";" & myRS.Fields("Transit") & ";" & myRS.Fields("firstname") & ";" & myRS.Fields("lastname") & ";" & myRS.Fields("Currency Code") & ";" & myRS.Fields("FGN Amount") & ";" & myRS.Fields("Branch Flag") & ";" & myRS.Fields("1-2BD") & ";" & myRS.Fields("2-3BD") & ";" & myRS.Fields("3-4BD") & ";" & myRS.Fields("4-5BD") & ";" & myRS.Fields("1-4BD") & ";" & myRS.Fields("Brink Del Days") & ";" & myRS.Fields("SLA Flag")
   myRS.MoveNext()
loop

myRS.Close
SET myRS = Nothing

myConn.Close
outFile.Close

msgbox "execution completed"

When I tried the above code for a normal query, I got the result. But if I'm trying to use it for stored procedure, it is showing as "Operation is not allowed as the object is closed."

Here I'm reading my stored procedure from a text file.

For executing normal query, I'm using myRS.Open SQLStr1, myConn, 0, 1, 4

For executing SP I'm using Set myRS = myConn.Execute(SQLStr1,4)

I didn't understand why Record Set is getting closed automatically when I'm executing for a stored procedure. Can Someone please help me with this?

dhilt
  • 18,707
  • 8
  • 70
  • 85
mahesh10549
  • 1
  • 1
  • Are you using SET NOCOUNT ON or SET ANSI_WARNINGS OFF in your Stored Procedure? If not, try adding them below the "AS" in your Stored Procedure. ADO can confuse the extra return messages and close the Result Set object. – Andrew Drake Aug 23 '18 at 19:52
  • [Did you look](https://stackoverflow.com/a/21944948/692942) before posting *(this applies both to VBScript and Classic ASP, recommend reading through the first 3 points)*? – user692942 Aug 23 '18 at 20:44
  • Thanks Drake and Lankymart, it worked perfectly. – mahesh10549 Aug 24 '18 at 15:39
  • 1
    Possible duplicate of [Using Stored Procedure in Classical ASP .. execute and get results](https://stackoverflow.com/questions/21915874/using-stored-procedure-in-classical-asp-execute-and-get-results) – user692942 Aug 26 '18 at 08:45

1 Answers1

0

Extra return messages from a stored procedure can cause the ADO Result Set to close prematurely due to error. So when trying to close the result set RS, you get the "Operation is not allowed as the object is closed." error.

In the stored procedure which your code is calling, use SET NOCOUNT ON to force the stored procedure to not return the affected row counts. It may or may not also be wise to use SET ANSI_WARNINGS OFF to remove the possibility of stored procedure errors causing the VBScript to crash.

Andrew Drake
  • 655
  • 1
  • 11
  • 25