[freetds] freebcp fails on identity column on MS SQL

John Kendall john at capps.com
Thu Jul 21 11:03:46 EDT 2016


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


> On Jun 27, 2016, at 5:12 AM, Frediano Ziglio <freddy77 at gmail.com> wrote:
> 
> Fixed
> 
> Frediano
> 
> 
> 2016-06-26 9:39 GMT+01:00 John Kendall <john at capps.com>:
>> 
>>> On Jun 26, 2016, at 12:34 AM, Frediano Ziglio <freddy77 at gmail.com> wrote:
>>> 
>>> 2016-06-22 16:01 GMT+01:00 John Kendall <john at capps.com>:
>>>> 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
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds



More information about the FreeTDS mailing list