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

You have to create view permission plus (possibly) ALTER SCHEMA permission on the schema where the view is created. — David Brabant
– David Brabant, Commented Sep 3, 2013 at 10:03

CRAFTY DBA · Accepted Answer · 2013-09-03 20:24:01Z

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

Raj · Accepted Answer · 2013-09-03 10:02:43Z

1

Three things:

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

Raj

answered Sep 3, 2013 at 10:02

Raj

10.9k2 gold badges47 silver badges53 bronze badges

1 Comment

Your Yummy Over a year ago

yes SELECT * FROM v_news show results but dont know where the view is stored how can i find and move to views folder I refreshed view folder but it is not here

Collectives™ on Stack Overflow

creating view in sqlserver

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related