Re: freebcp fails on identity column on MS SQL
Frediano Ziglio <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <CAHt6W4e19PY4dj_X2J8vG1kcwXWbg5z8W8m_J1tX21daOdNAFA@mail.gmail.com> |
I think the problem in this case (all columns are identities) is that a "insert bulk table_name ()" is generated. Actually if there are no column and you are importing... sounds weird but probably you should just the the same number of rows with identities generated. Frediano 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 > > >> 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