creating view in sqlserver

Question

I trying to create view linking 2 tables admins and news

create view v_news as
SELECT [n_id]
  ,[n_title]
  ,[n_detail]
  ,[n_date]
  ,[n_sdate]
  ,[n_edate]
  ,[n_admin]
  ,[a_name]
  ,[a_email]
  ,[a_role]
  ,[a_status]
FROM hed2.dbo.hed_news,hed2.dbo.hed_admins
where hed_admins.a_id=hed_news.n_admin

This message is displayed:

Command(s) completed successfully.

but there is no view in VIEWS folder.

When I try to run the same query again then it says:

There is already an object named 'v_news' in the database.

I am connected with windows authentication
I tried reconnect and restart sql server but ....

Solution 1

Your view is using ANSI 92 syntax. While this will work for older database versions, it will not work in SQL Server 2012. See Mike Walsh's blog on this topic.

1 - When using SSMS views do not show up right away. Right click and hit refresh.

2 - It is very important to make sure you are in the correct database. I am sure many people, including me, have create an object or two in master. This is the default for a new login.

This can be changed by changing the default database for your login.

3 - Execute the USE command to change the database context (default).

The snippet below is a SQL Server 2012 compliant version.

USE [hed2]
GO

create view v_news as
SELECT [n_id]
  ,[n_title]
  ,[n_detail]
  ,[n_date]
  ,[n_sdate]
  ,[n_edate]
  ,[n_admin]
  ,[a_name]
  ,[a_email]
  ,[a_role]
  ,[a_status]
FROM dbo.hed_news JOIN dbo.hed_admins ON hed_news.n_admin = hed_admins.a_id
GO

Solution 2

Three things:

1. You must use JOINS and the way your query is written is bad. Just an observation. Nothing to do with your question.
2. Did you try running SELECT * FROM v_news? That would return results.
3. Right click and views in SSMS and select refresh. It will show up in the list

Raj