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.
If I remove only the DISTINCT the query runs fine, add the DISTINCT and I get strange errors.
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
