[freetds] freebcp issue connecting to mssql

Michel Cunha mcunha at n-able.com
Tue Nov 14 15:06:55 EST 2006


Thank for the help,

I think the problem is located in the login process beetween freebcp and mssql 
not the format file.

I still get the same error msg if I give an wrong file name for the file 
format.

I also did a tcp dump. Here the latest ones:

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 3c a8 63 40 00 40 06 d3 78 c0 a8 14 ec c0 a8  .<.c at .@..x......
0020   28 a3 c0 91 05 99 d2 6d 9d 62 00 00 00 00 a0 02  (......m.b......
0030   16 d0 02 51 00 00 02 04 05 b4 04 02 08 0a 05 08  ...Q............
0040   35 01 00 00 00 00 01 03 03 02                    5.........

sql server anwser
0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 40 9e 28 00 00 7f 06 de af c0 a8 28 a3 c0 a8  . at .(........(...
0020   14 ec 05 99 c0 91 27 74 ca 8c d2 6d 9d 63 b0 12  ......'t...m.c..
0030   40 00 0f 14 00 00 02 04 05 b4 01 03 03 00 01 01  @...............
0040   08 0a 00 00 00 00 00 00 00 00 01 01 04 02        ..............

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 34 a8 64 40 00 40 06 d3 7f c0 a8 14 ec c0 a8  .4.d at .@.........
0020   28 a3 c0 91 05 99 d2 6d 9d 63 27 74 ca 8d 80 10  (......m.c't....
0030   05 b4 50 21 00 00 01 01 08 0a 05 08 35 02 00 00  ..P!........5...
0040   00 00                                            ..

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   02 80 a8 65 40 00 40 06 d1 32 c0 a8 14 ec c0 a8  ...e at .@..2......
0020   28 a3 c0 91 05 99 d2 6d 9d 63 27 74 ca 8d 80 18  (......m.c't....
0030   05 b4 c1 52 00 00 01 01 08 0a 05 08 35 06 00 00  ...R........5...
0040   00 00 02 00 02 00 00 00 00 00 68 6f 73 74 2d 32  ..........host-2
0050   33 36 00 00 00 00 00 00 00 00 00 00 00 00 00 00  36..............
0060   00 00 00 00 00 00 00 00 08 61 64 6d 69 6e 00 00  .........admin..
0070   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0080   00 00 00 00 00 00 00 05 61 64 6d 69 6e 00 00 00  ........admin...
0090   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
00a0   00 00 00 00 00 00 05 33 37 38 37 36 00 00 00 00  .......37876....
00b0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
00c0   00 00 00 00 00 05 03 01 06 0a 09 01 00 00 00 00  ................
00d0   02 00 00 00 00 00 46 72 65 65 42 43 50 00 00 00  ......FreeBCP...
00e0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
00f0   00 00 00 00 07 31 39 32 2e 31 36 38 2e 34 30 2e  .....192.168.40.
0100   31 36 33 00 00 00 00 00 00 00 00 00 00 00 00 00  163.............
0110   00 00 00 0e 61 64 6d 69 6e 00 00 00 00 00 00 00  ....admin.......
0120   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0130   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0140   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0150   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0160   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0170   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0180   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0190   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01a0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01b0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01c0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01d0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01e0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
01f0   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0200   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0210   00 00 00 05 04 02 00 00 44 42 2d 4c 69 62 72 61  ........DB-Libra
0220   72 79 0a 00 00 00 00 00 0d 11 75 73 5f 65 6e 67  ry........us_eng
0230   6c 69 73 68 00 00 00 00 00 00 00 00 00 00 00 00  lish............
0240   00 00 02 01 00 4c 00 00 00 00 00 00 00 00 00 00  .....L..........
0250   0a 00 00 00 00 00 00 00 00 00 00 00 00 00 00 69  ...............i
0260   73 6f 5f 31 00 00 00 00 00 00 00 00 00 00 00 00  so_1............
0270   00 00 00 00 00 00 00 00 00 00 00 00 00 05 01 35  ...............5
0280   31 32 00 00 00 03 00 00 00 00 00 00 00 00        12............

sql server anwser
0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 d2 9e 29 40 00 7f 06 9e 1c c0 a8 28 a3 c0 a8  ...)@.......(...
0020   14 ec 05 99 c0 91 27 74 ca 8d d2 6d 9f af 80 18  ......'t...m....
0030   fd b3 9d f4 00 00 01 01 08 0a 00 3d 7a 75 05 08  ...........=zu..
0040   35 06 04 01 00 9e 00 3a 01 00 e3 0f 00 01 06 6d  5......:.......m
0050   63 75 6e 68 61 06 6d 61 73 74 65 72 ab 3c 00 45  cunha.master.<.E
0060   16 00 00 02 00 25 00 43 68 61 6e 67 65 64 20 64  .....%.Changed d
0070   61 74 61 62 61 73 65 20 63 6f 6e 74 65 78 74 20  atabase context 
0080   74 6f 20 27 6d 63 75 6e 68 61 27 2e 0b 51 41 2d  to 'mcunha'..QA-
0090   4e 43 4f 4d 50 41 53 53 00 00 00 e3 09 00 03 05  NCOMPASS........
00a0   69 73 6f 5f 31 01 00 ad 20 00 01 04 02 00 00 16  iso_1... .......
00b0   4d 69 63 72 6f 73 6f 66 74 20 53 51 4c 20 53 65  Microsoft SQL Se
00c0   72 76 65 72 00 00 5f 08 00 ff e3 0a 00 04 03 35  rver.._........5
00d0   31 32 04 34 30 39 36 fd 00 00 00 00 00 00 00 00  12.4096.........
00e0  

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 34 a8 66 40 00 40 06 d3 7d c0 a8 14 ec c0 a8  .4.f at .@..}......
0020   28 a3 c0 91 05 99 d2 6d 9f af 27 74 cb 2b 80 10  (......m..'t.+..
0030   06 c0 d1 73 00 00 01 01 08 0a 05 08 35 07 00 3d  ...s........5..=
0040   7a 75                                            zu

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 4f a8 67 40 00 40 06 d3 61 c0 a8 14 ec c0 a8  .O.g at .@..a......
0020   28 a3 c0 91 05 99 d2 6d 9f af 27 74 cb 2b 80 18  (......m..'t.+..
0030   06 c0 bf 21 00 00 01 01 08 0a 05 08 35 07 00 3d  ...!........5..=
0040   7a 75 01 01 00 1b 00 00 00 00 73 65 74 20 74 65  zu........set te
0050   78 74 73 69 7a 65 20 36 34 35 31 32 20           xtsize 64512 

sql server anwser
0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 45 9e 2a 40 00 7f 06 9e a8 c0 a8 28 a3 c0 a8  .E.*@.......(...
0020   14 ec 05 99 c0 91 27 74 cb 2b d2 6d 9f ca 80 18  ......'t.+.m....
0030   fd 98 d7 5b 00 00 01 01 08 0a 00 3d 7a 75 05 08  ...[.......=zu..
0040   35 07 04 01 00 11 00 3a 01 00 fd 00 00 be 00 00  5......:........
0050   00 00 00                                         ...

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 4d a8 68 40 00 40 06 d3 62 c0 a8 14 ec c0 a8  .M.h at .@..b......
0020   28 a3 c0 91 05 99 d2 6d 9f ca 27 74 cb 3c 80 18  (......m..'t.<..
0030   06 c0 bf 1f 00 00 01 01 08 0a 05 08 35 07 00 3d  ............5..=
0040   7a 75 01 01 00 19 00 00 00 00 73 65 74 20 74 65  zu........set te
0050   78 74 73 69 7a 65 20 34 30 39 36                 xtsize 4096

sql server anwser
0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 45 9e 2b 40 00 7f 06 9e a7 c0 a8 28 a3 c0 a8  .E.+ at .......(...
0020   14 ec 05 99 c0 91 27 74 cb 3c d2 6d 9f e3 80 18  ......'t.<.m....
0030   fd 7f d7 4a 00 00 01 01 08 0a 00 3d 7a 75 05 08  ...J.......=zu..
0040   35 07 04 01 00 11 00 3a 01 00 fd 00 00 be 00 00  5......:........
0050   00 00 00                                         ...

my Linux to the sql server
0000   00 06 b1 0e 28 ab 00 0d 61 37 30 f8 08 00 45 00  ....(...a70...E.
0010   00 34 a8 69 40 00 40 06 d3 7a c0 a8 14 ec c0 a8  .4.i at .@..z......
0020   28 a3 c0 91 05 99 d2 6d 9f e3 27 74 cb 4d 80 11  (......m..'t.M..
0030   06 c0 d1 1c 00 00 01 01 08 0a 05 08 35 07 00 3d  ............5..=
0040   7a 75                                            zu

sql server anwser
0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 34 9e 2c 40 00 7f 06 9e b7 c0 a8 28 a3 c0 a8  .4., at .......(...
0020   14 ec 05 99 c0 91 27 74 cb 4d d2 6d 9f e4 80 10  ......'t.M.m....
0030   fd 7f da 5c 00 00 01 01 08 0a 00 3d 7a 75 05 08  ...\.......=zu..
0040   35 07       

and

0000   00 0d 61 37 30 f8 00 06 b1 0e 28 ab 08 00 45 00  ..a70.....(...E.
0010   00 34 9e 2d 40 00 7f 06 9e b6 c0 a8 28 a3 c0 a8  .4.- at .......(...
0020   14 ec 05 99 c0 91 27 74 cb 4d d2 6d 9f e4 80 11  ......'t.M.m....
0030   fd 7f da 5b 00 00 01 01 08 0a 00 3d 7a 75 05 08  ...[.......=zu..
0040   35 07                                            5.
                                     5.


On Tuesday 14 November 2006 14:44, Konrad J Hambrick wrote:
> Michel --
>
> 1. How did you create the bcp file ?
>
>     On UNIX / Linux then might the line
>     ending be "\n" instead of "\r\n" ?
>
> 2. I have also had better luck using
>     "SYBCHAR" than "SQLCHAR" with freebcp.
>
> 3. I don't use the Format Version 8.0.
>     I use 7.0 instead:
>
>        1 SYBCHAR 0  10  "\n"  1  col1
>
> HTH.
>
> -- kjh
>
> Michel Cunha wrote:
> > Note that In step 4 I wanted to say:
> >  run the command (Note that my DB is called mcunha, the table is called
> > allo) Michel
> >
> > On Tuesday 14 November 2006 13:44, Michel Cunha wrote:
> >>Hi can someone help me with freebcp issue
> >>
> >>Did freebcp work in the past with MSSQL server ?
> >>
> >>Here the steps that I did:
> >>
> >>1.create the bcp file
> >>a
> >>b
> >>c
> >>
> >>2.create the destination table
> >>create table allo (col1 varchar(10))
> >>
> >>3.create the format file
> >>8.0
> >>1
> >>1 SQLCHAR 0 1 "\r\n" 1 col1 Latin1_General_CI_AS
> >>
> >>4. run the command (Note that my DB is called mcunha, the table is called
> >>mcunha)
> >>freebcp mcunha..allo
> >>in /var/lib/datafilemanager/dataping_aggregated/20061104/000233/datafile3
> >>-S19x.1xx.xx.xxx:1433 -U xx -P xx -f/tmp/format
> >>
> >>(Note that I change the IP in this email for security reasons :)
> >>
> >>Then I get this error msg:
> >>Msg -100, Level 11
> >>unrecognized msgno
> >>
> >>I look at google, did not find anything that was usefull
> >>
> >>Can anyone help me with this issue ??
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

-- 
Regards,

Michel Cunha
Software Developer
N-able Technologies
450 March Road, 4th Floor
Ottawa, Ontario
Canada
K2K 3K2

www.n-able.com
mcunha at n-able.com
Tel: (613) 592-6676 x341
Toll Free: 877-655-4689 x341
Fax: (613) 592-2242


More information about the FreeTDS mailing list