[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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!!!!!
0
johnnyg123
Asked:
johnnyg123
  • 4
  • 2
1 Solution
 
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
 
johnnyg123Author Commented:
As I mentioned in the original question....the only difference between whether it works or not is the permission level
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ZberteocCommented:
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now