June 27, 2012 at 12:12 am
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
June 27, 2012 at 3:44 am
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
June 27, 2012 at 3:51 am
What is the compatibility level of your database?
John
September 14, 2012 at 1:34 am
What permission should I have on the database?
Koen Verbeeck (6/27/2012)
It works fine with me.Do you have the right permissions?
September 14, 2012 at 1:35 am
Hi john,
The compatibility level shows 80.
John Mitchell-245523 (6/27/2012)
What is the compatibility level of your database?John
September 14, 2012 at 2:02 am
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
September 14, 2012 at 2:43 am
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
September 14, 2012 at 2:47 am
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
September 14, 2012 at 4:08 am
Gail, thanks for pointing that out.
Shobha, have you considered using DBCC CHECKIDENT?
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply