Bcp using Format file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bcp using Format file

Author

Topic

S.Krishnan
Starting Member

6 Posts

Posted - 2002-12-10 : 04:18:29

I have exported data from SQL 6.5 using following command

bcp dbname..tablename out txtfile -c -Uuser -Ppw

I want to do bcp in of the same data into SQL 2000 but the table has one extra column added in the new database, hence, I want to use the format file. When I try the following to generate the format file

bcp dbname..tablename format fmtfile -c -Uuser -Ppw -f

I get the following error.

SQLState = S1090, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length

How can I create a format file using bcp command.

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-10 : 04:34:19

I've never used BCP, but looking at the help, it seems like you might have the parameters wrong. It looks like you'd need to specify the data file name

bcp dbname..tablename format DATAFILENAME -fFORMATFILEPATH -c -Uuser -Ppass

-------
Moo.

S.Krishnan
Starting Member

6 Posts

Posted - 2002-12-10 : 04:51:57

Sorry. The format file path if specified also gives the error.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 00:05:15

Can you post your table and some data and also the string youre trying to execute.

S.Krishnan
Starting Member

6 Posts

Posted - 2002-12-13 : 13:52:24

bcp <<dbname>>..tablea out tablea.txt -c -UOPM1 -P -S SQL65 -t {} -r []

bcp <<dbname>>..tablea in tablea.txt -c -UOPM1 -P -S SQL2K -t {} -r []

Data file in Text format
---------------------------------

ALOW {}001399 {}Sequence no. {}[]ARCDIR{}Y {}E\backup{}[]ARCIVE{}Y {}Archive files {}[]

tablea structure in SQL65
-------------------------------------

(key char(6) not null,
value char(10) null,
narration char(30) null)

tablea structure in SQL2000
----------------------------------------

(key char(6) not null,
value char(10) null,
narration char(30) null,
new_field char(5) null) -----> New field

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 20:18:36

Here's what i've done using sql2k.
First I used the following to create a table.

CREATE TABLE tablea
(
AKey char(6) not null,
value char(10) null,
narration char(30) null,
new_field char(5) null
)

Next I created a text file called tablea.txt with the following:
ALOW {}001399 {}Sequence no. {}[]ARCDIR{}Y {}E\backup{}[]ARCIVE{}Y {}Archive files {}[]

Then I ran the following bcp command

bcp tempdb.dbo.tablea in tablea.txt -c -Usa -Pxxx -SSQL2K -t{} -r[]

I got an unexpected eof error but the data still imported correctly
when I do a select on table I get

AKey Value narration new_field
ALOW 001399 Sequence no. NULL
ARCDIR Y E\backup NULL
ARCIVE Y Archive files NULL

Maybe I don't understand the problem.
My other question for you is why don't you just add the new_field column to your 6.5 then bcp out and then bcp in to 2k.

S.Krishnan
Starting Member

6 Posts

Posted - 2002-12-16 : 06:50:05

I want to create the format file and then use the same to do the bcp in. Since, I have around 13 databases with more than 200 tables and I dont want to change in 6.5. When I try to create the format file I get the error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources