Views in MS SQL Server

SQL statements are known as "views" in Microsoft SQL Server. They are the equivalent of "queries" in MS Access. They can be accessed with the MS SQL Server utility "SQL Enterprise Manager". A series of views was created for MBARI_Samples to be called from the web page queries through the ASP pages. This method was employed to allow queries to be changed in one place rather than having to change them every ASP page. If a table's design is changed, the views must be recreated (bring each one up in Enterprise Manager and click on the green arrow).

if exists (select * from sysobjects where id = object_id('dbo.vwCollSamplePerson') and sysstat & 0xf = 2)
    drop view dbo.vwCollSamplePerson
GO

CREATE VIEW vwCollSamplePerson
AS
SELECT CollectionEvent.CollectionEventDTG,
    CollectionEvent.TapeTimeCode,
    CollectionEvent.FrameGrabImageURL,
    CollectionEvent.CollectionRefName,
    CollectionEvent.Interpreter,
    CollectionEvent.CollectionVIMSConcept,
    CollectionEvent.Equipment,
    CollectionEvent.Latitude,
    CollectionEvent.Longitude,
    CollectionEvent.Depth,
    CollectionEvent.DiveName,
    CollectionEvent.WaypointName,
    CollectionEvent.Temperature,
    CollectionEvent.Salinity,
    CollectionEvent.CTDPressure,
    CollectionEvent.DissolvedOxygen,
    CollectionEvent.Comment,
    CollectionEvent.CTDLookupStatus,
    vwSampleCollectorContact.*, CollectionGroup.*
FROM vwSampleCollectorContact, CollectionEvent, CollectionGroup
WHERE (vwSampleCollectorContact.CollectionEventID = CollectionEvent.CollectionEventID)
AND (CollectionEvent.CollectionGroupID = CollectionGroup.CollectionGroupID)
GO

GRANT SELECT ON dbo.vwCollSamplePerson TO public
GO

GRANT SELECT ON dbo.vwCollSamplePerson TO everyone
GO

GRANT SELECT ON dbo.vwCollSamplePerson TO samWebUser
GO

»Next: GIS display
SamplesDB documentation in frames

Last updated: 05 January 1999 by Jennifer Paduan
Copyright © 1998 MBARI