Re: bcp -n -E fails on identity column

Now I remember the "fix binary file for freebcp" in
https://trello.com/b/bk0UZNRJ/freetds-todo-list !

Basically NUMERIC is written as TDS_NUMERIC and read is completely
missing and buggy !

Actually not sure what the dump should contain.

Can you use bcp (native Ms/Sybase) to dump a file with mssql/sybase?

Frediano


2016-06-26 9:39 GMT+01:00 John Kendall <[email protected]>:
>
>> On Jun 26, 2016, at 12:34 AM, Frediano Ziglio <[email protected]> wrote:
>>
>> 2016-06-22 16:01 GMT+01:00 John Kendall <[email protected]>:
>>> My last attempt to report this was a bit muddled.  Here's another try.
>>> Using version 1.00.6.
>>>
>>> Using the bcp -n (native file format) and -E (retain identity values) options together produces:
>>>
>>>   Msg 20060, Level 11
>>>   Unknown datatype encountered
>>>
>>>   Error in bcp_colfmt col 1
>>>
>>>
>>> I see this happening on Sybase (11 & 16) and MS SQL 2008.
>>>
>>>
>>> Test to reproduce:
>>>
>>>   select id_col=identity(5) into tempdb..tbl   -- Sybase
>>>   select id_col=identity(int) into tempdb..tbl  -- MS SQL
>>>
>>>   $ freebcp tempdb..tbl out tbl.bcp -S $DSQUERY -U usr -P pw -n -E
>>>   Msg 20060, Level 11
>>>   Unknown datatype encountered
>>>
>>>   Error in bcp_colfmt col 1
>>>
>>> datacopy also has a problem with the -E option, I assume the problems are related.
>>>
>>> John
>>>
>>
>> Hi,
>>  finally found some time to look at this issue.
>>
>> Basically the sequence become:
>> 1- do a query to get row format
>> 2- initialize bcp
>> 3- do a query for identity inserts
>> 4- fill the bcp columns information with information from step 1.
>> The problem is that the step 3 clear the information needed in step 4.
>> Moving the query at step 1 after 3 (I put after bcp_control and before
>> bcp_columns) fix the issue. Not sure about datacopy.
>>
>> Can you test master (or tomorrow nightly snapshot) ?
>>
>> Frediano
>
> That fixed the bcp out, but if I then truncate the table and try to copy back into it I get (with Sybase 11 & 16):
>
> freebcp tempdb..tbl in tbl.bcp -S $DSQUERY -U usr -P pw -n -E
>
> Starting copy...
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> Msg 20219, Level 4
> Internal Conversion error
>
> bcp copy in failed
>
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds