sql-serversql-server-2008-express

Where is my view in SQL Server?


I have a table structured like this:

sensorId int, Value int, obDate datetime

in SQL Server 2008 Express.

I want to get the latest value for each sensor, so I've created a View Like this:

CREATE VIEW [dbo].[LAT_POLDATA] 
AS (
    SELECT A.sensorId, A.obDate, A.Value 
    FROM [Met].[dbo].[POLDATA] AS A
    INNER JOIN 
        (SELECT sensorId, MAX(obDate) AS obDate 
         FROM [Met].[dbo].[POLDATA] 
         GROUP BY sensorId) AS B ON A.sensorId = B.sensorId 
                                 AND A.obDate = B.obDate 
)

Now If I query Select * from [dbo].[LAT_POLDATA] I get values when I do this in Microsoft SQL Server Management Studio, but I don't see this view under the Views in my database.

If I query it from a ASP.net Service, the query fails and throws an error:

System.Data.SqlClient.SqlException: Invalid object name 'LAT_POLDATA'

Where is the view? Why don't I see it SSMS?


Solution

  • In Management Studio please try adding your preferred database name and execute it.

    use [yourDatabaseName]
    
    Select * from [dbo].[LAT_POLDATA]
    

    If that gives you an error that view is not in your database

    Then follow this

    USE [yourDatabasename]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    Create View [dbo].[LAT_POLDATA] AS (
        SELECT A.sensorId , A.obDate , A.Value 
        FROM [Met].[dbo].[POLDATA] AS A
        INNER JOIN (
            SELECT sensorId , MAX(obDate) AS obDate 
            FROM [Met].[dbo].[POLDATA] 
            GROUP BY sensorId  
        ) AS B
            ON A.sensorId  = B.sensorId 
            AND A.obDate  = B.obDate 
    )
    
    GO