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
45 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
  • 4
  • 2
6 Comments
 
LVL 26

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now