Not your usual "The multi-part identifier could not be bound" error

Question

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

Answer 1

Are any of these Views? I seem to remember getting weird errors like that after changing view definitions and not running sp_refreshview. I can't see the text "Tbl1021" anywhere so I'm assuming this is likely to be in a View definition?

If so there is a script here to refresh all views http://stackoverflow.com/questions/2491298/how-do-i-create-a-stored-procedure-that-calls-sp-refreshview-for-each-view-in-the

Answer 2

Eugene Without running the code on my Development system, the first thing I would resolve is the inconsistent use of alias in the code You should as good coding practice always where possible identify the object {owner} I.E by using in this example prefix each object with the relevant alias A, B, C etc..

I.E for example ClientName 'Parent' make this A.ClientName 'Parent' if this columns was in table aliased as A

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

Answer 3

Ok I still don't know what caused this or what the correct answer is but here is what I ended up doing to get it fixed.

1. I created a duplicate of the offending table and populated it with a copy of the data.
2. I created all the same keys, indexes etc etc
3. Tested the query and guess what it worked.

So I made a backup of the data in the table dropped the offending table recreated it with all the keys, indexes etc etc and now order is restored.

All the queries that used to fail now works perfectly. Very strange