SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert via VBA - Operating system error 5


Bulk Insert via VBA - Operating system error 5

Author
Message
peter.griffin 45527
peter.griffin 45527
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27885 Visits: 39919
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!

peter.griffin 45527
peter.griffin 45527
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search