Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv

Tech-Archive recommends: Fix windows errors by optimizing your registry

I didn't find any obvious discrepancies in data type size between the data
file and destination table. By the way, I attempted to import the data file
into an empty table using the import wizard - but execution stopped at
column 33 with the following error:

Error 0xc020901c: Data Flow Task: There was an error with input column
"Column 33" (304) on input "Destination Input" (152). The column status
returned was: "The value could not be converted because of a potential loss
of data.".
(SQL Server Import and Export Wizard)

In the data file, I don't see a problem with column 33 -- but column 34 is
defined as smallint (allow null). And in most records the column is Null. In
the delimited file, Col 34 is first numeric column in the row that is null
(an empty delimiter) -- so I wonder if that could be a problem. (In the data
file sample below, it's the first delimiter in that string of 4 empty
delimiters positioned about 10 columns from the row end.)

A sample row from the data file:
2009,14012.00,"012-004-10037",4,"01200410037","002000022.000",14,12,"CONHILL
PROPERTIES INC",,"32 SO VIEW TERR",,"ANY TOWN","ST","90049","LAND &
DWELLING","OLD BOYTON HILL
RD",2,"R2","NS",10.20,564200.00,0.00,564200.00,190500.00,373700.00,0.00,,0.00,"N",564200.00,503700.00,0.00,,,,,0.00,0.00,0.00,0.00,0.00,5642.00

In the destination table - before I forget to mention it - there is a column
at the end of each row of data type TIMESTAMP. The data file does not have a
corresponding field. My understand is that SQL Server/bulk insert would
ignore this column. If not true, please let me know.

And here's the current status of my BULK INSERT, in case you see any problem
here:

BULK INSERT DataGL.dbo.[GLDATA]
FROM '\\WEY192\C$\WeyApps\Pvr\NewGL.txt'
WITH
( BATCHSIZE = 12000 ,
CHECK_CONSTRAINTS ,
DATAFILETYPE = 'char' ,
FIRSTROW = 1,
FORMATFILE='\\TAX192\C$\WeyApps\Pvr\gldata.fmt',
KEEPNULLS ,
TABLOCK
);



The revised format file looks like this:

9.0
43
1 SQLCHAR 0 0 "," 1 YEARGL
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 ",\"" 2 TCODEGL
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"," 3 SPANGL
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 ",\"" 4 SCHIDGL
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "\",\"" 5 SPANALT
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\"," 6 PARCID
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "," 7 CNTYGL
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 ",\"" 8 ASMTCODE
SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\",\"" 9 OWNER1
SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 "\",\"" 10 OWNER2
SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\"," 11 ADDRGL1
SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 ",\"" 12 ADDRGL2
SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 0 "\",\"" 13 CITYGL
SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 0 "\",\"" 14 STGL
SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 0 "\",\"" 15 ZIPGL
SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 0 "\",\"" 16 DESCPROP
SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 0 "\"," 17 LOCAPROP
SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 0 ",\"" 18 CATCOGL
SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 0 "\",\"" 19 CATABRV
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 0 "\"," 20 RESCODE
SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 0 "," 21 ACRESGL
SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 0 "," 22 REAL_FLV
SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 0 "," 23 HSTED_FLV
SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 0 "," 24 NRES_FLV
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 0 "," 25 LAND_LV
SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 0 "," 26 IMPRV_LV
SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 0 "," 27 EQUIPVAL
SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 0 "," 28 EQUIPCODE
SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 0 ",\"" 29 INVENVAL
SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 0 "\"," 30 HSDECL
SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 0 "," 31 HSTEDVAL
SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 0 "," 32 HSITEVAL
SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 0 "," 33 VETEXAMT
SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 0 "," 34 EXCLASS
SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 0 "," 35 EXTYPE
SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 0 "," 36 ENDDATE
SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 0 "," 37 STATUTE
SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 0 "," 38 EXAMT_HS
SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 0 "," 39 EXAMT_NR
SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 0 "," 40 UVREDUC_HS
SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 0 "," 41 UVREDUC_NR
SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 0 "," 42 GLVAL_HS
SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 0 "\r\n" 43 GLVAL_NR
SQL_Latin1_General_CP1_CI_AS


If I can't get this to work, would you have other suggestions - other ways
to doing a bulk insert? Thanks.

Joel


"Joel" wrote:

I've set up the quote-delimiters as you describe. The error I mentioned in my
last post ("string or binary data would be truncated") has gone away. But I
still receive the "Cannot fetch . . " error.

Next, I'll double check the size of all data types in the destination table.

Joel


"Erland Sommarskog" wrote:

Joel (Joel@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Thanks for your suggestions. In the format file, I changed the fourth
column to 0 - and that change resulted in a different error: "8152
String or binary data would be truncated."

Regarding quotes in the data file, those are functioning as text
qualifiers. All fields are comma-delimited. The text qualifier allows
for the use of commas within a field. Double quotes indicate that a
character string follows, within which commas are ignored. But perhaps
BULK INSERT can't handle the text qualifier?

BULK INSERT does not about text qualifiers. With the current format file
where you have only comma as separator, BULK INSERT thinks the quotes are
part of the data. You need to state in the format file as I described
that he quotes are part of the delimiter, as I showed you in my previous
post.

The error message you get means that data in some field is longer that what
fits in the table. It is likely that this is because of the quotes, so
fix that part first.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Simple Bulk Insert
    ... Unless I do not remember correctly neither BCP or bulk insert handle quoted ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... > Insert into SQL Table (Table Name = PLEDGEFILE) of Same Structure. ... > Please Suggest How do I Bulk Insert Only Values - Excluding the double> Quotes. ...
    (microsoft.public.sqlserver.server)
  • Re: Simple Bulk Insert
    ... Unless I do not remember correctly neither BCP or bulk insert handle quoted ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... > Insert into SQL Table (Table Name = PLEDGEFILE) of Same Structure. ... > Please Suggest How do I Bulk Insert Only Values - Excluding the double> Quotes. ...
    (microsoft.public.sqlserver.programming)
  • Re: DTSDataPumpTask -- dynamic transformations?
    ... The downside to the Bulk Insert Task is this bit: ... "The Bulk Insert task can transfer data only from a text file into a ... SQL Server table or view." ... I'd have to export from the source, then import into the destination. ...
    (microsoft.public.sqlserver.dts)
  • Re: Generating one table with a terabyte of data
    ... Pretty much disabled the transaction log completely. ... We are using BULK INSERT ... for all those who don't know, SQL server prefers smaller BULK ... ORACLE seems to handle that 1 big file really well but with small files SQL ...
    (microsoft.public.sqlserver.dts)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I am in an environment where bulk insert isn't an option--even though the ... underlying database is SQL server, I am using a third party OLE DB provider-- ... > insert(assuming you are using SQL server) or DTS. ...
    (microsoft.public.data.ado)