bcp problem

this command:

bcp MyDatabase..MyTable in Data\2005\MyFile.bcp.dat /f bcp.fmt /S MyServer /U sa

runs fine when run from its original location on a network drive.

When we copy the files referenced down to the C: drive and execute the command (with the path adjusted appropriately) and run the command I get an error:

Unable to open BCP host data-file

both MyFile.bcp.dat and bcp.fmt exist in the locations specified in the new command line. MyTable exists in MyDatabase. Etc. etc.

Why do we get this error? Are there other files secretly needed?

Thanks,

Jon

hi i 'm also facing the same problem could anybody reply if u have the solution...

i have tried bcp out command to a network drive which give the error

sql state = s1000

could not open host data file

please do help me.

jollyguyjolly@.hotmail.com

BCP Help - Incorrect host-column number found in BCP format-file

I've used BCP before, so I understand how to use it for the most part.
But, I'm trying to do something different.
I created a fmt file from my table. But, what I want is to only import
say column 50. So, this is how I changed it.
8.0
50
50 SQLCHAR 0 510 "\t" 41 COUNTY
That was just a guess.
Originally it was
8.0
108
then all the colums.
The text file I am processing includes all 108 columns.
So, my question is...Is what I am trying to do possible using the same
text file, but only wanting to import 1 column? (Please understand, I
will really be importing about 50 but this is an example)
*** Sent via Developersdex http://www.examnotes.net ***[posted and mailed, please reply in news]
Joey Martin (joey@.infosmiths.net) writes:
> I've used BCP before, so I understand how to use it for the most part.
> But, I'm trying to do something different.
> I created a fmt file from my table. But, what I want is to only import
> say column 50. So, this is how I changed it.
> 8.0
> 50
> 50 SQLCHAR 0 510 "\t" 41 COUNTY
> That was just a guess.
> Originally it was
> 8.0
> 108
> then all the colums.
> The text file I am processing includes all 108 columns.
> So, my question is...Is what I am trying to do possible using the same
> text file, but only wanting to import 1 column? (Please understand, I
> will really be importing about 50 but this is an example)
No, this is perfectly possible. If you already have a format file with
all the 108 fields, just put a 0 after the field delimiter after the
fields you don't care about. This means that this field in the file does
not map to a database column.
A non-zero value, for instance 41 as above, means that you want to
map that field to the 41th column. The name that follows the number
is informational only, and carries no meaning.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP generates 0x00 characters

Hello,
I'm using BCP with a .FMT file to generate exports of some tables.
BCP sometimes messes up the file by placing 0x00 characters (ASCII value 00)
in the export. It looks like BCP translates NULL values or empty fields like
this.
Strange enough, this doesn't happen all the time. It will happen every once
in a while (data changes all the time). When it happens, it messes up the
entire file.intGod (intGod@.discussions.microsoft.com) writes:
> I'm using BCP with a .FMT file to generate exports of some tables.
> BCP sometimes messes up the file by placing 0x00 characters (ASCII value
> 00) in the export. It looks like BCP translates NULL values or empty
> fields like this.
> Strange enough, this doesn't happen all the time. It will happen every
> once in a while (data changes all the time). When it happens, it messes
> up the entire file.
My guess the 0x00 characters are there, because they appear in the data.
How does your format file look like?
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|||"Erland Sommarskog" wrote:
> My guess the 0x00 characters are there, because they appear in the data.
> How does your format file look like?
Dear Erland,
I found the root of the problem but I don't know why BCP behaves like this.
The problem is: sometimes (only sometimes) BCP will treat an empty field
incorrectly and insert a 0x00 character instead of the string. By using a
CASE on that field (case fieldName when '' then ' ' else fieldName), the
problem is solved. Still, it's annoying.
I've never come across this problem but I've never used .fmt files before.
I've always used tab-delimited.|||intGod (intGod@.discussions.microsoft.com) writes:
> I found the root of the problem but I don't know why BCP behaves like
> this.
> The problem is: sometimes (only sometimes) BCP will treat an empty field
> incorrectly and insert a 0x00 character instead of the string. By using a
> CASE on that field (case fieldName when '' then ' ' else fieldName), the
> problem is solved. Still, it's annoying.
> I've never come across this problem but I've never used .fmt files before.
> I've always used tab-delimited.
Since you did not post the format file, I cannot comment much more. But
it appears that you use the queryout option. I have not use queryout myself
much, but I have the impression that it is considered buggy.
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

BCP fmt and Create Table

Hello,
I am being sent a fmt file and a data file without headers which I
would like to import into my sql 2000 server. However the data files
have couple of 100's of columns, how could I avoid manually creating a
table. Can I create a table using a fmt file thru bcp/bulk insert?

Thanks
sd<danths@.gmail.com> wrote in message
news:1117041460.483784.193120@.g47g2000cwa.googlegr oups.com...
> Hello,
> I am being sent a fmt file and a data file without headers which I
> would like to import into my sql 2000 server. However the data files
> have couple of 100's of columns, how could I avoid manually creating a
> table. Can I create a table using a fmt file thru bcp/bulk insert?
> Thanks
> sd

I don't believe there's an automatic way to create a table using only a
format file, but why not just ask the person sending the data to include a
CREATE TABLE script?

If that's not possible (which would be strange), one solution would be to
write a script to parse format files and produce the corresponding CREATE
TABLE statement - if you'll need to do this often, and if the data will
always be in different formats, it could be worth it.

Alternatively, for a one-off task, it might be easier to use DTS via the
Import and Export Data Wizard. That will give you the option to create the
destination table, although depending on what's in the format file, you
might have to spend a lot of time rearranging column order and so on.

But getting the table script is probably the best option, otherwise there's
no way to know what the exact data types are, what the key columns are,
which columns are NULL/NOT NULL, if there are any CHECK constraints etc.

Simon

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David Lozzi
Hi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John
|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John
|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We nee
d
> to use another delimiter and I'm wondering if we can use multiple characte
rs
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>> Howdy,
>> I have a fmt file used for importing data from a IBM database into SQL.
>> Currently we're using the pipe | as a delimiter and it works great. We
>> need
>> to use another delimiter and I'm wondering if we can use multiple
>> characters
>> like %$. If so, is it just as easy as changing the delimiter in the fmt
>> file?
>> Thanks,
>> David Lozzi
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> > Hi David
> >
> > "David Lozzi" wrote:
> >
> >> Howdy,
> >>
> >> I have a fmt file used for importing data from a IBM database into SQL.
> >> Currently we're using the pipe | as a delimiter and it works great. We
> >> need
> >> to use another delimiter and I'm wondering if we can use multiple
> >> characters
> >> like %$. If so, is it just as easy as changing the delimiter in the fmt
> >> file?
> >>
> >> Thanks,
> >>
> >> David Lozzi
> >>
> > The field terminator can be a string of up to 10 printable characters. See
> > http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> > http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> > file is a text file so it can be edited with any text editor.
> >
> > John
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

bcp

Hello World,
I have to load table from a a delimited file. The delimited char is (";").
Bcp is used with a format file (*.fmt).
First step :
The table contain a date field.
In the import file, the date format is : DD/MM/YYYY.
When the import runs, I obtain the message like : right truncating, so the
import stops.
Second step :
The struct table is modified with a varchar (10) for the date field.
The format file is modified.
The import runs well.
My question is :
Can we import directly date field from a file with bcp ?
If it is possible, how can we do it ?
Thank's in advance.
MLTry reformatting the source file so that the datatime values are in an
unambiguous format "yyyymmdd", or (which is my favourite) connect to the fil
e
using sp_addlinkedserver and describe it's format using the schema.ini file,
which enables you to query the file as if it were a regular SQL table.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
(Example H.)
...and here:
http://msdn.microsoft.com/library/d...ma_ini_file.asp
ML|||Hi
I would expect that you would get a message about the date being invalid if
dates were being interpretted as MM/DD/YYYY, therefore it may not be your
date field where this error message is being generated!!!
You may want to post your format file, command and a small section of
example data that re-creates this problem.
John
"Michel" wrote:

> Hello World,
> I have to load table from a a delimited file. The delimited char is (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating, so the
> import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
>
> Thank's in advance.
> ML
>
>|||Michel wrote:
> Hello World,
> I have to load table from a a delimited file. The delimited char is
> (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating,
> so the import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
You can use DTS and define a transformation for this column.
You could also try to not use bcp but do it from QA: change the session's
date format and use BULK INSERT (untested)
http://support.microsoft.com/defaul...kb;en-us;173907
http://msdn.microsoft.com/library/d...br />
4fec.asp
Kind regards
robert