Not all rows are imported using OPENROWSET in SQL Server

user_16222131 0
Viewed 388

I have a problem whit an import from a TXT file to a Table in SQL Server 2016, that fails import all rows. I'm trying to read the data using OPENROWSET statement, whit an XML to define the correct format of the columns in the file.

So I use this script to read the data:

select *
FROM OPENROWSET (BULK 'C:\SQL_DATA\VAR_REG_20190831.TXT', 
    FORMATFILE = 'C:\SQL_DATA\MR_VAR_REG.xml'   AS t1;

it start, but at 18954 rows it finish successfully, but the file are made by 30000+ rows. The problem isn't in the data, because if i copy a recors that are imported, in the position 18.954, previous and next for secure the problem persist.

it's like there is a limit of rows to import. Please someone can explain me why SQL do that?

openrowset import sql-server-2016 sql-server sql
1 Answers

You can try to add two additional parameters that will help to troubleshoot the issue:

  • ERRORFILE
  • MAXERRORS

-- UPDATE --

You can try to make two calls. First, SQL #1. And after that SQL #2. The goal is bypass a supposedly bad line #18955 in the file.

SQL #1

SELECT *
FROM OPENROWSET (BULK 'C:\SQL_DATA\VAR_REG_20190831.TXT'
    , FORMATFILE = 'C:\SQL_DATA\MR_VAR_REG.xml'  
    , ERRORFILE = 'C:\SQL_DATA\MR_VAR_REG.err'
    , MAXERRORS = 100
    ) AS t1;

SQL #2

Here is a trick to bypass supposedly 'bad' line in the file.

SELECT *
FROM OPENROWSET (BULK 'C:\SQL_DATA\VAR_REG_20190831.TXT'
    , FORMATFILE = 'C:\SQL_DATA\MR_VAR_REG.xml'  
    , ERRORFILE = 'C:\SQL_DATA\MR_VAR_REG.err'
    , MAXERRORS = 100
    , FIRSTROW = 18956 -- real data starts on that row
    ) AS t1;
user_19323110
Related