Invalid column name
-
When I try to execute updates or selects against a column called Failed_Login_Count I get error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Failed_Login_Count'.
I'm able to see this column in the object browser.
Why does this happen?
-
Can you post the DDL for the table and the queries you are having problems with?
-
i'm trying to execute:
update
users
set
password = 'myPassword',
active_flag
= 'Y',
failed_login_count
=0,
pwd_changed
='N'
where
user_name in ('myUserName')
Here's the DDL for the table:
USE
[My_Database]
GO
/****** Object: Table [dbo].[Users] Script Date: 08/20/2007 10:47:13 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[Users](
[User_Id] [int]
IDENTITY(1,1) NOT NULL,
[User_Name] [varchar]
(20) NOT NULL,
[Create_Date] [datetime]
NOT NULL CONSTRAINT [DF_Users_Create_Date] DEFAULT (getdate()),
[Created_By] [int]
NOT NULL,
[Modified_By] [int]
NULL,
[Last_Modified] [datetime]
NULL,
[Email_Address] [varchar]
(50) NULL,
[Password] [varchar]
(50) NULL,
[Email_Note_Flag] [char]
(1) NOT NULL CONSTRAINT [DF_Users_Email_Note_Flag] DEFAULT ('N'),
[Email_Note_Date] [datetime]
NULL,
[Full_Name] [varchar]
(50) NULL CONSTRAINT [DF_Users_Full_Name] DEFAULT ('Unknown Name'),
[Pwd_Changed] [char]
(1) NOT NULL CONSTRAINT [DF_Users_Pwd_hanged] DEFAULT ('Y'),
[Agreement_Flag] [char]
(1) NOT NULL CONSTRAINT [DF_Users_Agreement_Flag] DEFAULT ('N'),
[Agreement_Date] [datetime]
NULL,
[Active_Flag] [char]
(1) NOT NULL CONSTRAINT [DF_Users_Active_Flag] DEFAULT ('Y'),
[Last_Login] [datetime]
NULL,
[Physician_Id] [int]
NULL,
[Location] [varchar]
(2) NULL,
[Last_Password_Change] [datetime]
NULL,
[Role_Id] [int]
NOT NULL CONSTRAINT [DF_Users_Role_Id] DEFAULT (3),
[Note_Email_Address] [varchar]
(500) NULL,
[Parent_User_Id] [int]
NULL,
[Last_Failed_Login] [datetime]
NULL,
[Failed_Login_Count] [tinyint]
NOT NULL CONSTRAINT [DF_Users_Failed_Login_Count] DEFAULT (0),
CONSTRAINT [PK__Users__3493CFA7] PRIMARY KEY CLUSTERED
(
[User_Id]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [FK_Users_Roles] FOREIGN KEY([Role_Id])
REFERENCES
[dbo].[Roles] ([Role_Id])
GO
ALTER
TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Roles]
GO
ALTER
TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [FK_Users_Users] FOREIGN KEY([Parent_User_Id])
REFERENCES
[dbo].[Users] ([User_Id])
GO
ALTER
TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Users]
-
This runs perfect on my pc.
Do you have a trigger on that table that could throw this error?
-
there's a trigger called tu_users on the table but i don't know if it's responsible for the error.
Here's the trigger code:
USE
[My_Database]
GO
/****** Object: Trigger [dbo].[tu_users] Script Date: 08/20/2007 11:04:15 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
trigger [dbo].[tu_users] on [dbo].[Users] for update as
/* ERwin Builtin Thu Aug 28 10:43:07 2003 */
/* DELETE trigger on Institutions */
begin
declare @errno int,
@errmsg varchar
(255),
@agreement_date
datetime,
@email_note
datetime,
@pwd_changed_date
datetime,
@user_id
int
select @agreement_date=case u.Agreement_Flag
when 'Y' then case isnull(u.Agreement_Flag,'xyz105')
when isnull(d.Agreement_Flag,'xyz105') then u.Agreement_Date
else getdate()
end
else u.Agreement_Date
end,
@email_note
=case isnull(u.Email_Note_Flag,'xyz105')
when isnull(d.Email_Note_Flag,'xyz105') then u.Email_Note_Date
else getdate()
end,
@pwd_changed_date
=case isnull(u.Password,'xyz105')
when isnull(d.Password,'xyz105') then u.Last_Password_Change
else getdate()
end,
@user_id
=u.user_id
from inserted u,
deleted d
update users
set Last_Modified=getdate(),
Last_Password_Change
=@pwd_changed_date,
Email_Note_Date
=@email_note,
Agreement_Date
=@agreement_date
where [user_id]=@user_id
end
-
I can't really test this without some sample data on all tables. As far as my eyes can see, the trigger is not the problem... unless that trigger triggers another trigger.
Can you post the exact query you are running (if different from the one provided)?
Is there any other trigger on that table?
Can you post the full error message?
-
There are no other triggers on the table. The query I'm trying to run is what I posted just with the user name and password changed. The query is not the issue. I get the same error if I right a select that references that column. If I do a select * that column does not show up. It's something with that column.
-
Have you run DBCC checkdb?
Any other db integrity checks?
-
I've resolved this by manually editing the table, thanks for your time guys.
-
What was the problem?
-
the problem was i couldn't run sql statements that explicitly referenced that column.
-
What was causing the problem?
What did you change in the table definition?
-
I did not change anything. I did not solve the problem. I got around it by opening the table and editing it manually instead of executing sql against it.
-
Were you executing it on the correct server, on the correct db?
-
Yeah.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply