Bulk Insert Error

Bulk Insert Error

Post by Blak » Thu, 18 Sep 2003 20:09:20



We are migration from SQL Server 7 to 2000.  While the Bulk Insert
works in Ver. 7 it does not work in 2000.  There error I am getting
is:

Bulk Insert data conversion error (type mismatch) in tow X, column
7(SubId).

Here is the Format File:

7.0
7
1       SQLCHAR       0       3       ""                        1    
TARNO
2       SQLCHAR       0       3       ""                        2    
CXRCD
3       SQLCHAR       0       8       ""                        3    
FARECLS
4       SQLCHAR       0       4       ""                        4    
RULENO
5       SQLCHAR       0       3       ""                        5    
FARETYPE
6       SQLCHAR       0       1       ""                        6    
CATTYPE
7       SQLCHAR       0       10      "\r\n"                    7    
SUBID

and a sample of the data.

013UA TE14NQ  4027XPNE1054      
011AA NX14E2QN4035XPNE1054      
011AA N14E2QN 4035XPNE1054      
011CO BEY14ZN 4087XPNE1054      
011CO BEZ14ZN 4087XPNE1054      
011CO HEY14ZN 4087XPNE1054      
011CO HEZ14ZN 4087XPNE1054      
011CO KEY14ZN 4087XPNE1054    

Ive run up against a brick wall on this one...

any help will be greatly apreciated.

Blake

 
 
 

Bulk Insert Error

Post by mountain ma » Fri, 19 Sep 2003 07:45:21



Quote:> We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> works in Ver. 7 it does not work in 2000.  There error I am getting
> is:

> Bulk Insert data conversion error (type mismatch) in tow X, column
> 7(SubId).

> Here is the Format File:

> 7.0
> 7
> 1       SQLCHAR       0       3       ""                        1
> TARNO
> 2       SQLCHAR       0       3       ""                        2
> CXRCD
> 3       SQLCHAR       0       8       ""                        3
> FARECLS
> 4       SQLCHAR       0       4       ""                        4
> RULENO
> 5       SQLCHAR       0       3       ""                        5
> FARETYPE
> 6       SQLCHAR       0       1       ""                        6
> CATTYPE
> 7       SQLCHAR       0       10      "\r\n"                    7
> SUBID

> and a sample of the data.

> 013UA TE14NQ  4027XPNE1054
> 011AA NX14E2QN4035XPNE1054
> 011AA N14E2QN 4035XPNE1054
> 011CO BEY14ZN 4087XPNE1054
> 011CO BEZ14ZN 4087XPNE1054
> 011CO HEY14ZN 4087XPNE1054
> 011CO HEZ14ZN 4087XPNE1054
> 011CO KEY14ZN 4087XPNE1054

> Ive run up against a brick wall on this one...

> any help will be greatly apreciated.

1) Have no idea what the "\r\n" means at row
seven but would try with this set as "" like the
others (it may be some contraint not completely
consistent in the data of this column)

2) If the data exists on SQL7 you dont need to
bcp it out and in.  Have you checked the data
transformation services, and table import?

 
 
 

Bulk Insert Error

Post by Dan Guzma » Fri, 19 Sep 2003 14:20:27


Blake, can you post the table DDL?  I had no problems importing your
sample data using your format file into a table with the following
schema:  Are you using the SQL 2000 BCP utility?

CREATE TABLE Test
    (
    TARNO int,
    CXRCD varchar(3),
    FARECLS varchar(8),
    RULENO int,
    FARETYPE varchar(3),
    CATTYPE varchar(1),
    SUBID int
    )

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> works in Ver. 7 it does not work in 2000.  There error I am getting
> is:

> Bulk Insert data conversion error (type mismatch) in tow X, column
> 7(SubId).

> Here is the Format File:

> 7.0
> 7
> 1       SQLCHAR       0       3       ""                        1
> TARNO
> 2       SQLCHAR       0       3       ""                        2
> CXRCD
> 3       SQLCHAR       0       8       ""                        3
> FARECLS
> 4       SQLCHAR       0       4       ""                        4
> RULENO
> 5       SQLCHAR       0       3       ""                        5
> FARETYPE
> 6       SQLCHAR       0       1       ""                        6
> CATTYPE
> 7       SQLCHAR       0       10      "\r\n"                    7
> SUBID

> and a sample of the data.

> 013UA TE14NQ  4027XPNE1054
> 011AA NX14E2QN4035XPNE1054
> 011AA N14E2QN 4035XPNE1054
> 011CO BEY14ZN 4087XPNE1054
> 011CO BEZ14ZN 4087XPNE1054
> 011CO HEY14ZN 4087XPNE1054
> 011CO HEZ14ZN 4087XPNE1054
> 011CO KEY14ZN 4087XPNE1054

> Ive run up against a brick wall on this one...

> any help will be greatly apreciated.

> Blake

 
 
 

Bulk Insert Error

Post by Dan Guzma » Fri, 19 Sep 2003 14:28:46


Quote:> 1) Have no idea what the "\r\n" means at row
> seven but would try with this set as "" like the
> others

'\r' is the escape sequence for a carriage return and '\n' is the escape
sequence for a line feed.  This combination is apparently the row
terminator in Blake's file.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------




> > We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> > works in Ver. 7 it does not work in 2000.  There error I am getting
> > is:

> > Bulk Insert data conversion error (type mismatch) in tow X, column
> > 7(SubId).

> > Here is the Format File:

> > 7.0
> > 7
> > 1       SQLCHAR       0       3       ""                        1
> > TARNO
> > 2       SQLCHAR       0       3       ""                        2
> > CXRCD
> > 3       SQLCHAR       0       8       ""                        3
> > FARECLS
> > 4       SQLCHAR       0       4       ""                        4
> > RULENO
> > 5       SQLCHAR       0       3       ""                        5
> > FARETYPE
> > 6       SQLCHAR       0       1       ""                        6
> > CATTYPE
> > 7       SQLCHAR       0       10      "\r\n"                    7
> > SUBID

> > and a sample of the data.

> > 013UA TE14NQ  4027XPNE1054
> > 011AA NX14E2QN4035XPNE1054
> > 011AA N14E2QN 4035XPNE1054
> > 011CO BEY14ZN 4087XPNE1054
> > 011CO BEZ14ZN 4087XPNE1054
> > 011CO HEY14ZN 4087XPNE1054
> > 011CO HEZ14ZN 4087XPNE1054
> > 011CO KEY14ZN 4087XPNE1054

> > Ive run up against a brick wall on this one...

> > any help will be greatly apreciated.

> 1) Have no idea what the "\r\n" means at row
> seven but would try with this set as "" like the
> others (it may be some contraint not completely
> consistent in the data of this column)

> 2) If the data exists on SQL7 you dont need to
> bcp it out and in.  Have you checked the data
> transformation services, and table import?

 
 
 

Bulk Insert Error

Post by Blak » Tue, 23 Sep 2003 20:25:22


Dan

I am not useing the BCP utility.  We are calling the Bulkinsert statement
from within an application.

The statement is:

BULK INSERT CcfFareCls FROM 'W:\\Facts\\Work\\CcfFareCls.txt' WITH
(FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',ROWS_PER_BATCH=50000,CH
ECK_CONSTRAINTS)

the DDL for the table is...

CREATE TABLE dbo.CCFFareCls
(
    TarNo    char(3) NULL,
    CxrCd    char(3) NULL,
    FareCls  char(8) NULL,
    RuleNo   char(4) NULL,
    FareType char(3) NULL,
    CatType  char(1) NULL,
    SubId    int     NOT NULL
)

there are some constraints and indexies on the table that I have not
included.


> Blake, can you post the table DDL?  I had no problems importing your
> sample data using your format file into a table with the following
> schema:  Are you using the SQL 2000 BCP utility?

> CREATE TABLE Test
>     (
>     TARNO int,
>     CXRCD varchar(3),
>     FARECLS varchar(8),
>     RULENO int,
>     FARETYPE varchar(3),
>     CATTYPE varchar(1),
>     SUBID int
>     )

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------



> > We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> > works in Ver. 7 it does not work in 2000.  There error I am getting
> > is:

> > Bulk Insert data conversion error (type mismatch) in tow X, column
> > 7(SubId).

> > Here is the Format File:

> > 7.0
> > 7
> > 1       SQLCHAR       0       3       ""                        1
> > TARNO
> > 2       SQLCHAR       0       3       ""                        2
> > CXRCD
> > 3       SQLCHAR       0       8       ""                        3
> > FARECLS
> > 4       SQLCHAR       0       4       ""                        4
> > RULENO
> > 5       SQLCHAR       0       3       ""                        5
> > FARETYPE
> > 6       SQLCHAR       0       1       ""                        6
> > CATTYPE
> > 7       SQLCHAR       0       10      "\r\n"                    7
> > SUBID

> > and a sample of the data.

> > 013UA TE14NQ  4027XPNE1054
> > 011AA NX14E2QN4035XPNE1054
> > 011AA N14E2QN 4035XPNE1054
> > 011CO BEY14ZN 4087XPNE1054
> > 011CO BEZ14ZN 4087XPNE1054
> > 011CO HEY14ZN 4087XPNE1054
> > 011CO HEZ14ZN 4087XPNE1054
> > 011CO KEY14ZN 4087XPNE1054

> > Ive run up against a brick wall on this one...

> > any help will be greatly apreciated.

> > Blake

 
 
 

Bulk Insert Error

Post by Dan Guzma » Wed, 24 Sep 2003 05:27:36


Hi, Blake.

I successfully imported the sample data into my SQL 2000 SP3 instance
using your DDL, sample data and format file.  I ran the BULK INSERT
statement from Query Analyzer.

What error are you getting?

--
Hope this helps.

Dan Guzman
SQL Server MVP


Quote:> Dan

> I am not useing the BCP utility.  We are calling the Bulkinsert
statement
> from within an application.

> The statement is:

> BULK INSERT CcfFareCls FROM 'W:\\Facts\\Work\\CcfFareCls.txt' WITH

(FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',ROWS_PER_BATCH=5000
0,CH
> ECK_CONSTRAINTS)

> the DDL for the table is...

> CREATE TABLE dbo.CCFFareCls
> (
>     TarNo    char(3) NULL,
>     CxrCd    char(3) NULL,
>     FareCls  char(8) NULL,
>     RuleNo   char(4) NULL,
>     FareType char(3) NULL,
>     CatType  char(1) NULL,
>     SubId    int     NOT NULL
> )

> there are some constraints and indexies on the table that I have not
> included.



> > Blake, can you post the table DDL?  I had no problems importing your
> > sample data using your format file into a table with the following
> > schema:  Are you using the SQL 2000 BCP utility?

> > CREATE TABLE Test
> >     (
> >     TARNO int,
> >     CXRCD varchar(3),
> >     FARECLS varchar(8),
> >     RULENO int,
> >     FARETYPE varchar(3),
> >     CATTYPE varchar(1),
> >     SUBID int
> >     )

> > --
> > Hope this helps.

> > Dan Guzman
> > SQL Server MVP

> > -----------------------
> > SQL FAQ links (courtesy  Neil Pike):

> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > -----------------------



> > > We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> > > works in Ver. 7 it does not work in 2000.  There error I am
getting
> > > is:

> > > Bulk Insert data conversion error (type mismatch) in tow X, column
> > > 7(SubId).

> > > Here is the Format File:

> > > 7.0
> > > 7
> > > 1       SQLCHAR       0       3       ""                        1
> > > TARNO
> > > 2       SQLCHAR       0       3       ""                        2
> > > CXRCD
> > > 3       SQLCHAR       0       8       ""                        3
> > > FARECLS
> > > 4       SQLCHAR       0       4       ""                        4
> > > RULENO
> > > 5       SQLCHAR       0       3       ""                        5
> > > FARETYPE
> > > 6       SQLCHAR       0       1       ""                        6
> > > CATTYPE
> > > 7       SQLCHAR       0       10      "\r\n"                    7
> > > SUBID

> > > and a sample of the data.

> > > 013UA TE14NQ  4027XPNE1054
> > > 011AA NX14E2QN4035XPNE1054
> > > 011AA N14E2QN 4035XPNE1054
> > > 011CO BEY14ZN 4087XPNE1054
> > > 011CO BEZ14ZN 4087XPNE1054
> > > 011CO HEY14ZN 4087XPNE1054
> > > 011CO HEZ14ZN 4087XPNE1054
> > > 011CO KEY14ZN 4087XPNE1054

> > > Ive run up against a brick wall on this one...

> > > any help will be greatly apreciated.

> > > Blake

 
 
 

Bulk Insert Error

Post by Blak » Wed, 24 Sep 2003 18:58:51


There Error that I am getting is...

Bulk insert data conversion error (type mismatch) for row 1, column 7
(SubId).

The application that executes this command is writen in Borland C++ Builder,
using an ODBC connection (version 3.70.11.40).


> Hi, Blake.

> I successfully imported the sample data into my SQL 2000 SP3 instance
> using your DDL, sample data and format file.  I ran the BULK INSERT
> statement from Query Analyzer.

> What error are you getting?

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP



> > Dan

> > I am not useing the BCP utility.  We are calling the Bulkinsert
> statement
> > from within an application.

> > The statement is:

> > BULK INSERT CcfFareCls FROM 'W:\\Facts\\Work\\CcfFareCls.txt' WITH

> (FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',ROWS_PER_BATCH=5000
> 0,CH
> > ECK_CONSTRAINTS)

> > the DDL for the table is...

> > CREATE TABLE dbo.CCFFareCls
> > (
> >     TarNo    char(3) NULL,
> >     CxrCd    char(3) NULL,
> >     FareCls  char(8) NULL,
> >     RuleNo   char(4) NULL,
> >     FareType char(3) NULL,
> >     CatType  char(1) NULL,
> >     SubId    int     NOT NULL
> > )

> > there are some constraints and indexies on the table that I have not
> > included.



> > > Blake, can you post the table DDL?  I had no problems importing your
> > > sample data using your format file into a table with the following
> > > schema:  Are you using the SQL 2000 BCP utility?

> > > CREATE TABLE Test
> > >     (
> > >     TARNO int,
> > >     CXRCD varchar(3),
> > >     FARECLS varchar(8),
> > >     RULENO int,
> > >     FARETYPE varchar(3),
> > >     CATTYPE varchar(1),
> > >     SUBID int
> > >     )

> > > --
> > > Hope this helps.

> > > Dan Guzman
> > > SQL Server MVP

> > > -----------------------
> > > SQL FAQ links (courtesy  Neil Pike):

> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > -----------------------



> > > > We are migration from SQL Server 7 to 2000.  While the Bulk Insert
> > > > works in Ver. 7 it does not work in 2000.  There error I am
> getting
> > > > is:

> > > > Bulk Insert data conversion error (type mismatch) in tow X, column
> > > > 7(SubId).

> > > > Here is the Format File:

> > > > 7.0
> > > > 7
> > > > 1       SQLCHAR       0       3       ""                        1
> > > > TARNO
> > > > 2       SQLCHAR       0       3       ""                        2
> > > > CXRCD
> > > > 3       SQLCHAR       0       8       ""                        3
> > > > FARECLS
> > > > 4       SQLCHAR       0       4       ""                        4
> > > > RULENO
> > > > 5       SQLCHAR       0       3       ""                        5
> > > > FARETYPE
> > > > 6       SQLCHAR       0       1       ""                        6
> > > > CATTYPE
> > > > 7       SQLCHAR       0       10      "\r\n"                    7
> > > > SUBID

> > > > and a sample of the data.

> > > > 013UA TE14NQ  4027XPNE1054
> > > > 011AA NX14E2QN4035XPNE1054
> > > > 011AA N14E2QN 4035XPNE1054
> > > > 011CO BEY14ZN 4087XPNE1054
> > > > 011CO BEZ14ZN 4087XPNE1054
> > > > 011CO HEY14ZN 4087XPNE1054
> > > > 011CO HEZ14ZN 4087XPNE1054
> > > > 011CO KEY14ZN 4087XPNE1054

> > > > Ive run up against a brick wall on this one...

> > > > any help will be greatly apreciated.

> > > > Blake

 
 
 

Bulk Insert Error

Post by Dan Guzma » Thu, 25 Sep 2003 05:28:56


Sorry I asked you to repeat the error message - I missed that in your
original post.

As I understand it, you successfully bulk inserted into SQL 7 using the
same data file, format file and table structure but it fails on SQL 2000
SP3.  I doubt this is API-related but you might try to repro via Query
Analyzer to be sure.  Try inserting into a varchar(10) column using the
script below to see if this turns up problem data.  I had no problems
with SQL 7 or 2000 using the provided sample data.

CREATE TABLE dbo.CCFFareCls_Test
    (
    TarNo    char(3) NULL,
    CxrCd    char(3) NULL,
    FareCls  char(8) NULL,
    RuleNo   char(4) NULL,
    FareType char(3) NULL,
    CatType  char(1) NULL,
    SubId    varchar(10) NOT NULL
    )

BULK INSERT CCFFareCls_Test
FROM 'W:\\Facts\\Work\\CcfFareCls.txt'
WITH
    (
    FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',
    ROWS_PER_BATCH=50000,
    CHECK_CONSTRAINTS
    )

SELECT *
FROM dbo.CCFFareCls_Test
WHERE LTRIM(RTRIM(SubId)) LIKE '%[^0-9]%'

--
Hope this helps.

Dan Guzman
SQL Server MVP


> There Error that I am getting is...

> Bulk insert data conversion error (type mismatch) for row 1, column 7
> (SubId).

> The application that executes this command is writen in Borland C++
Builder,
> using an ODBC connection (version 3.70.11.40).



> > Hi, Blake.

> > I successfully imported the sample data into my SQL 2000 SP3
instance
> > using your DDL, sample data and format file.  I ran the BULK INSERT
> > statement from Query Analyzer.

> > What error are you getting?

> > --
> > Hope this helps.

> > Dan Guzman
> > SQL Server MVP



> > > Dan

> > > I am not useing the BCP utility.  We are calling the Bulkinsert
> > statement
> > > from within an application.

> > > The statement is:

> > > BULK INSERT CcfFareCls FROM 'W:\\Facts\\Work\\CcfFareCls.txt' WITH

(FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',ROWS_PER_BATCH=5000

- Show quoted text -

> > 0,CH
> > > ECK_CONSTRAINTS)

> > > the DDL for the table is...

> > > CREATE TABLE dbo.CCFFareCls
> > > (
> > >     TarNo    char(3) NULL,
> > >     CxrCd    char(3) NULL,
> > >     FareCls  char(8) NULL,
> > >     RuleNo   char(4) NULL,
> > >     FareType char(3) NULL,
> > >     CatType  char(1) NULL,
> > >     SubId    int     NOT NULL
> > > )

> > > there are some constraints and indexies on the table that I have
not
> > > included.



> > > > Blake, can you post the table DDL?  I had no problems importing
your
> > > > sample data using your format file into a table with the
following
> > > > schema:  Are you using the SQL 2000 BCP utility?

> > > > CREATE TABLE Test
> > > >     (
> > > >     TARNO int,
> > > >     CXRCD varchar(3),
> > > >     FARECLS varchar(8),
> > > >     RULENO int,
> > > >     FARETYPE varchar(3),
> > > >     CATTYPE varchar(1),
> > > >     SUBID int
> > > >     )

> > > > --
> > > > Hope this helps.

> > > > Dan Guzman
> > > > SQL Server MVP

> > > > -----------------------
> > > > SQL FAQ links (courtesy  Neil Pike):

> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > -----------------------



> > > > > We are migration from SQL Server 7 to 2000.  While the Bulk
Insert
> > > > > works in Ver. 7 it does not work in 2000.  There error I am
> > getting
> > > > > is:

> > > > > Bulk Insert data conversion error (type mismatch) in tow X,
column
> > > > > 7(SubId).

> > > > > Here is the Format File:

> > > > > 7.0
> > > > > 7
> > > > > 1       SQLCHAR       0       3       ""
1
> > > > > TARNO
> > > > > 2       SQLCHAR       0       3       ""
2
> > > > > CXRCD
> > > > > 3       SQLCHAR       0       8       ""
3
> > > > > FARECLS
> > > > > 4       SQLCHAR       0       4       ""
4
> > > > > RULENO
> > > > > 5       SQLCHAR       0       3       ""
5
> > > > > FARETYPE
> > > > > 6       SQLCHAR       0       1       ""
6
> > > > > CATTYPE
> > > > > 7       SQLCHAR       0       10      "\r\n"
7
> > > > > SUBID

> > > > > and a sample of the data.

> > > > > 013UA TE14NQ  4027XPNE1054
> > > > > 011AA NX14E2QN4035XPNE1054
> > > > > 011AA N14E2QN 4035XPNE1054
> > > > > 011CO BEY14ZN 4087XPNE1054
> > > > > 011CO BEZ14ZN 4087XPNE1054
> > > > > 011CO HEY14ZN 4087XPNE1054
> > > > > 011CO HEZ14ZN 4087XPNE1054
> > > > > 011CO KEY14ZN 4087XPNE1054

> > > > > Ive run up against a brick wall on this one...

> > > > > any help will be greatly apreciated.

> > > > > Blake

 
 
 

Bulk Insert Error

Post by Blak » Tue, 30 Sep 2003 19:22:46


Dan

Well, I have been able to get it to process the file if I use SQL Explorer,
but our application continues to crash.

Both Sql Explorer and our application are accessing the database through
Borlands BDE using the latest ODBC drivers....


> Sorry I asked you to repeat the error message - I missed that in your
> original post.

> As I understand it, you successfully bulk inserted into SQL 7 using the
> same data file, format file and table structure but it fails on SQL 2000
> SP3.  I doubt this is API-related but you might try to repro via Query
> Analyzer to be sure.  Try inserting into a varchar(10) column using the
> script below to see if this turns up problem data.  I had no problems
> with SQL 7 or 2000 using the provided sample data.

> CREATE TABLE dbo.CCFFareCls_Test
>     (
>     TarNo    char(3) NULL,
>     CxrCd    char(3) NULL,
>     FareCls  char(8) NULL,
>     RuleNo   char(4) NULL,
>     FareType char(3) NULL,
>     CatType  char(1) NULL,
>     SubId    varchar(10) NOT NULL
>     )

> BULK INSERT CCFFareCls_Test
> FROM 'W:\\Facts\\Work\\CcfFareCls.txt'
> WITH
>     (
>     FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',
>     ROWS_PER_BATCH=50000,
>     CHECK_CONSTRAINTS
>     )

> SELECT *
> FROM dbo.CCFFareCls_Test
> WHERE LTRIM(RTRIM(SubId)) LIKE '%[^0-9]%'

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP



> > There Error that I am getting is...

> > Bulk insert data conversion error (type mismatch) for row 1, column 7
> > (SubId).

> > The application that executes this command is writen in Borland C++
> Builder,
> > using an ODBC connection (version 3.70.11.40).



> > > Hi, Blake.

> > > I successfully imported the sample data into my SQL 2000 SP3
> instance
> > > using your DDL, sample data and format file.  I ran the BULK INSERT
> > > statement from Query Analyzer.

> > > What error are you getting?

> > > --
> > > Hope this helps.

> > > Dan Guzman
> > > SQL Server MVP



> > > > Dan

> > > > I am not useing the BCP utility.  We are calling the Bulkinsert
> > > statement
> > > > from within an application.

> > > > The statement is:

> > > > BULK INSERT CcfFareCls FROM 'W:\\Facts\\Work\\CcfFareCls.txt' WITH

> (FORMATFILE='W:\\Facts\\FormatFiles\\CcfFareCls.fmt',ROWS_PER_BATCH=5000
> > > 0,CH
> > > > ECK_CONSTRAINTS)

> > > > the DDL for the table is...

> > > > CREATE TABLE dbo.CCFFareCls
> > > > (
> > > >     TarNo    char(3) NULL,
> > > >     CxrCd    char(3) NULL,
> > > >     FareCls  char(8) NULL,
> > > >     RuleNo   char(4) NULL,
> > > >     FareType char(3) NULL,
> > > >     CatType  char(1) NULL,
> > > >     SubId    int     NOT NULL
> > > > )

> > > > there are some constraints and indexies on the table that I have
> not
> > > > included.



> > > > > Blake, can you post the table DDL?  I had no problems importing
> your
> > > > > sample data using your format file into a table with the
> following
> > > > > schema:  Are you using the SQL 2000 BCP utility?

> > > > > CREATE TABLE Test
> > > > >     (
> > > > >     TARNO int,
> > > > >     CXRCD varchar(3),
> > > > >     FARECLS varchar(8),
> > > > >     RULENO int,
> > > > >     FARETYPE varchar(3),
> > > > >     CATTYPE varchar(1),
> > > > >     SUBID int
> > > > >     )

> > > > > --
> > > > > Hope this helps.

> > > > > Dan Guzman
> > > > > SQL Server MVP

> > > > > -----------------------
> > > > > SQL FAQ links (courtesy  Neil Pike):

> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > -----------------------



> > > > > > We are migration from SQL Server 7 to 2000.  While the Bulk
> Insert
> > > > > > works in Ver. 7 it does not work in 2000.  There error I am
> > > getting
> > > > > > is:

> > > > > > Bulk Insert data conversion error (type mismatch) in tow X,
> column
> > > > > > 7(SubId).

> > > > > > Here is the Format File:

> > > > > > 7.0
> > > > > > 7
> > > > > > 1       SQLCHAR       0       3       ""
> 1
> > > > > > TARNO
> > > > > > 2       SQLCHAR       0       3       ""
> 2
> > > > > > CXRCD
> > > > > > 3       SQLCHAR       0       8       ""
> 3
> > > > > > FARECLS
> > > > > > 4       SQLCHAR       0       4       ""
> 4
> > > > > > RULENO
> > > > > > 5       SQLCHAR       0       3       ""
> 5
> > > > > > FARETYPE
> > > > > > 6       SQLCHAR       0       1       ""
> 6
> > > > > > CATTYPE
> > > > > > 7       SQLCHAR       0       10      "\r\n"
> 7
> > > > > > SUBID

> > > > > > and a sample of the data.

> > > > > > 013UA TE14NQ  4027XPNE1054
> > > > > > 011AA NX14E2QN4035XPNE1054
> > > > > > 011AA N14E2QN 4035XPNE1054
> > > > > > 011CO BEY14ZN 4087XPNE1054
> > > > > > 011CO BEZ14ZN 4087XPNE1054
> > > > > > 011CO HEY14ZN 4087XPNE1054
> > > > > > 011CO HEZ14ZN 4087XPNE1054
> > > > > > 011CO KEY14ZN 4087XPNE1054

> > > > > > Ive run up against a brick wall on this one...

> > > > > > any help will be greatly apreciated.

> > > > > > Blake

 
 
 

Bulk Insert Error

Post by Dan Guzma » Wed, 01 Oct 2003 04:19:42


I would guess there's something different about the SQL being sent to
the back end then.  Try comparing the Profiler trace of your app with
the SQL Explorer version to see if you can spot something.

--
Hope this helps.

Dan Guzman
SQL Server MVP


Quote:> Dan

> Well, I have been able to get it to process the file if I use SQL
Explorer,
> but our application continues to crash.

> Both Sql Explorer and our application are accessing the database
through
> Borlands BDE using the latest ODBC drivers....

 
 
 

Bulk Insert Error

Post by Blak » Wed, 08 Oct 2003 19:05:09


Dan

Well, finally found out what the problem was.  As it turns out, there where
Nulls in the file, that when cut/pasted into the email where converted to
spaces.  SQL Server 2000 does not like nulls in the file, while SQL Server 7
accepted them.

Thanks for all your help

Blake

> I would guess there's something different about the SQL being sent to
> the back end then.  Try comparing the Profiler trace of your app with
> the SQL Explorer version to see if you can spot something.

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP



> > Dan

> > Well, I have been able to get it to process the file if I use SQL
> Explorer,
> > but our application continues to crash.

> > Both Sql Explorer and our application are accessing the database
> through
> > Borlands BDE using the latest ODBC drivers....

 
 
 

1. Bulk Insert Error with SQL 2000

Our New 2000 production server will not see a fixed field as a null.
We import a file in SQL 7.0 That has |    | sometimes and it imports
this as null. Some of our other extracts may have || and it also sees
this as null. SQL 2000 sees || as null but will not see |    | as null.
Is there a setting or something that I need to adjust to fix this issue.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. dumb terminal (Wyse) setup in D3/Linux....

3. Bulk Insert Error

4. Update my Pick skills

5. Bulk Insert error checking

6. Word Indexing

7. BULK INSERT ERROR

8. 13505-ID-Idaho-DBA Skills-ORACLE Financials-ORACLE-Database Administrator

9. Bulk insert Error code 5(Access denied)

10. Bulk Insert Error

11. How to catch Bulk insert error

12. Bulk Insert error

13. BULK INSERT error lastrow less than firstrow