Using SQL Server various versions 2016+
I am working on a set of import routines and need to create dynamic format files as part of new requirements handed down from above as part of a larger project.
I have an example where I can create the format file via BCP, however when I attempt to use it, the import fails
Msg 4866, Level 16, State 17, Line 45
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 45
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
This does not happen when I import the file without a format file. The format file appears to be generated correctly, with tab delimitation and standard row terminator.
14.0
19
1 SQLNCHAR 8 0 "\t" 1 Command Latin1_General_CI_AS
2 SQLNCHAR 2 100 "\t" 2 Source Latin1_General_CI_AS
3 SQLNCHAR 8 0 "\t" 3 CompanyImportID Latin1_General_CI_AS
4 SQLNCHAR 2 100 "\t" 4 AccountNo Latin1_General_CI_AS
5 SQLNCHAR 2 510 "\t" 5 Profile Latin1_General_CI_AS
6 SQLINT 1 4 "\t" 6 CompanyActive ""
7 SQLNCHAR 2 200 "\t" 7 CompanyName Latin1_General_CI_AS
8 SQLNCHAR 2 510 "\t" 8 Admin_Email_Address Latin1_General_CI_AS
9 SQLNCHAR 2 100 "\t" 9 PaymentMethod Latin1_General_CI_AS
10 SQLINT 1 4 "\t" 10 DefaultStockLocation ""
11 SQLINT 1 4 "\t" 11 ChargeCompanyVAT ""
12 SQLNCHAR 2 20 "\t" 12 DefaultCurrency Latin1_General_CI_AS
13 SQLINT 1 4 "\t" 13 EnforcePORef ""
14 SQLINT 1 4 "\t" 14 FixedDeliveryAddress ""
15 SQLINT 1 4 "\t" 15 SendOrderConfirmations ""
16 SQLNCHAR 2 100 "\t" 16 DefaultPriceCode Latin1_General_CI_AS
17 SQLNCHAR 2 100 "\t" 17 SpecialPriceCode Latin1_General_CI_AS
18 SQLNCHAR 2 510 "\t" 18 DivCode1 Latin1_General_CI_AS
19 SQLNCHAR 2 510 "\n" 19 DivCode2 Latin1_General_CI_AS
The usual advice on setting the row terminators has been followed. Sample code for generating the format file and reading in are as follows:
-- Setup tables
DROP TABLE IF EXISTS dba.companies
CREATE TABLE [dba].[companies] ( [Command] [NVARCHAR](MAX) NULL
, [Source] [NVARCHAR](50) NULL
, [CompanyImportID] [NVARCHAR](MAX) NULL
, [AccountNo] [NVARCHAR](50) NULL
, [Profile] [NVARCHAR](255) NULL
, [CompanyActive] [INT] NULL
, [CompanyName] [NVARCHAR](100) NULL
, [Admin_Email_Address] [NVARCHAR](255) NULL
, [PaymentMethod] [NVARCHAR](50) NULL
, [DefaultStockLocation] [INT] NULL
, [ChargeCompanyVAT] [INT] NULL
, [DefaultCurrency] [NVARCHAR](10) NULL
, [EnforcePORef] [INT] NULL
, [FixedDeliveryAddress] [INT] NULL
, [SendOrderConfirmations] [INT] NULL
, [DefaultPriceCode] [NVARCHAR](50) NULL
, [SpecialPriceCode] [NVARCHAR](50) NULL
, [DivCode1] [NVARCHAR](255) NULL
, [DivCode2] [NVARCHAR](255) NULL )
-- Build format file
DECLARE @strSQL NVARCHAR(MAX)
SELECT @strSQL = N'EXEC xp_cmdshell ''bcp ' + DB_NAME () + N'.dba.companies format nul -n -f "c:\examples\1_Companies-OK.fmt" -t "\t" -r "0x0a" -T'''
EXEC sp_executesql @stmt = @strSQL
-- Read without format file - works fine
BULK INSERT dba.companies
FROM 'c:\examples\1_Companies-OK.txt'
WITH ( FIRSTROW = 2
, ERRORFILE = 'c:\examples\1_Companies-OK_errors.txt'
, FIELDTERMINATOR = '\t'
, ROWTERMINATOR = '0x0a'
, CODEPAGE = 65001 )
SELECT *
FROM dba.companies
GO
-- Read with format file - unexpected EOF
BULK INSERT dba.companies
FROM 'c:\examples\1_Companies-OK.txt'
WITH ( FIRSTROW = 2
, ERRORFILE = 'c:\examples\1_Companies-OK_errors.txt'
, FORMATFILE = 'c:\examples\1_Companies-OK.fmt'
, CODEPAGE = 65001 )
SELECT *
FROM dba.companies
I have also tried using the XML format files, i.e.
bcp LatestCode.dba.companies format nul -n -f "c:\examples\1_Companies-OK.fmt" -x -r "0x0a" -T
with that however it generates a seperate line at the very end of the record definition without an ID field which bulk insert registers as being a required attribute.
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="8" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="7" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="8" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="9" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="10" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="11" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="12" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="20" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="13" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="14" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="15" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="16" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="17" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="18" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="19" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD xsi:type="NCharTerm" TERMINATOR="\n" MAX_LENGTH="2" COLLATION="Latin1_General_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Command" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="Source" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="CompanyImportID" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="AccountNo" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="Profile" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="6" NAME="CompanyActive" xsi:type="SQLINT"/>
<COLUMN SOURCE="7" NAME="CompanyName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="8" NAME="Admin_Email_Address" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="9" NAME="PaymentMethod" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="10" NAME="DefaultStockLocation" xsi:type="SQLINT"/>
<COLUMN SOURCE="11" NAME="ChargeCompanyVAT" xsi:type="SQLINT"/>
<COLUMN SOURCE="12" NAME="DefaultCurrency" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="13" NAME="EnforcePORef" xsi:type="SQLINT"/>
<COLUMN SOURCE="14" NAME="FixedDeliveryAddress" xsi:type="SQLINT"/>
<COLUMN SOURCE="15" NAME="SendOrderConfirmations" xsi:type="SQLINT"/>
<COLUMN SOURCE="16" NAME="DefaultPriceCode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="17" NAME="SpecialPriceCode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="18" NAME="DivCode1" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="19" NAME="DivCode2" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
I will try and link to data file too if I can find a way.
Any advice greatfully received.