Asp Forum - Bulk insert error -- "Unknown version of format file"

Peter Nofelt

3/26/2007 10:37:00 PM

Hi all,

ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"

Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.

Any ideas? have you seen this before?

NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.

Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms1...

TO REPRODUCE:

* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))

* Data File Template:
Age<tab>Firstname<tab>Lastname<return>

* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/fo...
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance...
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:\datatest\exampledata-c.Dat'
WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');

Thanks in advanced for any feedback.

Cheers!

4 Answers

Uri Dimant

3/27/2007 8:16:00 AM

Peter
Take a look at this article
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/30/Preprocessing-data-before-bulk-i...

"Peter Nofelt" <pcnofelt@gmail.com> wrote in message
news:1174948599.676649.291900@n76g2000hsh.googlegroups.com...
> Hi all,
>
>
> ISSUE:
> ====================
> In SQL 2005 (sp2) I get the following error when preforming a bulk
> insert with an associated xml format file:
> "Could not bulk insert. Unknown version of format file"
>
> Question:
> ====================
> I am unsure what they mean by "unknown version". Specifically the
> format file in question was created using bcp. Also the entire table
> scenario was created from a msdn example.
>
> Any ideas? have you seen this before?
>
> NOTE: i can reproduce this issue outside the example but will refer to
> msdn considering it is simple and easily reproducible.
>
> Scenario
> ====================
> I can reproduce this error with the BULK INSERT example discussed on
> msdn (example A)
> http://msdn2.microsoft.com/en-us/library/ms1...
>
> TO REPRODUCE:
>
> * In short the table structure is:
> Person (Age int, FirstName varchar(20), LastName varchar(30))
>
> * Data File Template:
> Age<tab>Firstname<tab>Lastname<return>
>
> * xml file format from bcp (and described on msdn)
> <?xml version="1.0"?>
> <BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/fo...
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance...
> <RECORD>
> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
> MAX_LENGTH="12"/>
> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
> MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
> MAX_LENGTH="30"
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
> <COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
> <COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
> </ROW>
> </BCPFORMAT>
>
> * Here is some the actual sql statement that pulls this all together
> BULK INSERT mytestnames
> FROM 'C:\datatest\exampledata-c.Dat'
> WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');
>
>
> Thanks in advanced for any feedback.
>
> Cheers!
>

Erland Sommarskog

3/27/2007 1:16:00 PM

Peter Nofelt (pcnofelt@gmail.com) writes:
> Question:
>====================
> I am unsure what they mean by "unknown version". Specifically the
> format file in question was created using bcp. Also the entire table
> scenario was created from a msdn example.
>
> Any ideas? have you seen this before?
>...
> * Here is some the actual sql statement that pulls this all together
> BULK INSERT mytestnames
> FROM 'C:\datatest\exampledata-c.Dat'
> WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');

I don't have the time to research the situation right now, but as a shot in
the dark, I would try using xml for the file type.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

Erland Sommarskog

3/27/2007 9:56:00 PM

Peter Nofelt (pcnofelt@gmail.com) writes:
>====================
> In SQL 2005 (sp2) I get the following error when preforming a bulk
> insert with an associated xml format file:
> "Could not bulk insert. Unknown version of format file"
>
> Question:
>====================
> I am unsure what they mean by "unknown version". Specifically the
> format file in question was created using bcp. Also the entire table
> scenario was created from a msdn example.
>
> Any ideas? have you seen this before?

I researched this a little more, and I was able to use the format file,
no matter the file extension. I also tried creating the format file with
BCP, and it was still useful.

My suspicion is that you have some encoding problem. Maybe the file is
UTF-8. Or it's UTF-16, but there is BOM (Byte-order mark) in the file,
so that it looks like a file of 8-bit characters.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

Peter Nofelt

3/28/2007 9:28:00 PM

************** NOTE NOTE *****************

This issue has since been resolved

The format file was correct, but i had attempted to preform insert on
a SQL 2000 server rather than SQL 2006 server (i was still using SQL
management studio, but it was connected to the 2000 server).

Thanks for all the feedback

microsoft.public.sqlserver.programming

Bulk insert error -- "Unknown version of format file"

Peter Nofelt

Uri Dimant

Erland Sommarskog

Erland Sommarskog

Peter Nofelt

x Login to ForumsZone