Sql-server – Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() – Two part name needed

sql serversql-clrvisual studio

In Visual Studio, when trying to create a DAC which has a schemabound view which contains hierarchyid::GetRoot() this error is generated and the project does not build. However, the t-sql is ok as if I create the view directly in the database it works ok.

Is there a two part name for hierarchyid to solve this?

In a DAC project is VS:

CREATE VIEW [dbo].[View1]
with schemabinding
AS SELECT hierarchyid::GetRoot() as t

Gives:

Error 93 SQL70561: Cannot schema bind view '[dbo].[View1]' because name 'hierarchyid' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Best Answer

The answer is to add sys. in front of the hierarchyId:

sys.hierarchyid::GetRoot()

Related Solutions

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server:

GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

Visual Studio – Why ‘AS’ Table Alias Keyword is Required

I think this is happening because you're qualifying the database name in the query.

So remove **DBName** form the select clause; Visual Studio would then accept it.

Best Answer

Related Solutions

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

Visual Studio – Why ‘AS’ Table Alias Keyword is Required

Related Question