I have the following query, now the strange thing is if I run this query on my development and pre-prod server it runs fine. If I run it on production it fails.

I have figured out that if I run just the Select statement its happy but as soon as I try insert into the table variable it complains.

DECLARE @RESULTS TABLE
    (
     [Parent] VARCHAR(255)
    ,[client] VARCHAR(255)
    ,[ComponentName] VARCHAR(255)
    ,[DealName] VARCHAR(255)
    ,[Purchase Date] DATETIME
    ,[Start Date] DATETIME
    ,[End Date] DATETIME
    ,[Value] INT
    ,[Currency] VARCHAR(255)
    ,[Brand] VARCHAR(255)
    ,[Business Unit] VARCHAR(255)
    ,[Region] VARCHAR(255)
    ,[DealID] INT
    )

INSERT  INTO @RESULTS
SELECT DISTINCT
    ClientName 'Parent'
   ,F.ClientID 'client'
   ,ComponentName
   ,A.DealName
   ,CONVERT(SMALLDATETIME , ISNULL(PurchaseDate , '1900-01-01')) 'Purchase Date'
   ,CONVERT(SMALLDATETIME , ISNULL(StartDate , '1900-01-01')) 'Start Date'
   ,CONVERT(SMALLDATETIME , ISNULL(EndDate , '1900-01-01')) 'End Date'
   ,DealValue 'Value'
   ,D.Currency 'Currency'
   ,ShortBrand 'Brand'
   ,G.BU 'Business Unit'
   ,C.DMRegion 'Region'
   ,DealID
FROM
    LTCDB_admin_tbl_Deals A
    INNER JOIN dbo_DM_Brand B
    ON A.BrandID = B.ID
    INNER JOIN LTCDB_admin_tbl_DM_Region C
    ON A.Region = C.ID
    INNER JOIN LTCDB_admin_tbl_Currency D
    ON A.Currency = D.ID
    INNER JOIN LTCDB_admin_tbl_Deal_Clients E
    ON A.DealID = E.Deal_ID
    INNER JOIN LTCDB_admin_tbl_Clients F
    ON E.Client_ID = F.ClientID
    INNER JOIN LTCDB_admin_tbl_DM_BU G
    ON G.ID = A.BU
    INNER JOIN LTCDB_admin_tbl_Deal_Components H
    ON A.DealID = H.Deal_ID
    INNER JOIN LTCDB_admin_tbl_Components I
    ON I.ComponentID = H.Component_ID
WHERE
    EndDate != '1899-12-30T00:00:00.000'
    AND StartDate < EndDate
    AND B.ID IN ( 1 , 2 , 5 , 6 , 7 , 8 , 10 , 12 )
    AND C.SalesRegionID IN ( 1 , 3 , 4 , 11 , 16 )
    AND A.BU IN ( 1 , 2 , 3 , 4 , 5 , 6 , 8 , 9 , 11 , 12 , 15 , 16 , 19 , 20 , 22 , 23 , 24 , 26 , 28 , 30 )
    AND ClientID = 16128

SELECT ... FROM @Results

I get the following error

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1021.ComponentName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1011.Currency" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2454'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2461'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2491'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2490'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2482'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2478'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2477'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col2475'.

EDIT - EDIT - EDIT - EDIT - EDIT - EDIT through a process of elimination I have found that following and wondered if anyone can shed some light on this.

1. If I remove only the DISTINCT the query runs fine, add the DISTINCT and I get strange errors.
2. Also I have found that if I comment the following lines then the query runs with the DISTINCT what is strange is that none of the columns in the table LTCDB_admin_tbl_Deal_Components is referenced so I don't see how the distinct will affect that.

INNER JOIN LTCDB_admin_tbl_Deal_Components H 
ON A.DealID = H.Deal_ID