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
63 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

813 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

9 Experts available now in Live!

Get 1:1 Help Now