Link to home
Start Free TrialLog in
Avatar of jayme9
jayme9

asked on

FileSystemObject and reading multiple script files

Goal: A vbscript that will execute multiple script files from directory that include batches with "GO"
Status: The code below allows me to successfully execute ONLY ONE script file, but I need to alter it a bit to loop through multiple script files in a specific directory(folder).
Also, in experimenting, I have several commented out lines of code, but still could not get it to work.
Question: How do I (1) specify the specific folder (2) loop through all the script files within this folder?

Thank you in advance.

'----------------------------------------------------------------------code-----------------
'Use Common.asp include file to get connection string
Dim DBTRACS
Set DBTRACS = new clsDBTRACS
DBTRACS.Open
sConn = DBTRACS.ConnectionString
DBTRACS.Close

Const ForReading = 1, ForWriting = 2
Dim cn, path, folder, File, fso, sConn, intCommentBegin, intCommentEnd, sqlScript, strAfterComment, strBeforeComment, strLine, strToGo, wshShell

'Execute sql script file(s) in directory that contain batches with "GO"
Set fso = CreateObject("Scripting.FileSystemObject")
Set cn = CreateObject("ADODB.Connection")
'test this line
path = "C:\Inetpub\wwwroot\INBOX\JASP1-TRACS-dbo-tbl_contactPOC.sql"
'path = Server.Mappath("/INBOX/") & "\"
'Set folder = fso.GetFolder(path)
cn.ConnectionString = sConn
cn.Open
'For Each File in folder.Files
'Set sqlScript = fso.OpenTextFile(path, ForReading)
Set sqlScript = fso.OpenTextFile(path, ForReading)
'Next
Response.write path
While sqlScript.AtEndOfStream = False
strLine = sqlScript.ReadLine()
If StrComp(Trim(strLine),"go",1) = 0 Then
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If
Else
While InStr(1, strLine, "/*", 1) <> 0
intCommentBegin = InStr(1, strLine, "/*", 1)
strBeforeComment = Trim(Left(strLine, intCommentBegin - 1))
If Len(strBeforeComment) <> 0 Then
strToGo = strToGo & Chr(13) & strBeforeComment
End If
While InStr(intCommentBegin, strLine, "*/", 1) = 0
strLine = strLine & Chr(13) & sqlScript.ReadLine()
Wend
intCommentEnd = InStr(intCommentBegin, strLine, "*/", 1) + 1
strLine = Trim(Right(strLine, Len(strLine) - intCommentEnd))
Wend
strToGo = strToGo & Chr(13) & strLine
End If
Wend
If Len(Trim(strToGo)) <> 0 Then
cn.Execute strToGo, , adCmdText
strToGo = ""
End If

cn.Close

Set cn = Nothing
Set fso = Nothing
ASKER CERTIFIED SOLUTION
Avatar of mrGreen
mrGreen
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial