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 out and bcp in don't give the same number of rows

Hi,
I'm trying to export and import one big table in sql
server 2000 using bcp. Original table has 7,416,000 rows,
and after I bcp out this table into file in native format,
and bcp in from this file, also in native format, table
gets 7,478,000 rows and the data takes significantly less
space than before!
This table has char, varchar, numeric, datetime, and text
data types.
Can anybody tell me why the difference?
Thanks,
OJ
Hi,
Significatly less space:-
This is because your source table is fragmented heavily. The fragmentation
can be viwed by
DBCC SHOWCONTIG (Reger books online for info) command. By using this command
see the scan density, if the scan density is
too low than execute a DBCC DBREINDEX (Refer books online for usage and
info) command to remove the fragmention.
Cause for the fragmentation is because of the DML commands (Insert, Update
and Delete).
Row Difference:-
This may be bause of the inconsistency in sysindexes table. Execute the
below command to correct the inconsistency
and give the correct rows in the table. (Use the source table_name)
USE <dbname>
go
sp_spaceused <table_name>,@.updateusage = 'TRUE'
Thanks
Hari
MCDBA
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
> Hi,
> I'm trying to export and import one big table in sql
> server 2000 using bcp. Original table has 7,416,000 rows,
> and after I bcp out this table into file in native format,
> and bcp in from this file, also in native format, table
> gets 7,478,000 rows and the data takes significantly less
> space than before!
> This table has char, varchar, numeric, datetime, and text
> data types.
> Can anybody tell me why the difference?
> Thanks,
> OJ
|||Table is not fragmented at all. It is rebuilt before bcp
out. Statistics are also updated because of rebuilding the
table (clustered index with fillfactor 100)...
Thanks anyway,
OJ
>--Original Message--
>Hi,
>Significatly less space:-
>--
>This is because your source table is fragmented heavily.
The fragmentation
>can be viwed by
>DBCC SHOWCONTIG (Reger books online for info) command. By
using this command
>see the scan density, if the scan density is
>too low than execute a DBCC DBREINDEX (Refer books online
for usage and
>info) command to remove the fragmention.
>Cause for the fragmentation is because of the DML
commands (Insert, Update
>and Delete).
>Row Difference:-
>--
>This may be bause of the inconsistency in sysindexes
table. Execute the
>below command to correct the inconsistency
>and give the correct rows in the table. (Use the source
table_name)
>USE <dbname>
>go
>sp_spaceused <table_name>,@.updateusage = 'TRUE'
>--
>Thanks
>Hari
>MCDBA
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
rows,[vbcol=seagreen]
format,[vbcol=seagreen]
less[vbcol=seagreen]
text
>
>.
>
|||OJ (anonymous@.discussions.microsoft.com) writes:
> Table is not fragmented at all. It is rebuilt before bcp
> out. Statistics are also updated because of rebuilding the
> table (clustered index with fillfactor 100)...
How was it rebuilt? I've seen that when I have run DBCC DBREINDEX, there
has been a lot of extra space around, as it to have room next time
DBREINDEX is run.
How did you conclude that the number of rows were different? Did you
actually run a SELECT COUNT(*)? Or did you look at sysindexes.rows?
To get accurate values in this column - and in the space column,
run DBCC UPDATEUSAGE on the table.
If you indeed get different results with SELECT COUNT(*) before and
after running BCP, something is fishy. One alternative is that there
was some other data in the target table before you started. Also
check that the schemas of the tables are entirely identical.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

BCP Entire DB in MYSQL 2000

I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".

Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.