Skip to Content.
Sympa Menu

freetds - Re: [freetds] [GRAYMAIL] 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] [GRAYMAIL] freebcp fails on identity column on MS SQL
  • Date: Sun, 4 Sep 2016 15:47:33 +0000



> On Sep 4, 2016, at 5:41 AM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>
> 2016-07-21 16:03 GMT+01:00 John Kendall <john AT capps.com>:
>> 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 <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
>>
>> _______________________________________________
>> 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