BCP: Unable to open host data file

We need to run the BCP command in the SQL server but the .txt file from which
it should be imported or exported is in the client machine (the folder is
shared).
exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
[login id] -P [password]-S [Server Name]-c -t\t -r\n '
Throws an error.
"Unable to open BCP Host data file.".Geeta
Permissions?
"Geeta" <Geeta@.discussions.microsoft.com> wrote in message
news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> We need to run the BCP command in the SQL server but the .txt file from
which
> it should be imported or exported is in the client machine (the folder is
> shared).
> exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
> [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> Throws an error.
> "Unable to open BCP Host data file.".
>
>|||SQL server login is sa .
the system login has full permission on the shared folder.
"Uri Dimant" wrote:
> Geeta
> Permissions?
> "Geeta" <Geeta@.discussions.microsoft.com> wrote in message
> news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> > We need to run the BCP command in the SQL server but the .txt file from
> which
> > it should be imported or exported is in the client machine (the folder is
> > shared).
> >
> > exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
> > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> >
> > Throws an error.
> > "Unable to open BCP Host data file.".
> >
> >
> >
> >
>
>|||Hi
> > > exec XP_cmdshell 'BCP [table name] in "[file path- in client
machine]" -U
> > > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
DataFile.txt:
"Data1","Data22","Data333"
"Data1","Data22","Data333"
"Data1","Data22","Data333"
FormatFile.fmt:
7.0
4
1 SQLCHAR 0 1 "\"" 0 Quote1
2 SQLCHAR 0 10 "\",\"" 1 Column1
3 SQLCHAR 0 10 "\",\"" 2 Column2
4 SQLCHAR 0 10 "\"\r\n" 3 Column3
USE tempdb
GO
CREATE TABLE TestTable(
Column1 varchar(10) NOT NULL,
Column2 varchar(10) NOT NULL,
Column3 varchar(10) NOT NULL
)
GO
BCP tempdb..TestTable in c:\temp\DataFile.txt /T /fc:\temp\FormatFile.fmt
"Geeta" <Geeta@.discussions.microsoft.com> wrote in message
news:323D8765-7636-49C3-87BE-A43746F82C7F@.microsoft.com...
> SQL server login is sa .
> the system login has full permission on the shared folder.
> "Uri Dimant" wrote:
> > Geeta
> > Permissions?
> > "Geeta" <Geeta@.discussions.microsoft.com> wrote in message
> > news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> > > We need to run the BCP command in the SQL server but the .txt file
from
> > which
> > > it should be imported or exported is in the client machine (the folder
is
> > > shared).
> > >
> > > exec XP_cmdshell 'BCP [table name] in "[file path- in client
machine]" -U
> > > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> > >
> > > Throws an error.
> > > "Unable to open BCP Host data file.".
> > >
> > >
> > >
> > >
> >
> >
> >

BCP utility help

I want to bcp out the record set in a flat file. I am unable to write the
correct script to do this.
What I am trying to do is build a text comma delimited file by running a
stored procedure say procTest. This bcp command will be executed in a nightl
y
job.
Please help.David (David@.discussions.microsoft.com) writes:
> I want to bcp out the record set in a flat file. I am unable to write the
> correct script to do this.
> What I am trying to do is build a text comma delimited file by running a
> stored procedure say procTest. This bcp command will be executed in a
> nightly job.
BCP db.dbo.tbl out tblout.bcp -T -c -t,
This is a command-line operation. To run it from a stored procedure,
you would have to call xp_cmdshell to spawn out to command-line level.
Now, when you say comma-delimited, do you in fact mean something like:
"value",2,"other value",98
then it gets trickier, particularly if the first column needs a quote.
If the first column needs a quote, you can use a formar file. If the
first column needs a quote, you will need to use the queryout option, or
define a view or possibly use a global temp table. Queryout appears to
give people headache, so I would stay away from that one.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The line to tell BCP to import a comma separated file to SQL table is:
-t,
It's so tricky.
*** Sent via Developersdex http://www.examnotes.net ***

bcp error: Unable to open BCP host data-file

I get this error message:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

if I try to run

EXEC master..xp_cmdshell
'bcp TestDB.dbo.Employees out \\Ioana\Export\Employees.txt -c -t"," '

from Query Analyzer (or a store procedure).

If run the same command in cmd or run window (of course without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...

If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...

Thanking in AdvanceMy guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.|||Originally posted by MCrowley
My guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.

SQL Server is install on SQLSERVER machine and Ioana is my machine.
I want to run this script on SQLSERVER machine (in Query Analyzer) and to export the file in Export directory on my machine. The Export directory on my machine is shared with all the rights.

If run the bcp from cmd window on SQLSERVER machine is working fine. From Query Analyzer on the same machine (SQLSERVER) is not working even if I am logged in as system administrator with windows authentication. The administrator of SQLSERVER have full permision on my shared directory.

Thanks again

BCP error

I am trying to run a bcp import and I am getting the following error:
Unable to Load BCP resource dll. BCP cannot continue.
Does anyone have any idea what might cause this error?What happens when you run this from the command line?
C:\>bcp -v
You should get something like:
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
Version: 8.00.382
If you do get that, show us what command you are issuing so we can
troubleshoot further. If you still get "Unable to Load BCP resource dll.
BCP cannot continue." then your SQL Server installation is damaged.
We can then look into this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
msdn.microsoft.com wrote:
> I am trying to run a bcp import and I am getting the following error:
> Unable to Load BCP resource dll. BCP cannot continue.
> Does anyone have any idea what might cause this error?
>