conversion Failed when converting the varchar to data type int but nothing is defined as int

Question

I am doing a Union all over 2 different db's when running it I get this error:

Msg 245, Level 16, State 1, Line 16 Conversion failed when converting the varchar value '12037.5' to data type int.

But none of my columns are of type integer? For some reason it changes the Main select type to integer.

DECLARE @SAGESTARTDATE AS DATETIME,
        @SAGEENDDATE AS DATETIME,
        @SAGEITEM AS NVARCHAR(50),
        @SPSSTARTDATE AS DATETIME,
        @SPSENDDATE AS DATETIME,
        @SPSCOUNTRY AS NVARCHAR(50)

set     @sagestartdate = '2017-01-15'
set     @sageenddate = '2017-01-15'
set     @sageitem = 'diesel 50ppm'
set     @spsstartdate = '2017-01-15'
set     @spsenddate = '2017-01-15'
set     @spscountry = 'dRC'


Select FleetNo, TxDate, Max(STCode) as StCode, SageRev, SageQty, Max(SagePrj) as SagePrj, 
        MAx(SPSType) as SPSType, MAx(SPSModel) as SPSModel, MAX(SPSSite) as SPSSite, Max(SPSCountry) as SPSCountry, 
        SPSFuel
From (
        Select Strans.[Description] COLLATE SQL_Latin1_General_CP1_CI_AS as FleetNo 
                , Strans.TxDate as TxDate
                , StMstr.Code as STCode
                , Strans.[Reference] as SageRev     
                , Strans.Quantity as SageQty        
                , STrans.ProjectCode as SagePrj     
                , 0 AS SPSTYPE      
                , 0 AS SPSMODEL
                , 0 AS SPSSITE
                , 0 AS SPSCOUNTRY       
                , 0 as SPSFuel              

        from [KCS SARL].DBO._bvSTTransactionsFull STrans join
            [KCS SARL].DBO._bvStockFull StMstr on STrans.AccountLink = StMstr.StockLink

        Where Code = @SAGEITEM and Strans.TxDate BETWEEN @SAGESTARTDATE AND @SAGEENDDATE 

        UNION ALL

            Select  SPSTr.FleetNo as FleetNo
                    , SPSTR.ProdDate AS TxDate
                    , 0 as STCode
                    , 0 as SageRev
                    , 0 as SageQty
                    , 0 AS SagePrj      
                    , SPSTR.EqType AS SPSTYPE       
                    , SPSTR.ModelName AS SPSMODEL       
                    , SPSTR.SiteName AS SPSSITE
                    , SPSTR.Country AS SPSCOUNTRY       
                    , SPStr.Fuel as SPSFuel

        From [SPS].dbo.DataInputTotal SPStr
        Where SPStr.WCode = 102 and SPSTR.ProdDate BETWEEN @SPSSTARTDATE and @SPSENDDATE 
                AND SPSTR.Country = @SPSCOUNTRY
    ) as Fuel

    Group By FleetNo, TxDate, SageRev, SageQty, SPSFuel

    Order by FleetNo 

Answer 1

Strans.[Description] COLLATE SQL_Latin1_General_CP1_CI_AS as FleetNo is varchar which you are trying to union with SPSTr.FleetNo as FleetNo which may be int,this is the reasons for your error convert it to varchar convert(SPSTr.FleetNo as varchar) as FleetNo

Answer 2

SPStr.WCode is probably a varchar, and 102 is an int. Try wrapping 102 in single quotes.

Also, if any of these types are not integers, the union would be trying to cast them as integers based on this part of your union:

, 0 AS SPSTYPE
, 0 AS SPSMODEL
, 0 AS SPSSITE
, 0 AS SPSCOUNTRY
, 0 as SPSFuel

, 0 as STCode
, 0 as SageRev
, 0 as SageQty
, 0 AS SagePrj  

DECLARE @SAGESTARTDATE AS DATETIME,
        @SAGEENDDATE AS DATETIME,
        @SAGEITEM AS NVARCHAR(50),
        @SPSSTARTDATE AS DATETIME,
        @SPSENDDATE AS DATETIME,
        @SPSCOUNTRY AS NVARCHAR(50)

set     @sagestartdate = '2017-01-15'
set     @sageenddate = '2017-01-15'
set     @sageitem = 'diesel 50ppm'
set     @spsstartdate = '2017-01-15'
set     @spsenddate = '2017-01-15'
set     @spscountry = 'dRC'


Select FleetNo, TxDate, Max(STCode) as StCode, SageRev, SageQty, Max(SagePrj) as SagePrj, 
        MAx(SPSType) as SPSType, MAx(SPSModel) as SPSModel, MAX(SPSSite) as SPSSite, Max(SPSCountry) as SPSCountry, 
        SPSFuel
From (
        Select Strans.[Description] COLLATE SQL_Latin1_General_CP1_CI_AS as FleetNo 
                , Strans.TxDate as TxDate
                , StMstr.Code as STCode
                , Strans.[Reference] as SageRev     
                , Strans.Quantity as SageQty        
                , STrans.ProjectCode as SagePrj     
                , '' AS SPSTYPE      
                , '' AS SPSMODEL
                , '' AS SPSSITE
                , '' AS SPSCOUNTRY       
                , '' as SPSFuel              

        from [KCS SARL].DBO._bvSTTransactionsFull STrans join
            [KCS SARL].DBO._bvStockFull StMstr on STrans.AccountLink = StMstr.StockLink

        Where Code = @SAGEITEM and Strans.TxDate BETWEEN @SAGESTARTDATE AND @SAGEENDDATE 

        UNION ALL

            Select  SPSTr.FleetNo as FleetNo
                    , SPSTR.ProdDate AS TxDate
                    , 0 as STCode
                    , 0 as SageRev
                    , 0 as SageQty
                    , 0 AS SagePrj      
                    , SPSTR.EqType AS SPSTYPE       
                    , SPSTR.ModelName AS SPSMODEL       
                    , SPSTR.SiteName AS SPSSITE
                    , SPSTR.Country AS SPSCOUNTRY       
                    , SPStr.Fuel as SPSFuel

        From [SPS].dbo.DataInputTotal SPStr
        Where SPStr.WCode = '102' and SPSTR.ProdDate BETWEEN @SPSSTARTDATE and @SPSENDDATE 
                AND SPSTR.Country = @SPSCOUNTRY
    ) as Fuel

    Group By FleetNo, TxDate, SageRev, SageQty, SPSFuel

    Order by FleetNo