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;
  • 2
    You're mixing a straightforward dynamic SQL statement with a parameterized one. As you have it right now, just using EXEUTE sp_executesql @sql2; as the last line should work; the string you've built has no parameters. If you want to use a parameterized query, you need to actually have (for instance) the actual text @formatFileName in the SQL query itself, not concatenate in the contents of the matching variable name. Note that some things can't be treated as parameters; if that's a problem, then just using the dynamic SQL (as I said first) should work. – RDFozz Jul 18 '17 at 18:46
  • You make a good point, and I have tested removing the parameterized query, but this does not seem to be related to my original problem which is that SQL is unable to write the ERRORFILE. When it hits a bad row in the bulk file, it just stops, and no records are saved. The desired behavior is that it writes the bad record(s) to the ERRORFILE and continues processing. – G Temme Jul 18 '17 at 19:41
  • Do you get an error message? Also, uncomment the PRINT @sql2 line; make sure that the SQL statement is what you'd expect it to be. You may have to double-up the backslashes in the file name, so they arent' treated like escape characters (escaping a regular character just yields the regular character). Finally: per the docs, you will get an error if ERRORFILE already exists. It sounds like that's not the case, but it never hurts to double-check. – RDFozz Jul 18 '17 at 20:03
  • Yeah - confirmed that the ERRORFILE does not pre-exist. When I uncomment and execute from SSMS, this is the SQL statement and error: BULK INSERT [emioutagesbcp] FROM "C:\UploadtoSQL\ConEdEMI\TEST_CECONY_Planned_Outage_2017_07_13_09_25.txt" WITH ( FIRSTROW = 2, FORMATFILE = 'C:\UploadtoSQL\ConedEMIXML\ConedEMI.xml', ERRORFILE = 'C:\UploadtoSQL\ConEdBCPErrorLogs\err_TEST_CECONY_Planned_Outage_2017_07_13_09_25.txt', FIELDTERMINATOR = '|', ROWTERMINATOR = '\r\n', MAXERRORS = 100) – G Temme Jul 18 '17 at 20:10
  • and this is the error: Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the nvarchar value '""' to data type int. – G Temme Jul 18 '17 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.