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 ERRORFILE
?
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;
Asked by G Temme
(11 rep)
Jul 18, 2017, 06:32 PM
Last activity: Jul 18, 2017, 07:52 PM
Last activity: Jul 18, 2017, 07:52 PM