0

I am having trouble with this VBA script and it seems to keep adding funny characters (i.e.) every time it reads the .SQL file. I believe this is due to encoding.

 Option Explicit

Sub SomeExtract()

With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .Calculation = xlCalculationManual: .EnableEvents = False
End With

Dim strSQL As String, filePath As String, fileSQL As Integer, row As String
Dim connSQL As ADODB.Connection, serverName As String, databaseName As String, userID As String, userPassword As String, rs As ADODB.Recordset

fileSQL = FreeFile
strSQL = ""
filePath = Application.ThisWorkbook.Path & "\somesql.sql"

Open filePath For Input As fileSQL

Do Until EOF(fileSQL)
    Line Input #fileSQL, row
    strSQL = strSQL & row & vbNewLine
Loop

Close #fileSQL

serverName = "someserver"
databaseName = "somedb"

Set connSQL = New ADODB.Connection
Set rs = New ADODB.Recordset

connSQL.Open "Provider=SQLOLEDB;Server=" & serverName & ";Database=" & databaseName & _
";Trusted_connection=yes;"

rs.Open strSQL, connSQL, adOpenStatic

With ThisWorkbook.Sheets("test").Range("A1:Z1000000")
    .ClearContents
    .CopyFromRecordset rs
End With

With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .EnableEvents = True
End With

 ExitPoint:
    With Application
       .ScreenUpdating = True: .DisplayAlerts = True: .Calculation = 
       xlCalculationAutomatic: .EnableEvents = True
   End With

    Set rs = Nothing
    Exit Sub


ErrHandler:
    With ThisWorkbook
       .Sheets("Control").Range("B1").Value = Err.Description
        .Sheets("Control").Range("C1").Value = strSQL
    End With

Resume ExitPoint

End Sub

The error message says "Run-time error '-2147217900 (80040e14)': Incorrect syntax near '>>';". The SQL file doesn't have >> or any other characters as shown on the error message. In fact, I have replaced the .sql to only contain a straight SELECT statement. But I still seeing the funny characters being added when it is being read by Excel VBA (e.g. select top 100 * from test" instead of "select top 100 * from test").

Erik A
  • 31,639
  • 12
  • 42
  • 67
ArsedianIvan
  • 369
  • 2
  • 6
  • 20
  • 1
    If you [Debug] the error, the highlighted `rs.Open` statement tells you that the error happened executing `strSQL`, and so "Incorrect syntax near '>>'" could only mean that `strSQL` isn't valid SQL content ;-) I'd fire up the *immediate pane* (Ctrl+G), and do `?strSQL` to get me its literal value, where I'd look for the offending '>>' tokens. Does the .sql file run as-is in SQL Server Management Studio? – Mathieu Guindon May 02 '18 at 02:08
  • I actually thought it was that initially, but then I replaced strSQL to simply a straight select statement = "Select * from someTable" and I keep seeing the same error message. – ArsedianIvan May 02 '18 at 03:15
  • You do not open the RecorSet, you already opened the Connection. rs = connSQL.Execute(strSQL) Edit: However you do close the rs.Close when done. – Ricardo A May 02 '18 at 03:29
  • @RicardoA that is correct, however wouldn't make a difference; `rs.Open` is perfectly legal.. there's just too many ways to do the same thing with ADODB: I'd say you do not execute off the connection, you make a parameterized command ;-) – Mathieu Guindon May 02 '18 at 04:06
  • 1
    There's gotta be a `>>` *somewhere*... try `for i = 1 to connSQL.Errors.Count - 1 : ?connSQL.Errors(i).Description : next` in the immediate pane while in break mode after getting that error. Maybe there's more information there. – Mathieu Guindon May 02 '18 at 04:13
  • Yes you are right, for some strange reasons when the SQL file being read into excel, it adds the following leading characters that do not even exist on the SQL script file "" – ArsedianIvan May 02 '18 at 04:47
  • Mathiue and @Ricardo A, I've edited this question, the root cause seems to be how Excel VBA reads the SQL statement with these funny leading characters (i.e. ) from the .SQL file – ArsedianIvan May 02 '18 at 05:12
  • 1
    That's likely a UTF-8 BOM, i.e. you have encoding issues. Open up the .sql file in Notepad++ and change the encoding to ANSI. – Mathieu Guindon May 02 '18 at 05:22
  • Possibly, opening the file in plain old Notepad, deleting the junk, and then saving, might just fix it. – Mathieu Guindon May 02 '18 at 05:29
  • Some context: https://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom – Andre May 02 '18 at 07:10

0 Answers0