Skip to Content.
Sympa Menu

freetds - Re: [freetds] freebcp fails on identity column on MS SQL

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: John Kendall <john AT capps.com>
  • To: FreeTDS Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp fails on identity column on MS SQL
  • Date: Thu, 21 Jul 2016 15:03:46 +0000

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





Archive powered by MHonArc 2.6.24.

Top of Page