Jump to content

[SOLVED] SQL bulk insert error

Montfrooij
 Share

Recommended Posts

Montfrooij Seeker

Montfrooij

Posted
Posted (edited)

Hi,

I am trying to find a way to insert data extracted from a csv file into a SQL database and made a script with AutoIT.

Using the many wonderful examples in this forum I got pretty far (for a non programmer...), but now I am stuck.

Explanation of the problem:

I am executing a BULK INSERT statement from the program, which looks like:

BULK INSERT Bewerkt

FROM 'c:\bewerkt.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

It worked fine when the location of the .CSV file was on the local HD (local as where the SQL server resides).

I made a variable which stores the location of the file using the 'FileOpenDialog' and still the execution of the BULK insert statement worked fine. 

If _SQL_Execute(-1,"BULK INSERT Bewerkt FROM '"& $izFile &"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')") = $SQL_ERROR then
                    Msgbox(0 + 16 +262144,"Error4",_SQL_GetErrMsg())

Where _SQL_EXECUTE is a function that I found on this forum and $izFile is the variable which is set using the 'FileOpenDialog'.

#include-once
; #INDEX# ========================================================================
; Title .........: _SQL.au3
; AutoIt Version : 3.2
; Language ......: English
; Description ...: Some SQL stuff to use with an MSDE database
; Author ........: Chris Lambert
; ================================================================================

; #VARIABLES# ====================================================================
Global $SQL_LastConnection ;  enables the use of -1 to access the last opened connection
Global $SQLErr ;  Plain text error message holder
Global $MSSQLObjErr ;  For COM error handler
Global Const $SQL_OK = 0 ;  Successful result
Global Const $SQL_ERROR = 1 ;  SQL error
; ==============================================================================

; #FUNCTION# ===================================================================
; Name ..........: _SQL_RegisterErrorHandler
; Description ...: Register COM error handler
; Syntax.........:  _SQL_RegisterErrorHandler($Func = "_SQL_ErrFunc")
; Parameters ....: $Func      - String variable with the name of a user-defined COM error handler defaults to the _SQL_ErrFunc()
; Return values .: On Success - Returns $SQL_OK
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......: AutoIt3 V3.2 or higher
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_RegisterErrorHandler($Func = "_SQL_ErrFunc")
    $SQLErr = ""
    If ObjEvent("AutoIt.Error") = "" Then
        $MSSQLObjErr = ObjEvent("AutoIt.Error", $Func)
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "An Error Handler is already registered"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
EndFunc   ;==>_SQL_RegisterErrorHandler

; #FUNCTION# ===================================================================
; Name ..........: _SQL_UnRegisterErrorHandler()
; Description ...: Disable a registered error handler
; Syntax.........: _SQL_UnRegisterErrorHandler()
; Parameters ....: None
; Return values .: On Success - Returns $SQL_OK
;                  On Failure - None
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......: AutoIt3 V3.2 or higher
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_UnRegisterErrorHandler()
    
    $SQLErr = ""
    $MSSQLObjErr = ""
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_UnRegisterErrorHandler

; #FUNCTION# ===================================================================
; Name ..........: _SQL_Startup
; Description ...: Creates ADODB.Connection object
; Syntax.........:  _SQL_Startup()
; Parameters ....: None
; Return values .: On Success - Returns Object handle
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_Startup()
    
    $SQLErr = ""
    Local $adCN = ObjCreate("ADODB.Connection")
    If IsObj($adCN) Then
        $SQL_LastConnection = $adCN
        Return SetError($SQL_OK, 0, $adCN)
    Else
        $SQLErr = "Failed to Create ADODB.Connection object"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
EndFunc   ;==>_SQL_Startup

; #FUNCTION# ===================================================================
; Name ..........: _SQL_Connect
; Description ...: Starts a Database Connection
; Syntax.........:  _SQL_Connect($ADODBHandle,$server, $db, $username, $password)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $server   - The server to connect to.
;                  $db    - The database to open.
;                  $username    - username for database access.
;                  $password    - password for database user.
; Return values .: On Success   - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password)
    
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials
    
    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_Connect

; #FUNCTION# ===================================================================
; Name ..........: _SQL_JetConnect
; Description ...: Starts a Database Connection to a Jet Database
; Syntax.........:  _SQL_JetConnect($ADODBHandle,$sFilePath1)
; Parameters ....: $ADODBHandle -  ADODB.Connection handle
;                  $sFilePath1  - Path to Jet Database file
; Return values .: On Success   - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_JetConnect($ADODBHandle, $sFilePath1)
    
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & $sFilePath1 & ";")
    If @error Then
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    
    Return SetError($SQL_OK, 0, $SQL_OK)
EndFunc   ;==>_SQL_JetConnect

; #FUNCTION# ===================================================================
; Name ..........: _SQL_AccessConnect
; Description ...: Starts a Database Connection to an Access Database
; Syntax ........: _SQL_AccessConnect($ADODBHandle,$sFilePath1)
; Parameters ....: $ADODBHandle - ADODB.Connection handle - Optional
;                  $sFilePath1  - Path to an Access Database file
; Return values .: Success      - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......: no
; ================================================================================
Func _SQL_AccessConnect($ADODBHandle = -1, $sFilePath1 = "")
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & $sFilePath1 & ";")
    If @error Then
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_AccessConnect

; #FUNCTION# ===================================================================
; Name ..........: _SQL_Close
; Description ...: Closes an open ADODB.Connection
; Syntax.........:  _SQL_Close ($ADODBHandle = -1)
; Parameters ....: $ADODBHandle - Optional Database Handle
; Return values .: On Success   - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_Close($ADODBHandle = -1)
    
    $SQLErr = ""
    
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.Close
    If $ADODBHandle = $SQL_LastConnection Then $SQL_LastConnection = ""
    
    Return SetError($SQL_OK, 0, $SQL_OK)

EndFunc   ;==>_SQL_Close

; #FUNCTION# ===================================================================
; Name ..........: _SQL_Execute()
; Description ...: Executes an SQL Query
; Syntax.........:  _SQL_Execute([ $hConHandle = -1[,$vQuery = "" ]])
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                  $vQuery     - SQL Statement to be executed
; Return values .: On Success  - Returns a query handle
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_Execute($hConHandle = -1, $vQuery = "")
    
    $SQLErr = ""
    Local $hQuery
    
    If $hConHandle = -1 Then $hConHandle = $SQL_LastConnection
    $hQuery = $hConHandle.Execute($vQuery)

    If @error Then
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    Else
        Return SetError($SQL_OK, 0, $hQuery)
    EndIf
EndFunc   ;==>_SQL_Execute

; #FUNCTION# ===================================================================
; Name ..........: _SQL_GetErrMsg
; Description ...: Get SQL error as text
; Syntax.........:  _SQL_GetErrMsg()
; Parameters ....:                None
; Return values .: On Success - Returns the text string from $SQLErr
;                  On Failure - None
; Author ........: Chris Lambert
; Modified ......: Stephen Podhajecki (eltorro)
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_GetErrMsg()
    ;added temp var to return and clear $SQLErr
    ;returns the current errmsg and clears it.
    Local $SQLErr_TMP = $SQLErr
    $SQLErr = ""
    Return SetError($SQL_OK, 0, $SQLErr_TMP)
EndFunc   ;==>_SQL_GetErrMsg

; #FUNCTION# ===================================================================
; Name ..........: _SQL_GetTable2d()
; Description ...: Passes Out a 2Dimensional Array Containing Tablenames and Data of Executed Query
; Syntax.........:  _SQL_GetTable2D($hConHandle, $vQuery, ByRef $aResult, ByRef $iRows, ByRef $iColumns)
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                  $vQuery     - SQL Statement to be executed
;                  $aResult    - Passes out the Result
;                  $iRows      - Passes out the amount of 'data' Rows
;                  $iColumns   - Passes out the amount of Columns
; Return values .: On Success  - Returns $SQL_OK
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......: Stephen Podhajecki (eltorro)
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_GetTable2D($hConHandle, $vQuery, ByRef $aResult, ByRef $iRows, ByRef $iColumns)
    
    $SQLErr = ""
    Local $i, $x, $y, $objquery
    
    $iRows = 0
    $iColumns = 0
    ;sp mod removed handle check here use function.
    $objquery = _SQL_Execute($hConHandle, $vQuery)
    ;end mod
    If @error Then
        $SQLErr = "Query Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $objquery.eof Then
        $SQLErr = "Query has no data"
        $objquery = 0 ;sp mod
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    With $objquery

        $aResult = .GetRows()
        
        If IsArray($aResult) Then
            $iColumns = UBound($aResult, 2)
            $iRows = UBound($aResult)

            ReDim $aResult[$iRows + 1][$iColumns];Adjust the array to fit the column names and move all data down 1 row
            
            For $x = $iRows To 1 Step -1
                For $y = 0 To $iColumns - 1
                    $aResult[$x][$y] = $aResult[$x - 1][$y]
                Next
            Next
            ;Add the coloumn names
            For $i = 0 To $iColumns - 1 ;get the column names and put into 0 array element
                $aResult[0][$i] = .Fields($i).Name
            Next
            
        Else
            $SQLErr = "Unable to retreive data"
            $objquery = 0 ;sp mod
            Return SetError($SQL_ERROR, 0, $SQL_ERROR)
        EndIf;IsArray()

    EndWith
    $objquery = 0
    Return SetError($SQL_OK, 0, $SQL_OK)
EndFunc   ;==>_SQL_GetTable2D

; #FUNCTION# ===================================================================
; Name ..........: _SQL_FetchNames()
; Description ...: Read out the Tablenames of a _SQL_Query() based query
; Syntax.........:  _SQL_FetchNames($hQuery,ByRef $aNames)
; Parameters ....: $hQuery    - Query Handle Generated by _SQL_Execute()
;                  $aNames    - variable to store the Table Names
; Return values .: On Success - Returns $SQL_OK
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_FetchNames($hQuery, ByRef $aNames)
    
    Local $i, $SQL_Delim = "¬&~"
    Local $iDelLen = StringLen($SQL_Delim)
    $SQLErr = ""
    
    If Not IsObj($hQuery) Then
        $SQLErr = "Invalid Query Handle"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $aNames = ""
    For $i = 0 To $hQuery.Fields.Count - 1 ;get the column names and put into 0 array element
        $aNames &= $hQuery.Fields($i).Name & $SQL_Delim
    Next
    
    If StringRight($aNames, $iDelLen) = $SQL_Delim Then $aNames = StringTrimRight($aNames, $iDelLen)
    
    $aNames = StringSplit($aNames, $SQL_Delim, 3)
    
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_FetchNames

; #FUNCTION# ===================================================================
; Name ..........: _SQL_GetTable()
; Description ...: Passes Out a 1Dimensional Array Containing Tablenames and Data of Executed Query
; Syntax.........:  _SQL_GetTable($hConHandle, $vQuery, ByRef $aData, ByRef $iRows, ByRef $iColumns)
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                  $vQuery     - SQL Statement to be executed
;                  $aResult    - Passes out the Result
;                  $iRows      - Passes out the amount of 'data' Rows
;                  $iColumns   - Passes out the amount of Columns
; Return values .: On Success  - Returns $SQL_OK
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......: Stephen Podhajecki (eltorro)
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_GetTable($hConHandle, $vQuery, ByRef $aData, ByRef $iRows, ByRef $iColumns)
    
    Local $i, $objquery, $aNames
    $SQLErr = ""
    $iRows = 0
    $iColumns = 0
    
    $objquery = _SQL_Execute($hConHandle, $vQuery)
    
    If @error Then
        $SQLErr = "Query Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $objquery.eof Then
        $SQLErr = "Query has no data"
        $objquery = 0 ;sp mod
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    _SQL_FetchNames($objquery, $aNames)
    
    $iColumns = UBound($aNames)
    ReDim $aData[$iColumns + 1]
    
    $aData[0] = $iColumns
    
    For $i = 0 To $iColumns - 1
        $aData[$i + 1] = $aNames[$i]
    Next
    
    $aNames = 0
    
    While Not $objquery.eof
        $iRows += 1
        For $i = 0 To $objquery.Fields.Count - 1
            ReDim $aData[$aData[0] + 2]
            $aData[0] += 1
            $aData[$aData[0]] = $objquery.Fields($i).Value
        Next
        $objquery.MoveNext; Move to next row
    WEnd
    $objquery = 0 ;sp mod
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_GetTable

; #FUNCTION# ===================================================================
; Name ..........: _SQL_FetchData()
; Description ...: Fetches 1 Row of Data from an _SQL_Execute() based query
; Syntax.........:  _SQL_FetchData($hQuery,ByRef $aRow)
; Parameters ....: $hQuery    - Queryhandle passed out by _SQL_Execute()
;                  $aRow      - A 1 dimensional Array containing a Row of Data
; Return values .: On Success - Returns $SQL_OK
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_FetchData($hQuery, ByRef $aRow)
    
    Local $i, $SQL_Delim = "¬&~"
    Local $iDelLen = StringLen($SQL_Delim)
    $SQLErr = ""
    
    If Not IsObj($hQuery) Then
        $SQLErr = "Invalid Query Handle"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $hQuery.EOF Then
        $SQLErr = "End of Data Stream"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $aRow = ""
    For $i = 0 To $hQuery.Fields.Count - 1
        $aRow &= $hQuery.Fields($i).Value & $SQL_Delim
    Next

    If StringRight($aRow, $iDelLen) = $SQL_Delim Then $aRow = StringTrimRight($aRow, $iDelLen)
    $hQuery.MoveNext; Move to next row
    $aRow = StringSplit($aRow, $SQL_Delim, 3)
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_FetchData

; #FUNCTION# ===================================================================
; Name ..........: _SQL_QuerySingleRow()
; Description ...: Read out the first Row of the Result from the Specified query
; Syntax.........:  _SQL_QuerySingleRow($hConHandle, $sSQL, ByRef $aRow)
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database.
;                  $sSQL       - SQL Statement to be executed.
;                  $aRow       - Array to hold return results.
; Return values .: On Success  - Returns $SQL_OK
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......: $SQLErr will already be set by _SQL_GetTable2D
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_QuerySingleRow($hConHandle, $sSQL, ByRef $aRow)
    
    Local $aResult, $iRows, $iColumns, $Ret, $i
    $aRow = ""
    Dim $aRow[1]
    $Ret = _SQL_GetTable2D($hConHandle, $sSQL, $aResult, $iRows, $iColumns)
    If $Ret = $SQL_ERROR Then SetError($SQL_ERROR, 0, $SQL_ERROR) ;$SQLErr will already be set by _SQL_GetTable2D
    
    If $Ret = $SQL_OK And UBound($aResult, 0) > 0 Then
        ReDim $aRow[UBound($aResult, 2)]
        For $i = 0 To UBound($aResult, 2) - 1
            $aRow[$i] = $aResult[1][$i]
        Next
    EndIf
    
    Return SetError($SQL_OK, 0, $SQL_OK)
    
EndFunc   ;==>_SQL_QuerySingleRow

; #FUNCTION# ===================================================================
; Name ..........: _SQL_GetTableAsString
; Description ...: Passes Out a string of results
; Syntax.........:  _SQL_GetTableAsString( $hConHandle, $vQuery, ByRef $vStr[, $delim= "|"[, $ReturnColumnNames = 1]])
; Parameters ....: $hConHandle        - An Open Database, Use -1 To use Last Opened Database
;                  $vQuery            - SQL Statement to be executed
;                  $vStr              - Passes out the Result
;                  $delim= "|"        - The deliminator to use between columns
;                  $ReturnColumnNames - Use 1 to show column names and 0 without
; Return values .: On Success         - Returns $SQL_OK
;                  On Failure         - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......: Stephen Podhajecki (eltorro)
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================

Func _SQL_GetTableAsString($hConHandle, $vQuery, ByRef $vStr, $delim = "|", $ReturnColumnNames = 1)
    
    $SQLErr = ""
    Local $i, $objquery
    
    $objquery = _SQL_Execute($hConHandle, $vQuery)
    
    If @error Then
        $SQLErr = "Query Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $objquery.eof Then
        $SQLErr = "Query has no data"
        $objquery = 0 ;sp mod
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    With $objquery
        If $ReturnColumnNames Then
            For $i = 0 To .Fields.Count - 1 ;get the column names and put into 0 array element
                $vStr &= .Fields($i).Name & $delim
            Next
            If StringRight($vStr, 1) = $delim Then $vStr = StringTrimRight($vStr, 1)
            $vStr &= @CRLF
        EndIf
        
        While Not .EOF
            For $i = 0 To .Fields.Count - 1
                $vStr &= .Fields($i).Value & $delim
            Next
            If StringRight($vStr, 1) = $delim Then $vStr = StringTrimRight($vStr, 1)
            $vStr &= @CRLF
            .MoveNext; Move to next row
        WEnd
    EndWith
    $objquery = 0 ;sp mod
    Return SetError($SQL_OK, 0, $SQL_OK)
EndFunc   ;==>_SQL_GetTableAsString

; #FUNCTION# ===================================================================
; Name ..........: _SQL_ErrFunc
; Description ...: Autoit Error handler function
; Syntax ........: _SQL_ErrFunc()
; Parameters ....: None.
; Return values .: $SQLErr and @error set to $SQL_ERROR
; Author ........:
; Modified.......:
; Remarks .......: COM error handler function.
; Related .......:
; Link ..........:
; Example .......: no
; ================================================================================
Func _SQL_ErrFunc()
    Local $HexNumber = Hex($MSSQLObjErr.number, 8)
    $SQLErr = "err.description is: " & @TAB & $MSSQLObjErr.description & @CRLF & _
            "err.windescription:" & @TAB & $MSSQLObjErr.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $MSSQLObjErr.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $MSSQLObjErr.scriptline & @CRLF & _
            "err.source is: " & @TAB & $MSSQLObjErr.source & @CRLF & _
            "err.helpfile is: " & @TAB & $MSSQLObjErr.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $MSSQLObjErr.helpcontext
    ConsoleWrite("###############################" & @CRLF & $SQLErr & "###############################" & @CRLF)
    SetError($SQL_ERROR, 0, $SQLErr)
EndFunc   ;==>_SQL_ErrFunc

Now, while trying to open a .CSV file (still running the script from the SQL server) that is located on a network drive (on a different server than the SQL server), still executing the script from the SQL server, I am getting an error.

"cannot bulk load because the file "x:\bewerkt.csv could not be opened. Operating system error code 3 error not found"

Strangely I can open the SQL management studio and execute the query without errors (and with the right results)

BULK INSERT Bewerkt

FROM 'x:\bewerkt.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

I included a messagebox to the script showing the path and full query that is passed and it looks the same.

Next I tried to map a local folder (on the SQL server) to a network drive (so C:\AutoIT was mapped as X:\) and still the problem remained the same (the script generated an error and the management studio execution worked fine).

I did some research about BULK INSERT and the error code 3 and found out that the SQL service must run under a network logon to be able to open files that are located on 'remote' servers.

This seems to be the case (I checked services.msc and the SQL server service is running under xx\administrator logon).

So my question is:

How can I get my script to use a BULK INSERT statement from a .CSV file that is located on another server than the SQL server?

Thanks in advance.

Montfrooij

Edited by Montfrooij
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

Try using the UNC path instead of mapped drive: i.e. '\\ServerName\ShareName\SubDir\FileName.csv'

MSSQL, at least, seems to require it. From MSDN: BULK INSERT (Transact-SQL):

' data_file '

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt.

:mellow:

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...