OPENROWSET BULK simple example, puzzling error

[RRinTetons]

SQL Server 2005, 2008

Working from various web page examples of OPENROWSET I created two files on the server:

C:\RowSetWork\values.txt
C:\RowSetWork\format.fmt

values.txt looks like:

1     Data Item 1
2     Data Item 2
3     Data Item 3

format.fmt is:

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

I call:

SELECT a.* 
FROM OPENROWSET(BULK 'C:\RowSetWork\values.txt',
		FORMATFILE = 'C:\RowSetWork\format.fmt') AS a;

and get an error:

Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\RowSetWork\format.fmt" could not be read. Operating system error code (null).

I can paste that file path into a command window and the file opens right up, ditto values.txt.

I'm stumped. That's about as simple as I can make it and it's not working.

????

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

I found a FAQ that I used to get this to work, but I have no idea why it's required.

The FAQ is at

http://www.tek-tips.com/faqs.cfm?&rat1=5&fid=1602

Anybody know why I need all the 'extra' stuff?

-
Richard Ray
Jackson Hole Mountain Resort