You have many errors in that script beyond a loop issue. I suggest that you want to execute an select into query and drop all of the variables. You can get the dates as database functions.
Here is one line that makes no sense:
cnTable1.Open CacheConnectionString
I recommend doing this as a stored procedure on the database. Use SSMS to build and test the stored procedure then call it from VBScript of from PowerShell.
You would also find it easier to find you errors if you would correctly format the script.
Here is an example.
strStartDate = Replace(FormatDateTime(Now(),vbGeneralDate),"/.","-")
strEndDate = Replace(FormatDateTime(DateAdd("d",2,Now()),vbGeneralDate),"/.","-")
sSQL = "SELECT sdt.DATE_TIME, se.ENTITY_NAME, sas.CLINIC_NAME " & _
"from xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx " & _
"where xxxxxx = xxxxxxx " & _
"and xxxxxx = xxxxxxx " & _
"and xxxxxxxx >= '" & strStartDate & "' " & _
"and xxxxxxxx <= '" & strEndDate & "' " & _
"and xxxxxx = xxxxxxx " & _
"Order By xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx"
sSQLTable2 = "SELECT * FROM xxxxxxxxx"
Table1ConnectionString = "provider=MSDASQL.1;Extended Properties=DRIVER={xxxxxxxxxxxxxxxxxx};Server=xxxxxxxxxxx; Port=xxxxxx;Database=xxxxxx;UID=xxxxxx;PWD=xxxxxxxxxx"
Table2ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxxxxxxxxxxxx;DATABASE=xxxxxxxxxx;UID=xxxxxxx;PWD=xxxxxxxxxx;"
Set cnTable1 = CreateObject("ADODB.Connection")
Set cnTable2 = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")
Set oRsTable2 = CreateObject("ADODB.Recordset")
cnTable1.Open CacheConnectionString
cnTable2.Open Table2ConnectionString
Set oRsTable2 = cnTable2.Execute(sSQLTable2)
If Not oRsTable2.EOF Then
If oRsTable2("DataBeingRefreshed").value Then
If DateAdd("h",1,oRsTable2("DateLastModified").value)>Now Then
WScript.Quit
End If
End If
oRsTable2.Close
sSQLTable2 = "UPDATE xxxxxxxxxxx Set DataBeingRefreshed = 1, DateLastModified = '" & Trim(CStr(Now)) & "'"
cnTable2.Execute sSQLTab
End If
Set oRsTable2 = cnTable2.Execute("DELETE FROM OpenApptUpdateForDisplay WHERE fld_complete = 'NotComplete'; ")
Set oRs = cnTable1.Execute(sSQL)
Do While Not oRs.EOF
sSQLTable2 = "INSERT INTO OpenApptUpdateForDisplay (DATE_TIME, ENTITY_NAME, CLINIC_NAME, fld_TimeStamp)" & _
"VALUES ('" & oRs("DATE_TIME").value & ",'" & oRs("ENTITY_NAME").value & "', '" & oRs("CLINIC_NAME").value & "', '" & StartDate & "')"
cnTable2.Execute sSQLTable2
oRs.MoveNext
Loop
sSQLTable2 = "UPDATE PCMControl Set DataBeingRefreshed = 0, DateLastModified = '" & Trim(CStr(Now)) & "'"
cnTable2.Execute sSQLTable2
This is easier to read and follow and eliminates dozens of useless lines. NOw you can more easily debug this.
For result set queries and non esult set queries you can skip the recordset object and just use the execute method of the connection object.
\_(ツ)_/
