RE: multiple repeatable "could not bulk copy out of table" errors
- From: Raymond Mak [MSFT] <RaymondMakMSFT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 17 May 2005 21:31:02 -0700
We should be able to nail this down with a bit of persistence...
First, try running the select statement from the problematic view definition
in Query Analyzer or your favorite query execution tool. If you get the same
"Error converting data type varchar to float" then good (well, not really
:)), we have a simplified environment for experimentation. The next thing you
should try is to add "and 1 = 0" to the where clause to ensure that resulting
query doesn't return any rows. Execute the modified select statement and if
that doesn't return the same conversion error, then the problem must have
something to do with the data in your table. One thing that pops out at me
with the schema that you provided below is that [DMOM] is a computed column
that uses the str() function. According to SQL Books OnLine, str() takes a
floating point expression as the first argument but the [ORDER], [SEQ], and
[BUCKET_NO] columns that are used as the first argument to str() in [DMOM]'s
computed expression are all integer columns. My hunch is that some of the
integer values in the [ORDER], [SEQ], and [BUCKET_NO] columns cannot be
implicitly converted to float values thereby causing the conversion error.
While it doesn't really make sense to get an "Error converting data type
varchar to float." message if my hunch was indeed correct, SQL Server data
type conversion algorithm can work in mysterious way if you are forcing it to
do something that is not really possible. The only way to find out is to
exclude the [DMOM] column from the select statement and see if the resulting
query can be executed without conversion errors. Once(or if) you are
convinced that the [DMOM] computed column is the source of your trouble, you
can probably isolate the problematic rows by using a bnary search algorithm
on the ORDER column. My guess is that some of the values in the ORDER columns
are either too big or too small (as in negative) to be properly represented
as a float expression acceptable by the str() function.
-Raymond
"dwaine" wrote:
> Raymond,
> Thanks for the reply.... not really sure what to try next.
>
> The only filtering going on is Row filtering to partition data to it's
> perspective division. This is usually done by a char(3) with divisional
> qualifiers, in this case 'CCE'. Since my post, I've dropped the entire
> publication and recreated it. Now a 3rd table is erroring out which has NOT
> had it's data source changed whatsoever. Only 20 of 60 or so tables had
> their datasource/DTS changed.
>
> new Agent action and object ID/Error from snapshot output:
>
> Bulk copying snapshot data for article 'ORDB'
> select * from [dbo].[syncobj_0x3543334438453232] where 1 = 2
> [5/17/2005 2:47:57 PM]AAG0J6DW04.ODS: select * from
> [dbo].[syncobj_0x3543334438453232] where 1 = 2
>
> *** [Publication:'CCE'] Total snapshot generation time excluding publication
> setup: 661391 (ms) ***
>
> SourceTypeId = 5
> SourceName = AAG0J6DW04
> ErrorCode = 8114
> ErrorText = Error converting data type varchar to float.
> The process could not bulk copy out of table
> '[dbo].[syncobj_0x3543334438453232]'.
> Disconnecting from Publisher 'AAG0J6DW04'
>
>
> definition of view [syncobj_0x3543334438453232]as select
> [ORDER],[SEQ],[BUCKET_NO],[ORIG_PROM_DATE],[PROM_DATE],[CUST_REQ_DATE],[SCHED_QTY],[TIMES_CHANGED],[RELEASE_CODE],[EXT_DT],[ACD],[DIV],[DIVISION],[DMOM]
> from [dbo].[ORDB] where permissions(1444512525) & 1 = 1 and (DIV = 'CCE')
>
> The ORDB table def:
> CREATE TABLE [ORDB] (
> [ORDER] [int] NOT NULL ,
> [SEQ] [smallint] NOT NULL ,
> [BUCKET_NO] [tinyint] NOT NULL ,
> [ORIG_PROM_DATE] [smalldatetime] NULL ,
> [PROM_DATE] [smalldatetime] NULL ,
> [CUST_REQ_DATE] [smalldatetime] NULL ,
> [SCHED_QTY] [int] NULL ,
> [TIMES_CHANGED] [int] NULL ,
> [RELEASE_CODE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT_DT] [smalldatetime] NULL ,
> [ACD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DIV] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DIVISION] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DMOM] AS (str([ORDER],6) + replace(str([SEQ],2),' ','0') +
> replace(str([BUCKET_NO],2),' ','0')) ,
> CONSTRAINT [PK_ORDB] PRIMARY KEY CLUSTERED
> (
> [ORDER],
> [SEQ],
> [BUCKET_NO],
> [DIV]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> > I suspect that the POF article has a horizontal filter defined on it which,
> > perhaps due to recent change in the data import process at the publisher, is
> > now performing a comparison between a float value and a character column or
> > vice versa. To investigate this further, call sp_helptext
> > '[dbo].[syncobj_0x3937443834433942]' at the publisher database to determine
> > the select statement used for the bcp operation and then keep removing
> > elements (where clauses, column conversions etc) from it until you narrow
> > down to the part of the select statement that is causing the conversion
> > error. If it is indeed a user-defined horizontal filter that is causing the
> > trouble, you can specify a new one through SEM or
> > sp_articlefilter/sp_articleview.
> >
> > HTH
> >
> > -Raymond
> >
> > "dwaine" wrote:
> >
> > > We're using snapshot repl to move tables/data to divisions. Recently, the
> > > method of populating the tables on the distributer/publisher changed from
> > > TextFile/DTS to DB2/DTS, not very exciting. Now during shapshot build for
> > > ONE division (out of 4) we get the following error:
> > >
> > > Bulk copying snapshot data for article 'POF'
> > > select * from [dbo].[syncobj_0x3937443834433942] where 1 = 2
> > > [5/17/2005 7:50:57 AM]AAG0J6DW04.ODS: select * from
> > > [dbo].[syncobj_0x3937443834433942] where 1 = 2
> > >
> > > *** [Publication:'CCE'] Total snapshot generation time excluding publication
> > > setup: 666140 (ms) ***
> > >
> > > SourceTypeId = 5
> > > SourceName = AAG0J6DW04
> > > ErrorCode = 8114
> > > ErrorText = Error converting data type varchar to float.
> > > The process could not bulk copy out of table
> > > '[dbo].[syncobj_0x3937443834433942]'.
> > >
> > >
> > > POF is the table here, but it started as a different table. When I removed
> > > that table from the publication it's now erroring on a subsequent table.
> > >
> > > What could be causing such an error on the data of multiple tables who's
> > > definition hasn't changed? Bottom line is the data is ALREADY LOADED into
> > > the tables schema with no errors! This is the snapshot agent loading the
> > > data into a bcp file for packaging, and the target schema is WRONG??!!
> > >
> > > I've removed and re-added tables from the publication and this doesn't fix
> > > it. I'll probably drop and re-create the entire publication and cross my
> > > fingers, but I'd like to know what is happening at this stage of the snapshot
> > > process that requires data definition?
.
- References:
- multiple repeatable "could not bulk copy out of table" errors
- From: dwaine
- RE: multiple repeatable "could not bulk copy out of table" errors
- From: Raymond Mak [MSFT]
- RE: multiple repeatable "could not bulk copy out of table" errors
- From: dwaine
- multiple repeatable "could not bulk copy out of table" errors
- Prev by Date: Re: updatable subscriptions and conflicts
- Next by Date: whats the purpose of sp_MSrepl_startup
- Previous by thread: RE: multiple repeatable "could not bulk copy out of table" errors
- Next by thread: Still problems, re-sync data from remote distributor
- Index(es):
Relevant Pages
|
