SQLTeam.com | Weblogs | Forums

Openrowset not locating file


#1

I have a script below to read an image and load into a table, but it can not file the local file

update Company
set cmp_logo_image = (Select * from openrowset(bulk 'C:\Projects\jinny\Database\slim_et_al\Logo_300x300\jinnyunited.jpg',single_blob) AS Image )
where cmp_id = 1

Here is the error I received: "Msg 4861, Level 16, State 1, Line 4
Cannot bulk load because the file "C:\Projects\jinny\Database\slim_et_al\Logo_300x300\jinnyunited.jpg" could not be opened. Operating system error code 3(The system cannot find the path specified.)."

What am I doing wrong?


#2

If the relevant permission is set on your server then perhaps try this:

EXEC master.dbo.xp_cmdshell 'DIR C:\Projects\jinny\Database\slim_et_al\Logo_300x300\jinnyunited.jpg'

the path needs to be AS THE SERVER SEES IT - i.e. if that is a path to a file on YOUR PC's local drive then the server won't be able to see it (well ... not as C: anyway). Sorry if I am stating the obvious.


#3

It also fails when I try this with a network drive? But the EXEC master.dbo.xp_fileexist works. What permission on a folder do you need for openrowset to work correctly?

Declare @out int
EXEC master.dbo.xp_fileexist '\jinny.com\dfs\gblGraphics\Sales_Graphics\Logo_300x300\jinnyunited.jpg', @out output

update Company
set cmp_logo_image = (Select * from openrowset(bulk '\jinny.com\dfs\gblGraphics\Sales_Graphics\Logo_300x300\jinnyunited.jpg',single_blob) AS Image )
where cmp_id = 1


#4

OK,nothing to do with SQL not being able to see it then, although I agree that openrowset may need some particular file permissions on the folder. Sorry, I don't know what they would be, and I've never done an openrowset bulk BLOB update so I can't comment on whether there is any problem with your syntax etc.

Hopefully someone else who knows about that will be along shortly.


#5

Captain obvious, but, is the SQL server also local? On the same machine as the input file? It's much easier if it is. Plus any reference to c: will refer to the drive local to your sql server, not the machine you accessing the Sql Server from.


#6

If the file is on file share on another server, the SQL Server DBE needs to run with a Domain account with access to the share