I am trying to export in a fixed format file.

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)

I am trying to export in a fixed format file.

Author

Topic

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 06:49:41

I get the following error. What am I doing wrong?

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file

bcp.fmt
-----------
8.0
3
1 sybchar 0 03 "" 1 col1
2 sybchar 0 10 "" 2 col2
3 sybchar 0 02 "\r\n" 3 col3

bcp.dat
-----------
100Text01 10
101Text02 11
102Text03 12
103Text04 13

create table tab1
(col1 int NULL,
col2 Char(5) NULL,
col3 int NULL
)

bcp srm..tab1 in c:\bcp.dat -Sbox1\sql1 -Usa -Pdba1 -fc:\bcp.fmt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-03 : 07:38:31

If you're using SQL Server 7.0, change the 8.0 on the first line of the format file to 7.0.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 08:07:50

I am using sql server 2000. Is 8.0 still valid then.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-03 : 08:12:51

Well, if it's causing an error I'd say it's not valid. Are you sure the database is not set to 7.0 compatibility?

You should also check the version of bcp you're running. If you're running it on your local machine, you may have an earlier version. Copy the bcp.exe file from the SQL Server and try it.

If that fails, you should do a bcp out and let it generate a format file for you, you can edit it afterwards if need be.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 08:31:40

Its defanitly a sql server 2000 and its installed very recently. It also has the sp3a.

How do I check the version of bcp?

I am not so sure if generating the format file will help. Becuase data is coming from somewhere else(not sql server) and the format is as above. I am trying to wrie a bcp.fmt file that would read and import data in the above format.

by the way what flag do you give on the bcp command to generate format file.

Thanks for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 08:50:11

sybchar s.b. SYBCHAR (this causes the problem)
and I think you need the collation try

8.0
3
1 SYBCHAR 0 03 "" 1 col1 Latin1_General_CI_AS
2 SYBCHAR 0 10 "" 2 col2 Latin1_General_CI_AS
3 SYBCHAR 0 02 "\r\n" 3 col3 Latin1_General_CI_AS

Also the table onlt accepts 5 chars for col2 so that will give a truncation error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 08:53:54

Also is SYBCHAR an old type - think it is SQLCHAR now although SYBCHAR seems to be accepted

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 10:31:29

I am one step closer now. current bcp.fmt file is:

bcp.fmt
------------
8.0
3
1 SQLCHAR 0 03 "" 1 col1 Latin1_General_CI_AS
2 SQLCHAR 0 10 "" 2 col2 Latin1_General_CI_AS
3 SQLCHAR 0 02 "\r\n" 3 col3 Latin1_General_CI_AS

However, it is not copying any rows into table.

bcp TestDb..tab1 in c:\temp\bcp\bcp.dat -Slocalhost -T -c -e error.out

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 14:28:56

Do you have a crlf at the end of the last line in the data file?
In the bcp command you have there you don't reference the format file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 15:21:56

Yes I do have crlf at the end of file and you are right that I missed the -f option. Now with -f option I am back to my original problem.
Does the bcp format file have to be in fixed length

bcp TestDb..tab1 in c:\temp\bcp\bcp.dat -fc:\temp\bcp\bcp.fmt -Slocalhost -T

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file

Does bcp format file's field have to be spaced out in any particular length?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 16:04:05

Nope - what you have looks correct.
The error now is from an incorrect format - like missing out the column name but yours is ok.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 16:13:58

Exactly, so I dont understand what it complains about. This is the latest format file and the data file.

bcp.fmt
---------
8.0
3
1 SQLCHAR 0 03 "" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 02 "\r\n" 3 col3 SQL_Latin1_General_CP1_CI_AS

bcp.dat
--------
100Texts1 10
101Texts2 11
102Texts3 12
103Texts4 13

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 16:28:11

I take it you have 4 spaces after the texts?
And a crlf at the end of the last lines for both data and fmt?
Works fine on my m/c

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spatemp
Starting Member

11 Posts

Posted - 2005-03-03 : 19:33:34

NR,

Thanks so much for your help. As you can probably tell that this was my first time with bcp utility and it is so picky I might add.

You are right I did not have a crlf in the bcp.fmt file. Once I added a crlf it worked just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources