Bulk Insert via VBA - Operating system error 5

  • Hi,

    I hope this is the correct location for my question.

    I have written a simple piece of VBA to convert some data and save as a csv file which is then to be bulk instered into a database.

    I get the error 'Cannot bulk load because file "filename" could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).'

    When I copy the SQL string into SQL Studio is completes correctly and I have fully named the file and location or it. I have seen elsewhere on the forum similar issues which people put down to permissions, given the studio can complete the code without trouble that seems odd to me.

    Any ideas?

  • peter.griffin 45527 (3/19/2013)


    Hi,

    I hope this is the correct location for my question.

    I have written a simple piece of VBA to convert some data and save as a csv file which is then to be bulk instered into a database.

    I get the error 'Cannot bulk load because file "filename" could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).'

    When I copy the SQL string into SQL Studio is completes correctly and I have fully named the file and location or it. I have seen elsewhere on the forum similar issues which people put down to permissions, given the studio can complete the code without trouble that seems odd to me.

    Any ideas?

    Operating system error code 5 is usually permissions on the source, or the actual path really doesn't exist.

    my guess is that you are trying to call the vba via xp_cmdshell, right?

    The problem might be that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,xp_cmdshell,b cp with a "trusted" connection, sp_OA type functions etc.

    it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.

    if that is true, then xp_cmdshell is not running under the permissions you think it should be.

    here's a quick double check:

    DECLARE @Results table(

    ID int identity(1,1) NOT NULL,

    TheOutput varchar(1000))

    insert into @Results (TheOutput)

    exec master..xp_cmdshell 'whoami' --nt authority\system for example

    insert into @Results (TheOutput)

    exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

    --check some specific file path:

    insert into @Results (TheOutput)

    exec master..xp_cmdshell 'dir \\Networkpath\Sharename\Subdirectory' --NULL

    select * from @Results

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks.

    I am actually starting from VBA within a spreadsheet and executing a SQL string from within there.

    The reason I'm so confused is that running the identical string with the same source location directly in SQL Server studio works fine with no errors, then trying to do so from VBA gives me the error which, as you say, seems to be permissions based.

    I feel I may be slightly out of my depth here but was hoping this could esily be resolved.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply