0

I am trying to import data into Sybase ASE 15.7 (trial version) using BCP (version 10) on Windows. To do so, I first try to create format file for the table with following command and get an error: Copy direction must be either 'in' or 'out'. Syntax Error in 'format'.

C:\Sybase\OCS-15_0\bin>bcp TEST_EMP2 format nul -f C:\test_files\TEST_EMP2.fmt -
c -T -t,
Copy direction must be either 'in' or 'out'.
Syntax Error in 'format'.
usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out}
 [filename]
        [-m maxerrors] [-f formatfile] [-e errfile] [-d discardfileprefix]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]
        [-a display_charset] [-z language] [-v]
        [-i input_file] [-o output_file]
        [-A packet size] [-J client character set]
        [-T text or image size] [-E] [-g id_start_value] [-N] [-W] [-X]
        [-M LabelName LabelValue] [-labeled]
        [-K keytab_file] [-R remote_server_principal] [-C]
        [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
        [-y sybase directory] [-x trusted.txt_file]
        [--clienterr errfile] [--maxconn maximum_connections]
        [--show-fi] [--hide-vcc]
        [--colpasswd [[[db_name.[owner].]table_name.]column_name [password]]]
        [--keypasswd [[db_name.[owner].]key_name [password]]]
        [--initstring ASE initialization string] [--quoted-fname]
C:\Sybase\OCS-15_0\bin>

I looked up the BCP utility options on the following site and found that option to create format file or queryout is not listed there. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1550/html/utility/X14951.htm

bcp [[database_name.]owner.]table_name [: [ partition_id | slice_number ] |
    partition partition_name] {in | out} datafile
    [-f formatfile]
    [-e errfile] 
    [-d discardfileprefix]
    [-F firstrow]
    [-L lastrow]
    [-b batchsize]
    [-m maxerrors]
    [-n]
    [-c]
    [-t field_terminator]
    [-r row_terminator]
    [-U username]
    [-P password]
    [-I interfaces_file]
    [-S server]
    [-a display_charset]
    [-z language]
    [-A packet_size]
    [-J client_charset]
    [-T text_or_image_size]
    [-E]
    [-g id_start_value]
    [-N]
    [-W]
    [-X]
    [-M LabelName LabelValue] 
    [-labeled]
    [-K keytab_file]
    [-R remote_server_principal]
    [-C]
    [-V [security_options]]
    [-Z security_mechanism]
    [-Q]
    [-Y]
    [-y sybase directory]
    [-x trusted.txt_file]
    [--maxconn maximum_connections
    [--show-fi]
    [--hide-vcc]
    [--colpasswd [[[database_name.[owner].table_name.]column_name
        [password]]]
    [--keypasswd [[database_name.[owner].]key_name [password]]]

However for SQL server, BCP description shows format and queryout options in the following description of the utility. http://technet.microsoft.com/en-us/library/ms162802.aspx

_xCodexBlockxPlacexHolderx_Can anyone please tell me if the options to queryout and create format file using "format" option is not supported in BCP on Sybase ASE? Or is there a way to enable these?

I am aware that we can create format files manually, but that is not feasible as I have to create format files for 2000 tables.

I would like to know if anyone has faced similar issue to generate format files using BCP, and if there is a workaround to solve this issue.

user3496492
  • 1
  • 1
  • 1
  • check here: http://stackoverflow.com/questions/19526703/how-do-i-generate-sybase-bcp-fmt-file – dean Apr 04 '14 at 05:27

1 Answers1

3

Despite the same names and overlap in syntax, the utilities for Sybase ASE and MS SQL Server are quite different.

Sybase ASE bcp does not support queryout.

Also format files for Sybase BCP's are only used to define column datatypes, they are not for reordering/skipping or other actions that are available using SQL Server format files.

For Sybase ASE, format files aren't necessary to import or export data. They are only necessary if you do not specify -c (character mode) or -n (native mode) in your bcp command.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34