Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Currency Symbols
Error Messages
Home > SQL Server Error Messages > Msg 166 - 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
SQL Server Error Messages - Msg 166 - 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

Error Message

Server: Msg 166, Level 15, State 1, Line 1
'CREATE VIEW' does not allow specifying the database 
name as a prefix to the object name.

Causes:

This error occurs when creating a view on another database, which is not allowed because a view can only be created in the current database.  Also, when creating a view in the current database, don’t include the database name in the name of the view as the error will also be encountered.

To illustrate, the following script will generate the error:

CREATE VIEW [Northwind].[dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO
Server: Msg 166, Level 15, State 1, Line 2
'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

Solution / Work Around:

To avoid this error, remove the database name when creating a view:

CREATE VIEW [dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO

If you need to create a view on another database, change your database first then create the view without the database name:

USE Northwind
GO

CREATE VIEW [dbo].[CustomerCountries]
AS
SELECT DISTINCT [Country]
FROM [dbo].[Customers]
GO
Related Articles :