Win7 OpenRowSet Bulk data access problem

Question

Hello All,

I'm trying to insert the content of a binary file into the a db table using VARBINARY(MAX) with filestream attribute. The insert query fails when it tries to read the file stating that it is not possible to read the file. The filepath is correct and I could open the file normaly.

My insert query is "INSERT INTO setup_data (SETUP_ID, TYPE_ID, PATH, LENGHT, UNIQUEID, DATA, NOTES) VALUES (24, 0, 'D:\prova.pdf', 659803, newid(), (SELECT * FROM OPENROWSET(BULK N'D:\prova.pdf', SINGLE_BLOB) AS t".

When I try to exec the query, the file D:\prova.pdf is said not to be there:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Impossibile eseguire il caricamento bulk perché non è stato possibile aprire il file "D:\prova.pdf". Codice di errore del sistema operativo 21.

Finaly it says that it was not possible to open the file.

Executing the same on a WinXP, it works. Unfortunately I dodn't find any file permission setting that could solve the problem. Does anybody have some hints for me??

Thanks a lot and have a nice evening!!

Marc

Answer 1

This is a rights issue. 

The question is: When you run your command whose permissions is it using?  

• Is it your personal permissions and do you personally have access to the file?
• Is it the SQL Server's service account and does it have permissions to the file?
• Are you running a SQL Agent job (SQLCMD, SSIS Job, etc) that uses a proxy account and does that account have the needed permissions?

When you know "who" is running the command then you can ensure that account has the needed permissions.

If, for example, you personally have access to the file, then you know you are not running under your permissions.  That leaves the other possibilities for investigation.

RLF

Answer 2

Thank you very much for your reply.

When I execute the programm I see in the TaskManager that it is executed using my personal account as account name.

When I browse to the file location and try to pen it, it opens normaly without any restriction. That sounds strange to me :-(.

I get the same behavior executing the application from the VisualStudio and from the command shell.

Thanks again for your help!

Have a nice day!

Marc

Answer 3

Could it be that it is the file shadow service of window7 that changes the path for the file when I access it and when my application accesses it?

I mean: I see the file in D:\prova.pdf, but phisically the file is stored somewhere else, therefore the application looks into the wrong location?

Thanks again and have a nice day!

Marc

Answer 4

Hmmm... there is something I'm missing.

The path provided in the BULK operation has to be local for the DB server and not local for the remote computer?

I was convinced that the client loads the file accessing it locally and then sends it over to the server. My mistake.

So, to be able to upload a file from a remote computer, this file must be visible through the network for the sql server. Is that right?

Thanks a lot and sorry for this newbe issues :-))

Have a nice day!!

Marc

Answer 5

Correct.  The SQL Server must have rights to the location of the data.  If you have the needed rights, then you should be able to use the full path, something like:  \\server\share\folder\file.txt

RLF