‘connection to the sql server

Option Explicit
dim currentTime
Dim sServer
Dim sLogin
Dim sPwd
Dim oCn
Dim oRs
Dim sNames
Dim oFso
Dim oFileOut
Dim sLineOut, Wl, sql ,i, fileName,yearlevel
Dim wshShell
Dim objNetwork,strLocalDrive,strRemoteShare,objReg,objfso

 

sServer = “<databaseservername>”
sLogin = “<databasename>”
sPwd = “<password>”

Set oCn = CreateObject( “ADODB.Connection” )
Set oRs = CreateObject( “ADODB.Recordset”  )

Set oFso = CreateObject(“Scripting.FileSystemObject”)
set Wl = WScript.CreateObject(“WScript.Shell”)

oCn.ConnectionString = “PROVIDER=SQLOLEDB” & _
“;SERVER=” & sServer   & _
“;UID=”    & sLogin  & _
“;PWD=”    & sPwd    & _
“;DATABASE=EWorkSpaceV5”
oCn.open
sql= “<SQLPART> = ‘”& Wl.expandenvironmentstrings(“%username%”) & “‘”
fileName = Wl.expandenvironmentstrings(“%userprofile%”) & “\StudCls.csv”
oRs.Open sql, oCn
‘connection is done
‘create a file to save the data
set oFileOut = oFso.CreateTextFile(fileName, True)
yearlevel = oRs.Fields(3).Value
While Not oRs.EOF
‘ Get the column data for each column in the row.
For i = 0 to oRs.Fields.Count – 1
sLineOut = sLineOut & oRs.Fields(i).Value
‘ Separate the fields with commas
If i < oRs.Fields.Count – 1  Then
sLineOut = sLineOut & “,”
End If
Next

oFileOut.WriteLine(sLineOut)
sLineOut = “”
oRS.MoveNext
Wend

oRs.Close
oFileOut.Close
Set objFSO = CreateObject(“Scripting.FileSystemObject”)

Set WshShell = CreateObject(“WScript.Shell”)
WshShell.Run chr(34) & “<bat1>” & Chr(34), 0
WshShell.Run chr(34) & Wl.expandenvironmentstrings(“%logonserver%”)&”<exe1>” &chr(34) &” “& chr(34)& Wl.expandenvironmentstrings(“%logonserver%”)&”<arg1>” &chr(34)& ” <arg2>” , 0
WshShell.Run chr(34) & “<exe2>” & chr(34) & “<arg2>”, 0
if yearlevel <> “UG” then
Set objNetwork = WScript.CreateObject(“WScript.Network”)
If objFSO.DriveExists(“m:”) Then
objNetwork.RemoveNetworkDrive “m:”,True,True
End If

If objFSO.DriveExists(“m:”) Then
Set objReg =  GetObject(“winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv”)
objReg.DeleteKey HKCU, “Network\” & Left(“m:”, 1)
Set objReg = Nothing
End If

If objFSO.DriveExists(“g:”) Then
objNetwork.RemoveNetworkDrive “g:”,True,True
End If

If objFSO.DriveExists(“g:”) Then
Set objReg =  GetObject(“winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv”)
objReg.DeleteKey HKCU, “Network\” & Left(“g:”, 1)
Set objReg = Nothing
End If
yearlevel=trim(yearlevel)
if left(yearlevel,1)= “0” then
yearlevel=right(yearlevel,1)
end if
strLocalDrive = “m:”
strRemoteShare = “<sharedriveLocation>”&yearlevel
On Error Resume Next
objNetwork.MapNetworkDrive strLocalDrive, strRemoteShare, “False”
If Err.Number <> 0 Then
WScript.echo “Got Error#” & Err.Number & “: ” & Err.Description
WScript.echo “while mapping ” & strRemoteShare & ” to ” & strLocalDrive
WScript.quit
End If
On Error Goto 0
‘WScript.echo ” Drive Mapped ” & strLocalDrive
End if
Set WshShell = Nothing

Advertisements