在sqlserver中创建视图 [英] creating view in sqlserver

问题描述

我试图创建链接2个表的管理员和新闻的视图

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

显示以下消息：

Command(s) completed successfully.

但VIEWS文件夹中没有视图。

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.

我已与Windows身份验证连接

我尝试重新连接并重新启动sql server，但是....

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

推荐答案

您的视图使用的是ANSI 92语法。虽然这适用于旧版本的数据库，但不适用于SQL Server2012。请参见迈克·沃尔什（Mike Walsh）的博客关于此主题。

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-使用SSMS时，视图不会立即显示。右键单击并单击刷新。

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

2-确保您在正确的数据库中非常重要。我相信包括我在内的许多人都已经在master中创建了一个或两个对象。这是新登录名的默认设置。

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-执行USE命令更改数据库上下文（默认）。

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

下面的代码段是与SQL Server 2012兼容的版本。

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

这篇关于在sqlserver中创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！