Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add an Identity(autocounter) integer field (as Primary key)as first column of a SQL View


How to add an Identity(autocounter) integer field (as Primary key)as first column of a SQL View

Author
Message
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 465
How do I add an Is Identity field with Identity Increment 1 and Identity Seed 1
It also needs to be set to the Primary Key

A working View exist that can be run from SSMS, Access (via SQL Server Native Client 11.0 Linked Tables), and Excel (via ODBC).
It just needs the first column with the identity (auto-counter) and for that to become the primary key.



It appears that the GIS ODBC driver requires a unique Primary Key.
The existing View can be consumed with ODBC or SQL Server Native Client 11.0 on Excel or Access.
But, when the GIS ODBC tries to link, the error Loading Table Data Attribute column not found[42S22] Invalid column name 'Well'.] appears.
However, the same GIS ODBC can successfully read other tables in the same SQL Server DB that have an Primary Key identity field.
In the past when GIS was consuming an Access DB, it wouldn't work until an autocounter Primary Key was added to the table.
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 465
CREATE VIEW [dbo].[vRegulatory_Nav_GISC]
AS
SELECT ROW_NUMBER() over(order by Reg_ID_Wells desc) as RowNum, dbo.vEDWells_List.ID_Wells AS Reg_ID_Wells, ...
-- Also removed the ORDER BY clause

However, I still need to make the RowNum (Row_Number output) the Primary Key
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
Views can't have primary keys.


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


Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 465
Many thanks! Did learn a couple of new things.
The Identity key worked. And, the Primary Key on a view is not possible.
The actual problem was that I used a space to define a two word field name. e.g. Well Status vs Well_Status
It was an accident, my default is to use an underscore rather than a space.

Interesting enough, the data could be consumed with Native Client 11.0 and standard ODBC (for Access and Excel) but not in a GIS ODBC workstation. They send me a screen shot of the error.
The error was "Could not load data from data source. Attribute column not found[42S22:[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'Well'.]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search