Hello Peter and thanks for getting back to me. When I mean "stopped working" I was meaning in the sense that the imports no longer work.
I have this VBS script, When a file called actions.bea8c868-19f8-4168-9972-8e0b555d35a4csv is received, this VBS script runs to import the data in the csv file into the table. Now this did and still does work on our previous server, but I cant for the life figure out why it will not import on this new server
On Error Resume Next
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim resGuid
resGuid = WScript.Arguments(0)
Dim fileName
fileName = "actions." & resGuid & "csv"
'Message fileName
Dim file
If fso.FileExists(fileName) Then
Set file = fso.OpenTextFile(fileName, 1)
'Message "have file"
Dim colLine, resLine
colLine = file.ReadLine
resLine = file.ReadLine
file.close()
Set file = Nothing
Message colLine
Message resLine
colLine = Replace(colLine, """", "")
Dim shell
Set shell = CreateObject("WScript.Shell")
Dim cmd
cmd = "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlimport --local --user=root --password=Delgirl2015 new_db_2005 --fields-optionally-enclosed-by=\"" --fields-terminated-by=, --ignore-lines=1 --columns=" & colLine & " --verbose " & fileName
'Message cmd
Dim cmdMinus
cmdMinus = Replace(cmd, colLine, "")
'Dim resultFields
'resultFields = Split(resLine, ",")
'Dim resGuid
'resGuid = resultFields(9)
Message resGuid
Dim output
output = RunCommandAndGetResult(cmd)
Message("Warning..")
Dim warnings
warnings = Split(output, "Warnings: ")
Message warnings(1)
Dim warningCount
warningCount = Split(warnings(1))(0)
Message warningCount
' Check whether report is in database
cmd = "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql -e ""SELECT count(*) FROM actions WHERE reportGuid = '" & resGuid & "'"" --user=root --password=Delgirl2015 new_db_2005"
Message cmd
Dim selectCount
selectCount = RunCommandAndGetResult(cmd)
Dim count
count = Split(selectCount, "(*)")
Message count(1)
Dim numCount
numCount = Split(count(1), vbCrLf)
Message numCount(1)
Dim archiveFolder
Message archiveFile
If numCount(1) <> "1" Then
SendAlert "Import unsuccessful", resLine
Message "Failed"
archiveFolder = "Error"
ElseIf warningCount <> "0" Then
SendAlert "Import generated warnings, but report has been imported", resLine
Message "Warning"
archiveFolder = "Warning"
Else
Message "Success"
archiveFolder = "Done"
End If
Dim archiveFile
archiveFile = "AutoImport_" & archiveFolder & "\" & fileName
If fso.FileExists(archiveFile) Then
fso.DeleteFile(archiveFile)
End If
fso.moveFile fileName, archiveFile
Set fso = Nothing
End If
Function RunCommandAndGetResult(cmd)
Dim scriptExec
'Message cmdMinus
Set scriptExec = shell.Exec(cmd)
Do While scriptExec.Status = 0
WScript.Sleep 100
Loop
Dim output
If not scriptExec.StdOut.AtEndOfStream Then
output = scriptExec.StdOut.ReadAll()
End If
Message output
RunCommandAndGetResult = output
End Function
Public Sub SendAlert(subject, body)
Set oMessage = CreateObject("CDO.Message")
oMessage.From = "import_report@leymac.com"
oMessage.To = "pggrimes@hotmail.com"
oMessage.Subject = "Action Imported: " & subject
oMessage.Textbody = body
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail Server Address"
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/sendusername") = "Mail Server Username"
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Mail Server Password"
oMessage.Configuration.Fields.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 2626
oMessage.Configuration.Fields.Update
oMessage.Send
End Sub
Public Sub Message(text)
'MsgBox text
End Sub
The contents of the CSV file is this.
"externalPK","customerPK","customerId","locationPK","locationId","userPK","userId","template","formDate","dateTimeStarted","dateTimeMobileReleased","currentStatus","reportGuid","snapshotTrigger","reportStatus","scoreUN","previousScoreUN","Actions","TravelToJob","JobStartTime","R019","R002","R004","R001","Job_No","LoggedBy","JobSheet","CustomerEmail","CustomerRef","R055","R056","ContactEmail","R005","R007","R009","R011","R013","R015","R017","R006","R008","R010","R012","R014","R016","R018","R438","MUsed","R051","R003","R176","R075","SigReason","NoJobSheet","R054","R053","JobEndTime","CRB_No","R4500","R4510","R4520","IPA100","R4501","R4511","R4521","IPB100","R4502","R4512","R4522","IPC100","R4503","R4513","R4523","IPD100","R4504","R4514","R4524","IPE100","R4505","R4515","R4525","IPF100","R4506","R4516","R4526","IPG100","R4507","R4517","R4527","IPH100","R4508","R4518","R4528","IPI100","R4509","R4519","R4529","IPJ100"
"1000002_6_28___","1162","163","482","486","6","7","167","2019-05-23T08:05:11","2019-05-23T08:05:11","2019-05-23T08:07:08","9","bea8c868-19f8-4168-9972-8e0b555d35a4","Actions:Job_Received","9","-1","-1","","","","FAULT ON ONE HEATING BOILER","TEST SCHOOL","TEST CLIENT","EH 6312","1000002","TEST ENGINEER","TEST JOBSHEET","","CARETAKER","TESTNAME","","","Yes","Yes","N/A","N/A","Yes","No","No","N/A","N/A","N/A","N/A","N/A","N/A","N/A","","No","","2019-05-23T00:00:00","","","","No","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""
As for the error log, this is all that it displays
2019-05-23T06:51:06.345560Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-05-23T06:51:06.349113Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.15) starting as process 4672
2019-05-23T06:51:26.749421Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-05-23T06:51:26.841568Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.15' socket: '' port: 3306 MySQL Community Server - GPL.
2019-05-23T06:51:26.884634Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
Regards
Paul