Re: [GRAYMAIL] freebcp fails on identity column on MS SQL

> On Sep 4, 2016, at 5:41 AM, Frediano Ziglio <[email protected]> wrote:
> 
> 2016-07-21 16:03 GMT+01:00 John Kendall <[email protected]>:
>> Frediano,
>> Thanks for the fix to freebcp for Sybase identity columns.  I finally had a
>> chance to do more tests and freebcp (1.0.11) has a problem with identity
>> columns on MS SQL:
>> 
>>   select id_col=identity(int) into tempdb..tbl
>> 
>>   $ freebcp tempdb..tbl out tbl.bcp -S $DSQUERY -U usr -P pw -c
>> 
>>   Starting copy...
>>   1 rows copied.
>> 
>>   $ freebcp tempdb..tbl in tbl.bcp -S $DSQUERY -U usr -P pw -c
>> 
>>   Starting copy...
>>   Msg 102, Level 15, State 1
>>   Server 'ATHENA', Line 1
>>           Incorrect syntax near ')'.
>>   Msg 20018, Level 15
>>   General SQL Server error: Check messages from the SQL Server
>> 
>>   bcp copy in failed
>> 
>> The same error occurs when using the -n or -c.
>> There is no error when adding the -E option.
>> 
>> Thanks.
>> John
>> 
> 
> Hi,
>   can you try this change?
> 

I seem to get the exact same error. 

> 
> diff --git a/src/tds/bulk.c b/src/tds/bulk.c
> index ffd7fc1..a9baa17 100644
> --- a/src/tds/bulk.c
> +++ b/src/tds/bulk.c
> @@ -301,7 +301,10 @@ tds_bcp_start_insert_stmt(TDSSOCKET * tds,
> TDSBCPINFO * bcpinfo)
>                        return TDS_FAIL;
>                }
> 
> -               erc = asprintf(&query, "insert bulk %s (%s)%s",
> tds_dstr_cstr(&bcpinfo->tablename), colclause.pb, hint);
> +               if (firstcol)
> +                       erc = asprintf(&query, "insert bulk %s
> (%s)%s", tds_dstr_cstr(&bcpinfo->tablename), colclause.pb, hint);
> +               else
> +                       erc = asprintf(&query, "insert bulk %s%s",
> tds_dstr_cstr(&bcpinfo->tablename), hint);
> 
>                free(hint);
>                if (colclause.from_malloc)
> 
> 
> Frediano
> 
>> 
>>> On Jun 27, 2016, at 5:12 AM, Frediano Ziglio <[email protected]> wrote:
>>> 
>>> Fixed
>>> 
>>> 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
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>> 
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds