不是常见的“无法绑定多部分标识符”错误

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

#0

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 How do I create a stored procedure that calls sp_refreshview for each view in the database?

#1

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

#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 

推荐文章

在ActiveRecord的新方法Rails中传递和使用hash对象

在ActiveRecord的新方法Rails中传递和使用hash对象

推荐文章

使用命令行参数运行PostgreSQL.sql文件

使用命令行参数运行PostgreSQL.sql文件

推荐文章

带有numberditor的Java Swing JSpinner显示空白而不是零

带有numberditor的Java Swing JSpinner显示空白而不是零

推荐文章

在文件中搜索的非典型正则表达式

在文件中搜索的非典型正则表达式

推荐文章

在android中使用SharedPreferences更改油漆颜色

在android中使用SharedPreferences更改油漆颜色

推荐文章

NSOperation正在运行,但NSOperationQueue中缺少

NSOperation正在运行,但NSOperationQueue中缺少

推荐文章

控制台应用启动时不显示等待光标

控制台应用启动时不显示等待光标

推荐文章

以html/javascript获取绝对文件路径

以html/javascript获取绝对文件路径

推荐文章

使用Eclipse设置playframework应用程序的问题

使用Eclipse设置playframework应用程序的问题

推荐文章

在macosx应用cocoa中生成图像文件的问题

在macosx应用cocoa中生成图像文件的问题

推荐文章

使用只接受UTF的API上传到DropBOX

使用只接受UTF的API上传到DropBOX

推荐文章

如何在mssql中创建动态表?

如何在mssql中创建动态表?

推荐文章

如何使用mysql检查where子句中的撇号

如何使用mysql检查where子句中的撇号

推荐文章

有没有工具可以重新命名Java库及其引用?

有没有工具可以重新命名Java库及其引用?

推荐文章

使用jQuery排序表

使用jQuery排序表

推荐文章

在javascript弹出窗口中显示图像标题?

在javascript弹出窗口中显示图像标题?