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: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp fails on identity column on MS SQL
  • Date: Sun, 4 Sep 2016 13:41:53 +0100

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?


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




Archive powered by MHonArc 2.6.24.

Top of Page