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



Joel (Joel@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I've been trying for a couple of days to resolve an error (7330 - Cannot
fetch a row from OLE DB provider "BULK" for linked server "(null)".)
that I consistently receive during a stored proc Bulk Insert from a text
file. From the SQL Server 2005 .TXT file exported from Access 2007 using
TransferText Format file created using BCP
...

Database table in SQLServer has the same structure - but data types vary
from format file. Early attempts did not include a format file at all. I
found a disscussion group post that indicated a format file as defined
above would be useful. From that post, the author says "no matter the
datatypes in the destination table, the source is just a plain ascii
file, so specify all columns in the format file as SQLCHAR, all prefixes
as zero, and the length as the actual charachter length, not the
bytelength for the datatype. (ie a datetime becomes 26 instead of 8
etc)"

The format file does not look entirely correct, although I'm not sure
that is why you get the error.

First of all, I would change change the numbers in the fourth column to 0.
Not that I know whether it matters. My impression is that if you specify
both a delimiter and a length, that only the delimiter matters. But I could
be wrong.

In any case, your delimiters are probably not correct. I notice that
some fields in the data file are quote-delimited. BULK INSERT will
insert those quotes as they are data, unless you explicitly specify
them as delimiters in the file. So if a field is quote-delimited, the
field before should have ",\"" as the delimiter, and the field itself
should have "\"," - or "\",\"" if the next field is also quote-delimited.

There are also some things in your BULK INSERT statement that you can
take out:

BULK INSERT DataGL.dbo.[GLDATA]
FROM '\\WEY192\C$\WeyApps\Pvr\NewGL.txt'
WITH
( BATCHSIZE = 12000 ,
CHECK_CONSTRAINTS ,
DATAFILETYPE = 'char' ,
FIELDTERMINATOR = ',' ,
FORMATFILE='\\WEY192\C$\WeyApps\Pvr\gldata.fmt' ,
KEEPNULLS ,
ORDER ( OWNER1 ASC ) ,
ROWTERMINATOR = '\n'
)

Since you use a format file, FIELDTERMINATOR and ROWTERMINATOR are
superfluous. ORDER looks innocent, but take it out.

I've checked the data file for anomalies. No problems there -- except
that I don't know how to determine for sure exactly what ROWTERMINATOR
has been used by the TransferText export out of Access.

Try opening the file in a hex editor. The most likely bet is \r\n.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.