Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problem with the folder given

Answered Problem with the folder given

  • Friday, January 11, 2013 8:12 AM
     
     
    Sign In to Vote
    0

    Hi,
    This statement is working fine and also the image file goes to the table in its original format.
    update dbo.user_master
    set photo_file=(SELECT BulkColumn
    FROM OPENROWSET(BULK N'C:\t93.jpg', SINGLE_BLOB) AS SRC)
    where user_abbr='pc1';

    But why do I get the problem for this, while "C:\setup\t93.jpg" is surely existing in the server with fine file format?
    update dbo.user_master
    set photo_file=(SELECT BulkColumn
    FROM OPENROWSET(BULK N'C:\setup\t93.jpg', SINGLE_BLOB) AS SRC)
    where user_abbr='pc1';

    Msg 4861, Level 16, State 1, Line 2
    Cannot bulk load because the file "C:\setup\t93.jpg" could not be opened. Operating system error code 5(Access is denied.).

    How to make sure the DB user is able to access the folders of the server machine?

    If you see the statements I've run above, one is with "c:\setup" which leads to the problem, one with "c:\" which is fine. This means the current DB user is having access to "c:\" while not to "c:\setup", as I am using the same DB user for doing the above. Then where is the control to make "c:\setup" "available/seeable" to the current DB user. Thanks.

    Many Thanks & Best Regards, Hua Min


     

All Replies

  • Friday, January 11, 2013 8:24 AM
     
     
    Sign In to Vote
    0

    The SQL Server service account does not have sufficient rights to access that folder.

    Start-->SQLServerManager.msc-->Right click the server-->property-->log on as.

    Find the account name and check the account name has enough permission to access the path.

    The other case is as follows in the below link:

    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/10/29/sql-bulk-copy-error-operating-system-error-code-5-access-is-denied.aspx

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Friday, January 11, 2013 8:30 AM
     
     
    Sign In to Vote
    0

    Thanks. Do you mean Right-click the instance to see this

    Right click the server-->property-->log on as?

    But I don't see "log on as". My SS is 2012 version.

    Many Thanks & Best Regards, Hua Min


     
  • Friday, January 11, 2013 10:50 AM
     
     
    Sign In to Vote
    0

    Since 2008 R2 there exists  sys.dm_server_services DMV  http://msdn.microsoft.com/en-us/library/ms176083.aspx which will show  SS services accounts as well.

    Serg

     
  • Friday, January 11, 2013 11:20 AM
     
     
    Sign In to Vote
    0
    You may even check with the services.msc and check the log on user for the SQLService.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Friday, January 11, 2013 12:01 PM
     
     Answered
    Sign In to Vote
    0

    As other says, Its a permission issue. Check the SQL service log on account & provide access on your folder.

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.