SQL Server Error Messages - Msg 166

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