We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs
BULK INSERT. We are trying to capture misformatted rows and continue processing using
ERRORFILE. The problem is that the
ERRORFILE does not get created or updated.
The SQL Server command is running as account NT Service\MSSQL$SQLEXPRESSSERVER. The directory where we are trying to write the
ERRORFILE is on the C: drive on the same physical box and has account MSSQL$SQLEXPRESSSERVER with full control. Is there some additional flag or permissions that need to be changed that will allow the creation of the
The error is :
Msg 245, Level 16, State 1, Line 6 Conversion failed when converting the nvarchar value '""' to data type int.
Shouldn't it log this row into the ERRORFILE and keep processing?
this is a snippet from the stored proc:
SET @paramDefinition = N'@inputFileName as nvarchar(500), @formatFileName as nvarchar(500), @conEdBcpErrorLogs as nvarchar(500), @firstRow as Int'; SET @sql2 = N'BULK INSERT [emioutagesbcp] FROM "' + @inputFileName + '" WITH ( FIRSTROW = ' + Convert(VARCHAR(10),@firstRow) + ', FORMATFILE = ''' + @formatFileName + ''', ERRORFILE = ''' + @conEdBcpErrorLogs + ''', FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\r\n'', MAXERRORS = 100)'; --PRINT @sql2 EXECUTE sp_executesql @sql2, @paramDefinition, @inputFileName = @inputFileName, @formatFileName = @formatFileName, @conEdBcpErrorLogs = @conEdBcpErrorLogs, @firstRow = @firstRow;