SQLCMD utility generate rejected rows to flat file

Question

Currently I am using SQLCMD Utility to load the CSV data to SQL Server. Below is my command which was executed in command prompt to load the data:

sqlcmd -Usa -Pxxx -S192.168.1.223,49546 -dlocal -i"/test.sql" -o"/test.log"

I have also copied my test.sql file contents for your reference:

SET NOCOUNT ON 
BULK INSERT test FROM 
"\\192.168.1.223\test.csv"
WITH
(
MAXERRORS = 1000000,
CODEPAGE = 1251,
FIELDTERMINATOR = '~%',
ROWTERMINATOR = '0x0a'
)
GO
SELECT CONVERT(varchar,@@ROWCOUNT) + ' rows affected'
GO

The insert operation is working fine with the above process. But my concern is, in case of any errors due to data type or data length the row is rejected and I am unable to trace the particular row.

Each time I have to look at the log file for the rejected row number and the data file to check the corresponding row.

Is there any option to generate the error/rejected row to another file, as like we have in ORACLE - SQLPLUS Utility to generate bad file?

Answer 1

I think the option your are looking for is not in sqlcmd, but in BULK INSERT:

ERRORFILE ='file_name'

Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. These rows are copied into this error file from the data file "as is."