Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to add an Identity(autocounter) integer field (as Primary key)as first column of a SQL View Expand / Collapse
Author
Message
Posted Friday, June 27, 2014 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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.





Post #1586957
Posted Friday, June 27, 2014 10:53 AM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347

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
Post #1586968
Posted Friday, June 27, 2014 11:13 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
Views can't have primary keys.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1586973
Posted Wednesday, July 2, 2014 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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'.]
Post #1588484
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse