2

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.

Improve this question
2
  • 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
    Commented Sep 10, 2012 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
    Commented Sep 11, 2012 at 7:42

2 Answers 2

Reset to default
4

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

sys.hierarchyid::GetRoot()

Improve this answer
1
  • Ah, yes. This makes sense.
    – Jon Seigel
    Commented Sep 11, 2012 at 16:48
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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