Post reply

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

  • June 27, 2014 at 10:31 am

    #288022

    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.

  • June 27, 2014 at 10:53 am

    #1725536

    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

  • June 27, 2014 at 11:13 am

    #1725541

    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

  • July 2, 2014 at 8:19 am

    #1726517

    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'.]

Viewing 4 posts - 1 through 3 (of 3 total)

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