Invalid Column Name - SQL Server Forums

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

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2012-02-17 : 12:45:23

[code]
SELECT am.AsmtYear AS tax_year,
am.AcctNmbr AS id,
( cast( right( Twnshp + 100, 2 ) as varchar(2) ) + TwnshpDir +
rtrim( Range ) + RangDir +
cast( right( Sctn + 100, 2 ) as varchar(2) ) + right( QtrSctn + 100, 2 ) + '-' +
cast( right( Prcl + 100000, 5 ) as varchar(5) ) ) AS map_lot,
MntnAreaCode AS area,
PropClassCode AS pclas,
OwnerName AS owner,
AcctAcresMkt AS acres,
Rmv AS rmv ,
Sav AS sav ,
MaxSpecAssessedValue AS msav ,
TaxableAssessedValue AS tav
FROM
AwbreyWheelerAsrdta..AB_AccountMaster am JOIN
AwbreyWheelerAsrdta..AB_AccountSummary sm ON
am.AcctNmbr = sm.AcctNmbr
and am.AsmtYear = sm.AsmtYear
where am.AcctNmbr = 2813 and am.AsmtYear between 2002 and 2006
UNION
SELECT distinct
pvt.tax_year ,
pvt.id,
left( p.tax_bill_id, 16 ) as map_lot,
p.routing_number as area,
property_class as pclas,
p.owner1 as owner,
p.legal_acreage as acres,
( MKLND + SAMKL + MKIMP ) AS rmv ,
( MKLND + M5SAV + MKIMP ) AS sav ,
( SAVL + BMAV + EAV ) AS msav,
TVR AS tav
FROM
ProvalWheelerCommon..parcel_base p JOIN
ManatronWheelerCustom..udtValComponentPivot pvt ON
p.lrsn = pvt.id
where p.parcel_id = 2813 and pvt.tax_year >= 2007
ORDER BY 1 desc;
[/code]

I created this script a couple years ago and has worked fine until of late. I am getting

quote:

Msg 207, Level 16, State 1, Line 37
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'id'.

I can assure you [id] is indeed a valid column name.

quote:

SELECT [id]
,[parcel_id]
,[tax_year]
,[AVO]
,[AVR]
,[BMAV]
,[BMKT]
,[BSAMK]
,[EAR]
,[EAV]
,[EAVO]
,[EBV]
,[M5SAV]
,[MAVIM]
,[MAVLN]
,[MAVMK]
,[MAVMO]
,[MKAV]
,[MKAVO]
,[MKIMP]
,[MKITL]
,[MKLND]
,[MKLTL]
,[MKTTL]
,[MSAV]
,[NCEV]
,[NCYRO]
,[NCYRS]
,[NEWCN]
,[OEV]
,[PNCEO]
,[PNCEV]
,[PNCO]
,[PNCOO]
,[PPROP]
,[SAMKI]
,[SAMKL]
,[SAV]
,[SAVI]
,[SAVL]
,[TVR]
FROM [ManatronWheelerCustom].[dbo].[udtValComponentPivot]

As can be seen here.

So, what is really going on here?

TIA

Education is what you have after you've forgotten everything you learned in school

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 13:03:14

Post he DDL of the table ManatronWheelerCustom..udtValComponentPivot

Or do an sp_help

my guess is that the column is history

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

doco
Yak Posting Veteran

77 Posts

Posted - 2012-02-17 : 13:09:07

[code]
USE [ManatronWheelerCustom]
GO
/****** Object: Table [dbo].[udtValComponentPivot] Script Date: 02/17/2012 10:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[udtValComponentPivot](
[id] [int] NOT NULL,
[parcel_id] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[tax_year] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AVO] [decimal](38, 0) NULL,
[AVR] [decimal](38, 0) NULL,
[BMAV] [decimal](38, 0) NULL,
[BMKT] [decimal](38, 0) NULL,
[BSAMK] [decimal](38, 0) NULL,
[EAR] [decimal](38, 0) NULL,
[EAV] [decimal](38, 0) NULL,
[EAVO] [decimal](38, 0) NULL,
[EBV] [decimal](38, 0) NULL,
[M5SAV] [decimal](38, 0) NULL,
[MAVIM] [decimal](38, 0) NULL,
[MAVLN] [decimal](38, 0) NULL,
[MAVMK] [decimal](38, 0) NULL,
[MAVMO] [decimal](38, 0) NULL,
[MKAV] [decimal](38, 0) NULL,
[MKAVO] [decimal](38, 0) NULL,
[MKIMP] [decimal](38, 0) NULL,
[MKITL] [decimal](38, 0) NULL,
[MKLND] [decimal](38, 0) NULL,
[MKLTL] [decimal](38, 0) NULL,
[MKTTL] [decimal](38, 0) NULL,
[MSAV] [decimal](38, 0) NULL,
[NCEV] [decimal](38, 0) NULL,
[NCYRO] [decimal](38, 0) NULL,
[NCYRS] [decimal](38, 0) NULL,
[NEWCN] [decimal](38, 0) NULL,
[OEV] [decimal](38, 0) NULL,
[PNCEO] [decimal](38, 0) NULL,
[PNCEV] [decimal](38, 0) NULL,
[PNCO] [decimal](38, 0) NULL,
[PNCOO] [decimal](38, 0) NULL,
[PPROP] [decimal](38, 0) NULL,
[SAMKI] [decimal](38, 0) NULL,
[SAMKL] [decimal](38, 0) NULL,
[SAV] [decimal](38, 0) NULL,
[SAVI] [decimal](38, 0) NULL,
[SAVL] [decimal](38, 0) NULL,
[TVR] [decimal](38, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
[/code]

BTW. All fields in that table come back as invalid column name...

Education is what you have after you've forgotten everything you learned in school

doco
Yak Posting Veteran

77 Posts

Posted - 2012-02-17 : 13:30:15

I reran the SP that created the table and all is well now. Nothing changed in the table structure - just recreated it.

Bizarre

Education is what you have after you've forgotten everything you learned in school

doco
Yak Posting Veteran

77 Posts

Posted - 2012-02-17 : 13:30:15

I reran the SP that created the table and all is well now. Nothing changed in the table structure - just recreated it.

Bizarre

Education is what you have after you've forgotten everything you learned in school

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 14:48:35

Alrighty then..."It's Miller time"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources