SQL Server, Cannot fetch a row from OLE DB provider "BULK" for linked serv

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?





"Erland Sommarskog" wrote:

> Joel ( XXXX@XXXXX.COM ) 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, XXXX@XXXXX.COM
>
> 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
>
>

Joel ( XXXX@XXXXX.COM ) writes:

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, XXXX@XXXXX.COM

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

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

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_Lati

Joel ( XXXX@XXXXX.COM ) writes:

Does the problem occur, if you have a file with this sole line of data?
I ask for two reasons. One, it is something for you to test. Two, if I
am to take a stab at this, I need data for which the problem occurs.

And while I'm at it, I would also need the CREATE TABLE statement
for the table.


If that is column 44 in the table, that should be alright.


The only suggestion I can give at this point is to add the ERRORFILE
option to get errors written to a file. That may reveal something.

Also, you could try using BCP instead, in case there is a quirk with
OLE DB. If nothing else, you may get a different error message. (Though
necessary one more comprehensible; error messages around BULK INSERT/BCP
are often cryptic.)

I could have a look at it, but I as I mentioned I would need the CREATE
TABLE statement, and a sample data file for which the problem appears.
Put the data file in an attachment, or make it available for download
at a website, so it does not get distorted.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

Erland,

Yes. the problem occurs when the datafile contains that single line of data.
Timestamp is in column 44 - so that should be ok.

You can find the related files, includIng the table create statement at:

http://www.state.vt.us/tax/pdf.word.excel/pvr/txrpdfs/Files.zip

Thanks taking a look.

Joel ( XXXX@XXXXX.COM ) writes:

Thanks for the files! Unfortunately I have some bad news for you: it's
bedtime for me, so I cannot look at your files now. But I will try to
give them an eye tomorrow.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

Joel ( XXXX@XXXXX.COM ) writes:

I've now looked at your files, and there were a couple of problems.

The first is that, yes, BEGIN-TRY is great, but when you get problems
with BULK INSERT you should run the statement on it own. When I run
the statement alone I got this output:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".

No, I don't expect that you would have understood this. There is a
still a high mumbo-jumbo factor here. But the first message tells me
who have fought with BULK INSERT and BCP in the past, that there is a
mismatch between the format file and the data. BCP and BULK INSERT
are funny fellows. They read the format description and the follow it
slavically. Field 1, Field 2 and so on. If they get out of sync, they
have no chance to recover. For them there exist no such things as lines;
the rowterminator is only the terminator for the last field. This may
seem corny, but keep in mind that they just as much target binary data
as they target character data.

Anyway, there was an error in the format file, at least in regards to
the data file. Here are the two corrected lines:

9 SQLCHAR 0 0 "\"," 9 OWNER1
SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 ",\"" 10 OWNER2
SQL_Latin1_General_CP1_CI_AS

In the sample file, OWNER2 is blank. Now, I would not be surprised if
there are other rows in your data where OWNER2 has a value and is quoted.

I think I mentioned this, but I might not have stressed clear enough.
If the file has inconsistent quoting, that is a file is sometimes quoted
and sometimes not, BULK INSERT and BCP lose. They are completely
unintelligent when it comes to quotes. Your only option in this case
is to read the data with comma as the only delimiter, and inside SQL
Server get rid of the quotes. (Which also means that you need to have
space for the quotes, or use OPENROWSET(BULK).

But if the data can in fact has embedded commas, your defeat is complete.
BCP/BULK INSERT will get out of sync, and everything will be a mess.
This is probably when you need to look at SQL Server Integration Services,
something I have not worked with at all myself.

Finally, don't feel bad if you had the delimiters wrong for OWNER2.
Assembling a format file for a 43-column file with a mix of quoted
and unquoted fields is something that could put anyone to sleep.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

hanks very much for taking time to look at the files. I never expected such
complication with BULK INSERT.

Tomorrow I'll have a closer look at the data to see how many instances of
embedded commas I find. If I can remove all embedded commas in the source
data, then BULK INSERT and the format file (after removing quote-delimiters)
should work. Correct? I'll also remove BEGIN-TRY for testing.

Original data source is Access - so I have opportunities on that side to
remove embedded commas.

Also, I suppose that instead of exporting a text file out of Access (2007),
I could create an XML file. But I haven't worked with XML yet - and that
would probably be another long process of trial and error. If I can force
BULK INSERT to work with my existing data file, I'll be able to claim some
progress. (This is my first project with SQL Server.)

I'll post back tomorrow with final results of the BULK INSERT saga. Thanks
for your excellent help!

"Erland Sommarskog" wrote:

Joel ( XXXX@XXXXX.COM ) writes:

Keep in mind that with the quotes, some data will not fit your target
table. There are two ways to go. One is to get the data into a temp table
with wider columns, and then use substring to strip quotes. The other is
to use OPENROWSET(BULK) which permits you to select from the data file
and strip the quotes on the fly. But if memory serves, OPENROWSET(BULK)
requires an XML format file. Then again, if you are down on comma as
delimiter for all columns, you don't need a format file.


XML would be a better choice, in that XML gives you a more robust handling
of delimiters and embedded delimiters. Learning to use XML in SQL Server
will also pay off in the long run, but I can agree that for a first project,
that's quite a mouthful.

Yet an option is to create a linked server which would permit you to access
the Access database from SQL Server. This would require that the Access
database is accessible from within SQL Server. The advantage is that in
this case you can use regular SQL statements, and you don't have to export
the data. The disadvantage is that getting the linked server to work may
prove to be yet another battle.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

n consideration of the fact that my data file has inconsistent quoting, my
primary effort today involved exporting a data file from Access that did not
contain quote-delimiters and executing BULK INSERT without the format file.

Made serveral attempts -- but continued to get the "unexpected end of file
was encountered" error.

So I began to look at other options -- XML in particular. As I have recently
taken a course that included a cursory look at shredding XML data for insert
into tables. But a couple of points were not clear.

1) I'll need to pass an XML string into my stored procedure as a parameter.
How do I accomplish this? When I call the stored procedure from within Access
(a pass-through query) I can pass parameters - but how to instantiate a
variable with the content of my XML file so that it can be passed to SQL
Server? Or would this somehow be accomplished at the top of my stored
procedure in T-SQL by pointing to the XML file?

2) I assume that BULK INSERT or Format files will not be needed. Correct?

With answers to these questions, I think I'll be on my way! And as you say,
figuring out how to work with XML at the beginning of my encounter with SQL
Server will be of significant benefit down the road.






"Erland Sommarskog" wrote:

Joel ( XXXX@XXXXX.COM ) writes:

From SQL Server you can read the file with OPENROWSET(BULK)
using the SINGLE_BLOB option.

Passing the XML document from within Access... You cannot simply to
a parameterised procedure call and use the xml data type, because ADO
does not support that. But you could pass it a text parameter, and
have the procedure in SQL Server to have an nvarchar(MAX) parameter
and then convert to XML from there. You could also construct a query
batch which goes:

EXEC yourprocedure N'<?xml ....>

and invoke that one. Only watch out for embedded single quotes, but
I believe they entitised in XML, so it should not be an issue.


Correct. Even if OPENROWSET(BULK) is the same as BULK INSERT, the
SINGLE_BLOB option makes it a simple affair.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

Today I worked on the OPENROWSET(BULK) using the SINGLE_BLOB option. I think
I'm close, but seen to have fallen a bit short.

After I create the XML file from Access and call the stored procedure, this
is the error:
[Microsoft][SQL Native Client][SQL Server]Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 0, current count = 1. (#266)

Current files that I'm running are available at:
http://www.state.vt.us/tax/pdf.word.excel/pvr/txrpdfs/xmlFiles.zip

But here's a snippet of the stored procedure . . .
DECLARE @xml_doc xml
DECLARE @hdoc INT -- handle to XML doc

SELECT @xml_doc =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK '\\TAX192\C$\TaxApps\Pvr\NewGL.xml', SINGLE_BLOB)
AS x

SELECT @xml_doc
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml_doc

etc . . .

Most likely, something is not right in the "SELECT @xml_doc = " statement. I
got that syntax from discussion group posts during a frantic several hours of
searching.

Can you identify the problem swiftly and send me on my way? Thanks -- once
again!

Joel ( XXXX@XXXXX.COM ) writes:

Which indicates that you start a transaction that you never commit or
rollback.


And indeed:

RETURN 0
COMMIT TRAN

:-)


Uh-uh, that sp_xml_prearedocument is old stuff from SQL 2000. Rather
that and OPENXML, use the type methods .nodes and .values. You don't
have to jazz with any system procedure, and it is more effeicient.

I have a very quick example on
http://www.sommarskog.se/arrays-in-sql-2005.html #XML.

I see in your procedure that you use element-centred XML. If you have
control over what Access generates, go for attribute-centred instead.
It's more compact, and when using .nodes it's more effecient than
element-based.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

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

oday I covnerted from OPENXML in my stored procedure to using .nodes, as is
recommended. Although I followed my examples as best I could, the T-SQL in my
revised stored procedure will not execute without error.

Admittedly, some creative guesswork has been employed here on my part -
because I was unsure how the structure of my XML data relates to the example
on your "arrays-in-sql-2005" site. Your article was helpful for sure. But my
lack of experience won the day. Three questions, if I may:

1) I have doubts about that "Select Into" statement I put in there - Does it
belong?

2) Regarding the particular error currently preventing successful execution:
Incorrect syntax near '='. It is raised on the first line of the SELECT
statement . . . .

SELECT
YEARGL = T.Item.value('../../@YEARGL', 'smallint'),
TCODEGL = T.Item.value('@TCODEGL', 'int'),
SPANGL = T.Item.value('@SPANGL', 'nvarchar(13)',

............................

3) And in the FROM statement . . . I have no confidence in the path I have
used:

@xml_doc.nodes('/Temp_GLData') as T(Item)
Should there be more than one node? A "root" node perhaps?

The full stored procedure from today is located at:
http://www.state.vt.us/tax/pdf.word.excel/pvr/txrpdfs/XmlFiles.zip


Also today I've determined that I can't control element-centered v.
attribute-centered coming out of the Access export. Looks like I have to use
element-based XML.


"Erland Sommarskog" wrote: