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

.



Relevant Pages

  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... complication with BULK INSERT. ... mismatch between the format file and the data. ... is to read the data with comma as the only delimiter, and inside SQL ... This is probably when you need to look at SQL Server Integration Services, ...
    (microsoft.public.sqlserver.programming)
  • Re: More efficient way to insert a dataset in .NET 2.0?
    ... If you have a large set of data you need to import into a SQL Server database, you should use the SQL Server BULK INSERT command. ... I changed the way it inserted, so that it used the bulk insert transact sql statement, using a format file to define the format of the data. ... From the time it started inserting to the time it finished was 15 seconds...the entire app runs in under 2 minutes now. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Question on BCP
    ... the format file. ... BCP not populate a field once it has already been populated earlier in ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Bulk Insert With Identity Field
    ... > You can use a format file ... > columns and bcp into that. ... >>Can I Bulk Insert to to SQL Table with a Identity Column ...
    (microsoft.public.sqlserver.programming)
  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... Bulk load: An unexpected end of file was encountered in the data file. ... Anyway, there was an error in the format file, at least in regards to ... This is probably when you need to look at SQL Server Integration Services, ...
    (microsoft.public.sqlserver.programming)