SQL – sysadminben

This is vbscript code I used to clean up massive SQL files that had random corruption that appeared as long strings of repeated characters,

'Written by Ben Penney https://sysadminben.wordpress.com
'Script requires sub-folders CLEAN, BAD and ERROR to be in the script path

Option Explicit

Dim objShell, objFSO
Set objShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objFile, objInput, objOutput, objBad, objError
Dim intLineNum, strRead, blnLineGood, CharNum, CharNum2
For Each objFile in objFSO.GetFolder(objShell.CurrentDirectory).Files
  If LCase(Right(objFile.Name, 3)="sql") Then

    Set objInput = objFSO.OpenTextFile(objFile.Name)
    Set objOutput = objFSO.CreateTextFile("CLEAN\" & objFile.Name)
    Set objBad = objFSO.CreateTextFile("BAD\" & objFile.Name)
    Set objError = objFSO.CreateTextFile("ERROR\" & objFile.Name)

    intLineNum = 1
    Do While Not objInput.AtEndOfStream
      strRead = objInput.ReadLine
      blnLineGood = True

      ' ---------- START: Check for repeat characters ----------
      For CharNum = 1 to Len(strRead) - 102
        If Mid(strRead,CharNum,1) <> " " Then
          For CharNum2 = 1 to 100
            blnLineGood = False   ' Assume bad line unless we prove it is not
            If Mid(strRead,CharNum,1) <> Mid(strRead,CharNum+CharNum2,1) Then
              blnLineGood = True   ' Line is not bad
              Exit For
            End If
          Next
          If blnLineGood = False Then Exit For
        End If
      Next
      ' ----------- END: Check for repeat characters -----------

      ' ---------- START: Check for SQL syntax ----------
      If Left(strRead,6) <> "INSERT" And Right(strRead,2) <> ")," And Right(strRead,2) <> ");" Then blnLineGood = False
      If Left(strRead,6) <> "INSERT" And Left(strRead,1) <> "(" Then blnLineGood = False
      ' ----------- END: Check for SQL syntax -----------

      ' ---------- START: Write line to output files. Catch errors. ----------
      On Error Resume Next
      Err.Clear
      If blnLineGood = True Then
        objOutput.WriteLine strRead
      Else
        objBad.WriteLine strRead
      End If
      If Err.Number <> 0 Then objError.WriteLine "error writing line " & intLineNum
      On Error Goto 0
      ' ----------- END: Write line to output files. Catch errors. -----------

      intLineNum = intLineNum + 1
    Loop

    objInput.Close
    objOutput.Close
    objBad.Close
    objError.Close
  End If

Next

Wscript.Echo "All Done."

**Script requires sub-folders CLEAN, BAD and ERROR to be in the script path**

It is pretty rough but basically it checks each character in each line for repeated characters (ignoring spaces). If it finds the same character repeated 100 times it will output that line to the BAD file.

Additionally it checks that each line starts in ( and ends in either ), or ); (excluding INSERT lines). If it fails these tests it will output the line to the BAD file otherwise the line goes into the CLEAN file.

The ERROR file is there to catch errors with writing the line to either file which seem to pop up occasionally. The line numbers of the failed line are recorded in this file.

Can be easily tweaked to suit any sort of syntax check you were interested in.

Thanks,
Ben

Tag: SQL

Clean up corrupted SQL files