?
Solved

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

Posted on 2016-09-19
6
Medium Priority
?
181 Views
Last Modified: 2016-09-29
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
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41805337
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
 

Author Comment

by:johnnyg123
ID: 41805401
Appreciate the feed back!

My main issue is what I am missing permission wise

If someone could please comment on that
0
 

Author Comment

by:johnnyg123
ID: 41805402
As I mentioned in the original question....the only difference between whether it works or not is the permission level
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41806616
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
 

Author Comment

by:johnnyg123
ID: 41819857
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
 

Author Closing Comment

by:johnnyg123
ID: 41821771
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question