Sample Header Ad - 728x90

BULK INSERT ERRORFILE Not Created

1 vote
0 answers
1121 views
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