Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
This is almost certainly an issue with DACs themselves -- I don't think it recognizes this as valid syntax. DACs don't support all features in SQL Server, and this appears to be one of them. –  Jon Seigel Sep 10 '12 at 16:44
    
I'm not totally convinced, it's what I initially thought, however, if you remove schemabinding then its ok. DAC 2.0 supports hierarchyId. –  Ross Buggins Sep 11 '12 at 7:42
add comment

2 Answers

up vote 4 down vote accepted

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

sys.hierarchyid::GetRoot()

share|improve this answer
    
Ah, yes. This makes sense. –  Jon Seigel Sep 11 '12 at 16:48
add comment

There are a couple of prerequisites to use the SCHEMABINDING option:

· You cannot use * in the SELECT clause in the query, you have to specify column names

· You have to use two-part naming convention when referring to objects (which is in general a good practice)

So, you need to use schema name always when referring to objects when you want to use the SCHEMABINDING option, in this case sys.hierarchyid

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.