sql server bcp "SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file"

Pretty sure has something to do with sql permissions but not sure what


Created test domain user and logged in as that.   Tried to execute the following bcp command


bcp "select 'CampaignID','ListID','PlayerID','SeedList','Tier','FirstName','MName','LastName','Address1','Address2','City','State','Zip','PlayEvalBeginDate','PlayEvalEndDate','PullDate','ADT','Enroll','MailTo','Mailable','OfferDescription','OfferAmount','FCPOfferTitle','TotalOfferDescription','ADTBonusEntries','AdditionalPitBonusEntries','TotalEntries','BonusEntryText','Comments','ETLNotes' union all select char(34) + convert(varchar(1000),CampaignID) + char(34),char(34) + convert(varchar(1000),ListID) + char(34),char(34) + convert(varchar(1000),PlayerID) + char(34),char(34) + convert(varchar(1000),SeedList) + char(34),char(34) + convert(varchar(1000),Tier) + char(34),char(34) + convert(varchar(1000),FirstName) + char(34),char(34) + convert(varchar(1000),MName) + char(34),char(34) + convert(varchar(1000),LastName) + char(34),char(34) + convert(varchar(1000),Address1) + char(34),char(34) + convert(varchar(1000),Address2) + char(34),char(34) + convert(varchar(1000),City) + char(34),char(34) + convert(varchar(1000),State) + char(34),char(34) + convert(varchar(1000),Zip) + char(34),char(34) + convert(varchar(1000),PlayEvalBeginDate) + char(34),char(34) + convert(varchar(1000),PlayEvalEndDate) + char(34),char(34) + convert(varchar(1000),PullDate) + char(34),char(34) + convert(varchar(1000),ADT) + char(34),char(34) + convert(varchar(1000),Enroll) + char(34),char(34) + convert(varchar(1000),MailTo) + char(34),char(34) + convert(varchar(1000),Mailable) + char(34),char(34) + convert(varchar(1000),OfferDescription) + char(34),char(34) + convert(varchar(1000),OfferAmount) + char(34),char(34) + convert(varchar(1000),FCPOfferTitle) + char(34),char(34) + convert(varchar(1000),TotalOfferDescription) + char(34),char(34) + convert(varchar(1000),ADTBonusEntries) + char(34),char(34) + convert(varchar(1000),AdditionalPitBonusEntries) + char(34),char(34) + convert(varchar(1000),TotalEntries) + char(34),char(34) + convert(varchar(1000),BonusEntryText) + char(34),char(34) + convert(varchar(1000),Comments) + char(34),char(34) + convert(varchar(1000),ETLNotes) + char(34) From GVCBA.MarketingDirectMailLists.dbo.vDirectMail_Promo_Rocktober_MailHouseFile where campaignid = 279" QueryOut C:\testfiles\2016\October\Giveaway\Mailhouse\Giveaway_2016_October_2016_09_19_Time_12_39_MailHouse_9623.csv -c -t,  -T -S GVCBA

 I get the unable to open bcp host file info error

If I use my account and check server role of test user to serveradmin  and then log back in as test user it works fine

I tried taking out the queryout part and just running the select part (without serveradmin checked)  and works fine so not a table or view permission error

Driving me nuts!!!!!
johnnyg123Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZberteocConnect With a Mentor Commented:
It is not very clear what you mean by:

" I use my account and check server role of test user to serveradmin"

and by

"just running the select part (without serveradmin checked) "

BCP works in 2 ways when connecting to SQL server:

1. with SQL authorization, when you use a user and password
1. With trusted connection, when it will use the domain account from the context, who logged in to the box. This is what you use with -T switch.

Regardless of how you connect to the SQL server from the BCP command the permission to the location where you generate the output is based on your domain account and accessibility from that box, if we are talking about network.

So make sure that the folder exists and that you have write permission on it.


If
0
 
ZberteocCommented:
I see you are wrapping yoyr column values in ":

select
      char(34) +<some_string_value> + char(34),...

But what happens if one of those values contains " ?

What you should do is to double the " like this"

select
      char(34) +REPLACE(<some_string_value>,'"','""') + char(34),...


Make sure that the folder exists.
0
 
johnnyg123Author Commented:
Appreciate the feed back!

My main issue is what I am missing permission wise

If someone could please comment on that
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
johnnyg123Author Commented:
As I mentioned in the original question....the only difference between whether it works or not is the permission level
0
 
johnnyg123Author Commented:
Appreciate all the comments!

Let me try to further explain

I am a domain admin and I have  sysadmin for server role in sql server

When I run the process it works fine so I'm thinking it is not a file/folder not exist issue

If one of my users tries it he gets the error unless I give him a server role of sysadmin in sql

I have given him full permissions on the folder the file is trying to be written to

I am guessing there is a permission issue that occurs when he is not given server role of sysadmin in sql but do not know what that is

Hopefully this helps clarify
0
 
johnnyg123Author Commented:
After much consult with network team it had to do with permissions on file directory

For what ever reason, had to add authenticated user for the file share
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.