invalid object name 'sys.identity_columns'

Question

Post reply

invalid object name 'sys.identity_columns'

shobha.aradhya

SSC Veteran

Points: 256
More actions
June 27, 2012 at 12:12 am

#262896

Hi,
I am using sql server 2005. I was checking for last_value of identity columns. When I try to execute the command
select * from sys.identity_columns
its showing error invalid object name 'sys.identity_columns'
Any help on this?
Thanks in Advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

It works fine with me.

Do you have the right permissions?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

John Mitchell-245523

SSC Guru

Points: 148809

June 27, 2012 at 3:51 am

#1506035

What is the compatibility level of your database?

John

shobha.aradhya SSC Veteran Points: 256 More actions · Answer 3

What permission should I have on the database?

Koen Verbeeck (6/27/2012)
It works fine with me.
Do you have the right permissions?

shobha.aradhya SSC Veteran Points: 256 More actions · Answer 4

Hi john,

The compatibility level shows 80.

John Mitchell-245523 (6/27/2012)
What is the compatibility level of your database?
John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The view you are trying to use is only available in SQL Server 2005 onwards. Your database is in SQL Server 2000 compatibility mode. Is there any reason to stay in that compatibility mode?

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Compat mode does not affect the visibility of the DMVs. All DMVs are viewable no matter what the compat mode is.

Now, if he's actually on SQL 200, that might. Please run SELECT @@Version and post the results

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shobha.aradhya SSC Veteran Points: 256 More actions · Answer 7

Hi John,

Thanks for your reply. I am accessing the client server which has this version and mode.

The reason why I wanted to view identity_columns is when I restored db the last_value for the table fields are different, i mean its retaining the old value instead of new one. ex : the max(id) of a field is 10230 from the restored db but its showing 986 which is throwing duplicate key value (I checked this by restoring to sql 2008)

Why is this so?

Regards,

Shobha

John Mitchell-245523 (9/14/2012)
The view you are trying to use is only available in SQL Server 2005 onwards. Your database is in SQL Server 2000 compatibility mode. Is there any reason to stay in that compatibility mode?
John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Gail, thanks for pointing that out.

Shobha, have you considered using DBCC CHECKIDENT?

John