Invalid column name 'Expr1010' in SQLQuery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid column name 'Expr1010' in SQLQuery

Author

Topic

willfindavid
Starting Member

27 Posts

Posted - 2010-03-20 : 06:48:40

[code]
SELECT WMP.[PlanName] 'AA'
FROM [CUSCONFIG].WorkMgmtPlan WMP (NOLOCK)
INNER JOIN [CUSCONFIG].WorkMgmtPlanLineItems WPL (NOLOCK)-- GET WORK REQUESTS
ON WMP.PlanID = WPL.WorkMgmtPlanID AND WMP.[IsActive] = 1 AND WPL.[IsActive] = 1 AND [Type] = 'R'
INNER JOIN [CUSCONFIG].pcworktypes PC (NOLOCK) -- GET WORK TYPES
ON WPL.[PCWorkTypeID] = PC.[PCWorkTypeID] and PC.[isactive] = 1
INNER JOIN [CUSCONFIG].PCProcessPackages pp (NOLOCK)
on PC.[pcprocesspackageid] = pp.[ProcessPackageId]
INNER JOIN [CUSDASHBOARD].MasLevelValues MLVA (NOLOCK) -- CHECK FOR PROJECTS
ON MLVA.[SourceSystemID] = CONVERT(varchar,WMP.ProliteProjectID)[/code]

When i execute this Query error displayed is,
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Expr1010'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col1051'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col1050'.

SHOWPLAN DATA:
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1010])=([MLVA].[SourceSystemID]), RESIDUAL:([c20cddev ].[CUSDASHBOARD].[MasLevelValues].[SourceSystemID] as [MLVA].[SourceSystemID]=CONVERT_IMPLICIT(nvarchar(30),CONVERT(varchar(30),[CTSINTBMVCTRTD1].[Cognizant20].[Dbo].[WorkMgmtPlan].[ProliteProjectID] as [WMP].[ProliteProjectID],0),0)))
|--Compute Scalar(DEFINE:([CTSINTBMVCTRTD1].[Cognizant20].[Dbo].[WorkMgmtPlan].[PlanName]=[CTSINTBMVCTRTD1].[Cognizant20].[Dbo].[WorkMgmtPlan].[PlanName] as [WMP].[PlanName], [CTSINTBMVCTRTD1].[Cognizant20].[Dbo].[WorkMgmtPlan].[ProliteProjectID]=[CTSINTBMVCTRTD1].[Cognizant20].[Dbo].[WorkMgmtPlan].[ProliteProjectID] as [WMP].[ProliteProjectID], [Expr1010]=[Expr1010]))
| |--Remote Query(SOURCE:(CTSINTBMVCTRTD1), QUERY:(SELECT "Col1050","Col1051","Expr1010" ORDER BY "Expr1010" ASC))
|--Sort(ORDER BY:([MLVA].[SourceSystemID] ASC))
|--Index Scan(OBJECT:([c20cddev ].[CUSDASHBOARD].[MasLevelValues].[IX_CUSDASHBOARD_MASLEVELVALUES] AS [MLVA]))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-20 : 07:10:20

is the correct query posted ? I can't find Expr1010 in that query.

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 07:17:03

I don't think it is related but NOLOCK is the work of the devil

Sorry if I sound harsh, but I've never met a DEV yet who knew what the consequences of NOLOCK's "dirty reads" are. You can get rows repeated twice in your query, or COMPLETELY MISSING. Is that OK for (all) your queries that use NOLOCK?

NOLOCK was a band-aid in SQL2000 (possibly earlier versions, I have forgotten). Our DBAs use it when we query very high transaction tables for diagnostic reasons only, never in any production code.

Anyway, enough of my wibble, MS fixed this in SQL 2005 by introducing

READ_COMMITTED_SNAPSHOT

which gives you all the benefits of NOLOCK - assuming you are using it to stop Reads from blocking / deadlocking writes - recommend you switch to using that and get rid of all NOLOCK statements

Couple of thoughts:


SELECT WMP.[PlanName] 'AA'

Change to


SELECT WMP.[PlanName] AS [AA]

and


ON MLVA.[SourceSystemID] = CONVERT(varchar,WMP.ProliteProjectID)

change to


ON MLVA.[SourceSystemID] = CONVERT(varchar(20),WMP.ProliteProjectID)

so you are not relying on the default size - I've chosen 20 on the basis that ProliteProjectID might be an INT, but better to use the actual width of SourceSystemID. Note that there is a "CONVERT_IMPLICIT(nvarchar(30), ..." in the Query Plan, so if SourceSystemID is Nvarchar, rather than Varchar, best to cast it to that directly

But I don't think any of that is related to the problem.

Is the query referencing VIEWs rather than tables? The Query Plan looks a bit "removed" from the query itself. If so would probably help to see what the VIEWs look like.

In particular whatever is generating this:


Remote Query(SOURCE:(CTSINTBMVCTRTD1), QUERY:(SELECT "Col1050","Col1051","Expr1010" ORDER BY "Expr1010" ASC))

which is I think the root of the error message

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 07:17:50

quote:
Originally posted by khtan

is the correct query posted ? I can't find Expr1010 in that query.


Remote Query(SOURCE:(CTSINTBMVCTRTD1), QUERY:(SELECT "Col1050","Col1051","Expr1010" ORDER BY "Expr1010" ASC))

in the Query Plan - from a VIEW I am reckoning?

willfindavid
Starting Member

27 Posts

Posted - 2010-03-20 : 07:25:18

I've tried removing all locks and changing the size of varchar to varchar(20) and also tried nvarchar.

But the same errors gets displayed.

The thing is im contacting tables from different server.
[CUSCONFIG] points to the server CTSINTBMVCTRTD1 and this is a synonymn.
[CUSDASHBOARD] is a schema name and the table is in local server.

The code marked in red is when the error occurs.
Without that it doesn't.

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 07:52:57

Long shot: Check you have latest Service Pack installed - just in case you have hit an already-fixed-bug

http://www.sqlteam.com/article/sql-server-versions

"[CUSCONFIG] points to the server CTSINTBMVCTRTD1 and this is a synonymn."

Any idea how SQL is generating the SELECT "Col1050","Col1051","Expr1010" ORDER BY "Expr1010" ASC) code? Doesn't seem to match any other columns in the query. Anything fishy with the synonymn definition

Does the query plan change (with respect to this code snippet) when you remove your code in red? That might just shed some light on the problem.

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 07:57:35

P.S.

Expr1010 is being used as an internal working label for ON MLVA.[SourceSystemID] = CONVERT(varchar,WMP.ProliteProjectID) (which is part of your red-code), and also for the remote query (seems to be part of the other two JOINs). I'm not good at reading query plans, but I wonder if there is some sort of name clash there?

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 07:59:04

P.P.S.

Any idea why I am not seeing any of the CUSCONFIG tablenames in the Query Plan?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-20 : 08:40:30

quote:
Originally posted by Kristen

quote:
Originally posted by khtan

is the correct query posted ? I can't find Expr1010 in that query.

Remote Query(SOURCE:(CTSINTBMVCTRTD1), QUERY:(SELECT "Col1050","Col1051","Expr1010" ORDER BY "Expr1010" ASC))

in the Query Plan - from a VIEW I am reckoning?

Strange .. it didn't appear when i press CTRL-F earlier

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources