Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert via VBA - Operating system error 5 Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 10:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:48 AM
Points: 2, Visits: 14
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?
Post #1432785
Posted Tuesday, March 19, 2013 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1432846
Posted Wednesday, March 20, 2013 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:48 AM
Points: 2, Visits: 14
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.

Post #1433119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse