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.)."
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.
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
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.
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.