Bulk Insert error: "referencing non-existing element"
kwda...@gmail.com
error:
Line 30 in format file ...: referencing non-existing element id "21"
If I run BCP against the same format and table, I get the error:
"XML Format File : Bad element reference detected"
Line 30 is:
<COLUMN SOURCE="21" NAME="UPDATE_DATE" xsi:type="SQLDATETIME"/>
The entire format file is at the end below. I'm running SQL Server
2005 Developer Edition.
I don't see what the issue is. If I run BCP to CREATE a format file to
dump the same table, the line it creates for the UPDATE_DATE field is
the same:
<COLUMN SOURCE="21" NAME="UPDATE_DATE" xsi:type="SQLDATETIME"/>
UPDATE_DATE is indeed the 21st column in the table (by having SQL
Management studio script the table create, or looking the
ORDINAL_POSITION in the schema).
Help?
Complete format file.
<?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="CharTerm" TERMINATOR="\t" MAX_LENGTH = "13"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "12"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "3"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "5"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "24"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "5"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "1"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "4"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH = "24"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH = "15"/
>
</RECORD>
<ROW>
<COLUMN SOURCE="6" NAME="PROCEDURE_CODE" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="1" NAME="PATIENT_NO" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="ENCOUNTER_NO" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="3" NAME="CHARGE_NO" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="4" NAME="SUB_PROGRAM" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="PROVIDER" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="UNITS" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="DIAGNOSIS_XREF_NO" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="PAYOR_CLASS" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="PAYOR_NO" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="21" NAME="UPDATE_DATE" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="22" NAME="USER_ID" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
kwda...@gmail.com
> I'm trying to do a BULK INSERT with an XML format file. I get the
> error:
>
> Line 30 in format file ...: referencing non-existing element id "21"
>
> If I run BCP against the same format and table, I get the error:
>
> "XML Format File : Bad element reference detected"
Nevermind. I see that the Column source is not the same thing as the
Server Column Order that was in the old style format files.
Kevin
Erland Sommarskog
> Nevermind. I see that the Column source is not the same thing as the
> Server Column Order that was in the old style format files.
not been able to find any advantage with the new XML format, but if you
find one please tell me. :-) Until then, I stick to the old format.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
kwda...@gmail.com
> (kwdav...@gmail.com) writes:
> > Nevermind. I see that the Column source is not the same thing as the
> > Server Column Order that was in the old style format files.
>
> Just because it's XML and newer does not mean that it is better. I have
> not been able to find any advantage with the new XML format, but if you
> find one please tell me. :-) Until then, I stick to the old format.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Actually, I can give some advantages.
In order to use the old style format file, you have to know the column
ordinal position in the database table for each output field. This
means that if the database columns get reordered for some reason (and
it happens), your format files go bad. It's also much more complicated
if you have to generate one on the fly to have to access the table
schema to get the ordinal position.
I was driven to XML because I get sporadic transient errors in the old
style formats which I have not been able to resolve. XML represents
starting over.
Kevin
Erland Sommarskog
> In order to use the old style format file, you have to know the column
> ordinal position in the database table for each output field. This
> means that if the database columns get reordered for some reason (and
> it happens), your format files go bad. It's also much more complicated
> if you have to generate one on the fly to have to access the table
> schema to get the ordinal position.
have three fields in your data file, and there 50 columns in your table,
and three fields maps to columns 1, 10 and 20. With the old format you only
need to specify the rows in the format file. But how you deal with it
from XML?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx