Combine 2 VBScripts using the same file

54 views Asked by At

Pretty new to scripting but i managed to put something together where i retrieve free disk space - formatted how i want. then the file is imported to SQL.

the error i am getting is that the output file is being used by the connection. how can i get this to run under one script without errors.. all help is appreciated.

`CONST strComputer = "."                            
AuditPath = "C:\Users\Pam\Desktop\Khalid.txt"
ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=khalid;Data Source=GRIMLEY"

Set objFSO=CreateObject("Scripting.FileSystemObject")                                                                           
Set objFile = objFSO.CreateTextFile(AuditPath,True)                                                                         
Set objWMIService = GetObject("winmgmts:" _                                                                                     
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")                                                          
Set colItems = objWMIService.ExecQuery ("Select * from Win32_Volume")
Set objConn = CreateObject ("ADODB.Connection")                                                         
d = date()                                      

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=khalid;Data Source=GRIMLEY"

DIM objWMIService, objItem, colItems            
DIM AuditPath, txt , strSQL , ConnectionString

txt = "Server" & vbtab & "DriveLetter" & vbtab & "DriveName" & vbtab & "Size" & vbtab & "Used" & vbtab & "Free" & vbtab & "% Free" & vbtab & "Date" & vbcrlf

Server = "ALL_IN_ONE_Final"

For Each objItem In colItems

    ObjFile.Write Server & "," 
    objFile.write objItem.DriveLetter & "," 
    ObjFile.Write objItem.Label & ","
    objFile.write Int(objItem.Capacity /1073741824)\1 & "," 
    objFile.write Int((objItem.Capacity - objItem.FreeSpace )/1073741824)\1 & "," 
    objFile.write Int (objItem.FreeSpace /1073741824)\1 & "," 
    objFile.write ((objItem.FreeSpace/objItem.Capacity) * 100)\1 & "," & vbcrlf
next  

WScript.Sleep 8000


Set objConn = CreateObject ("ADODB.Connection")

objConn.Open ConnectionString

    strSQL = strSQL & " BULK INSERT Server_Space"
    strSQL = strSQL & " FROM 'C:\Users\Pam\Desktop\Khalid.txt' with"
    strSQL = strSQL & " ( FIELDTERMINATOR =',', ROWTERMINATOR = '0x0a')"

objConn.Execute strSQL
objConn.Close

WScript.Quit()`
0

There are 0 answers