Add a rowID to view - 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

SQL Server Administration (2005)

Add a rowID to view

Author

Topic

dengar
Starting Member

4 Posts

Posted - 2009-03-11 : 10:35:24

Hi,

I have a problem with a view I created: I can't add a row_id which I want to use as a primary key...

It sorta works with:


  row_number() OVER (ORDER BY ContainerID) AS ID

But I get an error:

quote:

The OVER SQL construct or statement is not supported.

The ID is still there when I look at the view... I was quite happy with that, but I also use this view in a cube and here comes the problem.

For some reason I can't process the cube (this used to work fine) and I get this error:

quote:

Error: 0xC1000007 at Analysis Services Processing Task, Analysis Services Execute DDL Task: Internal error: The operation terminated unsuccessfully.
Warning: 0x811F0001 at Analysis Services Processing Task, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_CAD1, Column: ID, Value: 442671.

Now I don't exactly know what this is mean to tell me, I looked at said row, but nothing seems to be out of the ordinary...

My question is: Is there any other way to add a primary key to a view?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 10:38:36

You need SQL Server 2005 or later to make use of the new windowed functions.
Also your compatibility level must be set to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"