SQL Server Statement Error

• Eric Brochu

  Grasshopper

  Points: 12

  More actions

  October 18, 2005 at 8:41 am

  #89486

  Hi,

   

  I might be too tired to figure this out but I’m currently facing a problem with SQL Server 2005 September CTP.

   

  Using the following statement:

   

     UPDATE DW.dbo.DIMPromotion

        SET DescriptionPromotion = b.DescriptionPromotion

           ,DescriptionCirculaire = b.DescriptionCirculaire

           ,DescriptionCarreau = b.DescriptionCarreau

           ,CodeImpact = b.CodeImpact

           ,DescriptionImpact = b.DescriptionImpact

           ,CodePromotionLiee = b.CodePromotionLiee

           ,StatutPromotion = b.StatutPromotion

           ,DateDebutPromotion = b.DateDebutPromotion

           ,DateFinPromotion = b.DateFinPromotion

           ,DateDebutMagasin = b.DateDebutMagasin

           ,DateFinMagasin = b.DateFinMagasin

           ,MontantFixe = b.MontantFixe

           ,VolumeProjete = b.VolumeProjete

       FROM DW.dbo.DIMPromotion a

       JOIN #DimPromotion b ON b.CodePromotion = a.CodePromotion

                           AND b.CodeCirculaire = a.CodeCirculaire

                           AND b.CodeCarreau = a.CodeCarreau

                           AND b.NumeroContrat = a.NumeroContrat

                           AND

                          (

                              b.DescriptionPromotion <> a.DescriptionPromotion

                           OR b.DescriptionCirculaire <>            a.DescriptionCirculaire

                           OR b.DescriptionCarreau <> a.DescriptionCarreau

                           OR b.CodeImpact <> a.CodeImpact

                           OR b.DescriptionImpact <> a.DescriptionImpact

                           OR b.CodePromotionLiee <> a.CodePromotionLiee

                           OR b.StatutPromotion <> a.StatutPromotion

                           OR b.DateDebutPromotion <> a.DateDebutPromotion

                           OR b.DateFinPromotion <> a.DateFinPromotion

                           OR b.DateDebutMagasin <> a.DateDebutMagasin

                           OR b.DateFinMagasin <> a.DateFinMagasin

                           OR b.MontantFixe <> a.MontantFixe

                           OR b.VolumeProjete <> a.VolumeProjete

                           )

   

  I Receive the following error message:

  Msg 8114, Level 16, State 5, Line 1

  Error converting data type nvarchar to numeric.

   

  The weird part is if a comment a few lines (never the same) the statement works properly without generating en error message…

   

  The following statement are working properly :

   

     UPDATE DW.dbo.DIMPromotion

        SET DescriptionPromotion = b.DescriptionPromotion

           ,DescriptionCirculaire = b.DescriptionCirculaire

           ,DescriptionCarreau = b.DescriptionCarreau

           ,CodeImpact = b.CodeImpact

           ,DescriptionImpact = b.DescriptionImpact

           ,CodePromotionLiee = b.CodePromotionLiee

           ,StatutPromotion = b.StatutPromotion

           ,DateDebutPromotion = b.DateDebutPromotion

           ,DateFinPromotion = b.DateFinPromotion

           ,DateDebutMagasin = b.DateDebutMagasin

           ,DateFinMagasin = b.DateFinMagasin

           ,MontantFixe = b.MontantFixe

           ,VolumeProjete = b.VolumeProjete

       FROM DW.dbo.DIMPromotion a

       JOIN #DimPromotion b ON b.CodePromotion = a.CodePromotion

                           AND b.CodeCirculaire = a.CodeCirculaire

                           AND b.CodeCarreau = a.CodeCarreau

  --                         AND b.NumeroContrat = a.NumeroContrat

                           AND

                          (

                              b.DescriptionPromotion <> a.DescriptionPromotion

  --                         OR b.DescriptionCirculaire <>            a.DescriptionCirculaire

  --                         OR b.DescriptionCarreau <> a.DescriptionCarreau

  --                         OR b.CodeImpact <> a.CodeImpact

  --                         OR b.DescriptionImpact <> a.DescriptionImpact

  --                         OR b.CodePromotionLiee <> a.CodePromotionLiee

  --                         OR b.StatutPromotion <> a.StatutPromotion

                           OR b.DateDebutPromotion <> a.DateDebutPromotion

                           OR b.DateFinPromotion <> a.DateFinPromotion

                           OR b.DateDebutMagasin <> a.DateDebutMagasin

                           OR b.DateFinMagasin <> a.DateFinMagasin

                           OR b.MontantFixe <> a.MontantFixe

                           OR b.VolumeProjete <> a.VolumeProjete

                           )

   

   

     UPDATE DW.dbo.DIMPromotion

        SET DescriptionPromotion = b.DescriptionPromotion

           ,DescriptionCirculaire = b.DescriptionCirculaire

           ,DescriptionCarreau = b.DescriptionCarreau

           ,CodeImpact = b.CodeImpact

           ,DescriptionImpact = b.DescriptionImpact

           ,CodePromotionLiee = b.CodePromotionLiee

           ,StatutPromotion = b.StatutPromotion

           ,DateDebutPromotion = b.DateDebutPromotion

           ,DateFinPromotion = b.DateFinPromotion

           ,DateDebutMagasin = b.DateDebutMagasin

           ,DateFinMagasin = b.DateFinMagasin

           ,MontantFixe = b.MontantFixe

           ,VolumeProjete = b.VolumeProjete

       FROM DW.dbo.DIMPromotion a

       JOIN #DimPromotion b ON b.CodePromotion = a.CodePromotion

  --                         AND b.CodeCirculaire = a.CodeCirculaire

                           AND b.CodeCarreau = a.CodeCarreau

                           AND b.NumeroContrat = a.NumeroContrat

                           AND

                          (

                              b.DescriptionPromotion <> a.DescriptionPromotion

                           OR b.DescriptionCirculaire <>            a.DescriptionCirculaire

                           OR b.DescriptionCarreau <> a.DescriptionCarreau

                           OR b.CodeImpact <> a.CodeImpact

                           OR b.DescriptionImpact <> a.DescriptionImpact

                           OR b.CodePromotionLiee <> a.CodePromotionLiee

                           OR b.StatutPromotion <> a.StatutPromotion

  --                         OR b.DateDebutPromotion <> a.DateDebutPromotion

  --                         OR b.DateFinPromotion <> a.DateFinPromotion

  --                         OR b.DateDebutMagasin <> a.DateDebutMagasin

  --                         OR b.DateFinMagasin <> a.DateFinMagasin

  --                         OR b.MontantFixe <> a.MontantFixe

  --                         OR b.VolumeProjete <> a.VolumeProjete

                           )

   

  This ensure me that there is no conversion error in the statement itself… I’m I missing something here ? Is there a maximum number of charater we can put in a statement (or a join clause)?

   

  Thanks for your help,

  Eric Brochu

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 18, 2005 at 9:00 am

  #597953

  Eric,

  Are you actually executing the statements with the commented part or are they just compiled in a stored proc?

  also can you post the DDL of  "DW.dbo.DIMPromotion" and "#DimPromotion" ?

   

  
  * Noel

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  October 18, 2005 at 9:03 am

  #597954

  Impossible to help without the DDL and datatypes of the columns involved.

  When you comment parts of a join, the resultset is different, therefore rows that are triggering the type conversion error in statement #1 may be absent from the resultset in statements #2 and #3.

• Eric Brochu

  Grasshopper

  Points: 12

  More actions

  October 18, 2005 at 9:24 am

  #597969

  OK,

  My bad ...I learned today not to trust what was developed and/or the analyzed prior to your involvement in a project. 

  #DimPromotion 1 CodeCirculaire varchar

  #DimPromotion 2 DescriptionCirculaire varchar

  #DimPromotion 3 CodePromotion varchar

  #DimPromotion 4 DescriptionPromotion varchar

  #DimPromotion 5 CodePromotionLiee varchar

  #DimPromotion 6 StatutPromotion varchar

  #DimPromotion 7 DateDebutPromotion varchar

  #DimPromotion 8 DateFinPromotion varchar

  #DimPromotion 9 DateDebutMagasin varchar

  #DimPromotion 10 DateFinMagasin varchar

  #DimPromotion 11 CodeCarreau numeric

  #DimPromotion 12 DescriptionCarreau varchar

  #DimPromotion 13 NumeroContrat numeric

  #DimPromotion 14 DateCreationCarreau varchar

  #DimPromotion 15 CodeImpact varchar

  #DimPromotion 16 DescriptionImpact varchar

  #DimPromotion 17 MontantFixe int

  #DimPromotion 18 VolumeProjete int

   

  CREATE

  TABLE [dbo].[DIMPromotion](

  [IdPromotion] [int]

  IDENTITY(1,1) NOT NULL,

  [CodePromotion] [nvarchar]

  (5) NOT NULL,

  [DescriptionPromotion] [nvarchar]

  (30)  NULL,

  [CodeCirculaire] [nvarchar]

  (2)  NOT NULL,

  [DescriptionCirculaire] [nvarchar]

  (20)  NULL,

  [CodeCarreau] [nvarchar](3)

  NOT NULL,

  [DescriptionCarreau] [nvarchar]

  (30)  NULL,

  [DateCreationCarreau] [datetime]

  NOT NULL,

  [CodeImpact] [nchar]

  (1)  NULL,

  [DescriptionImpact] [nvarchar]

  (15)  NULL,

  [NumeroContrat] [nvarchar](4) 

  NOT NULL,

  [CodePromotionLiee] [nvarchar]

  (5)  NULL,

  [StatutPromotion] [nchar]

  (1)  NOT NULL,

  [DateDebutPromotion] [datetime]

  NOT NULL,

  [DateFinPromotion] [datetime]

  NULL,

  [DateDebutMagasin] [datetime]

  NOT NULL,

  [DateFinMagasin] [datetime]

  NULL,

  [MontantFixe] [decimal]

  (18, 2) NULL,

  [VolumeProjete] [int]

  NULL,

  [DateInsertion] [datetime]

  NOT NULL

  ) ON [PRIMARY]

  Thanks,

  Eric

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  October 18, 2005 at 11:57 am

  #598023

  Try this:

  SELECT CodeCarreau

  FROM DimPromotion

  WHERE ISNUMERIC(CodeCarreau) = 0

  Then try the same thing with NumeroContrat.

  Anything that is returned will be values that can not be converted.

  -SQLBill