Create View with schemabinding from other database table

Hello,
I am trying to create index view fore that I went to create view with
schemabinding .
If I try table in same database it work properly. But my database is
different from I went to create view.
syntax is this
CREATE VIEW VSTATE WITH SCHEMABINDING AS
SELECT STATE, ID, NAME
FROM [gujarat-villages].dbo.[FPT-GUJARAT]
WHERE (DISTRICT = '0')
it give me following error message :
Cannot schema bind view 'VSTATE' because name
'gujarat-villages.dbo.FPT-GUJARAT' is invalid for schema binding. Names must
be in two-part format and an object cannot reference itself.> If I try table in same database it work properly. But my database is
> different from I went to create view.
One of the requirements for schema binding is that the referenced objects
must be in the same database as the schemabound object. This is why a *two*
part rather than three part name is required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:0AE95685-811C-40EE-B740-8544C5F5D09E@.microsoft.com...
> Hello,
> I am trying to create index view fore that I went to create view with
> schemabinding .
> If I try table in same database it work properly. But my database is
> different from I went to create view.
> syntax is this
> CREATE VIEW VSTATE WITH SCHEMABINDING AS
> SELECT STATE, ID, NAME
> FROM [gujarat-villages].dbo.[FPT-GUJARAT]
> WHERE (DISTRICT = '0')
> it give me following error message :
> Cannot schema bind view 'VSTATE' because name
> 'gujarat-villages.dbo.FPT-GUJARAT' is invalid for schema binding. Names
> must be in two-part format and an object cannot reference itself.
>
>|||You can't use SCHEMABINDING if you refer to a able in a different database. Here's a quote from
Books Online (CREATE VIEW):
"SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the
base table or tables cannot be modified in a way that would affect the view definition. The view
definition itself must first be modified or dropped to remove dependencies on the table that is to
be modified. When you use SCHEMABINDING, the select_statement must include the two-part names
(schema.object) of tables, views, or user-defined functions that are referenced. All referenced
objects must be in the same database."
See the last sentence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:0AE95685-811C-40EE-B740-8544C5F5D09E@.microsoft.com...
> Hello,
> I am trying to create index view fore that I went to create view with schemabinding .
> If I try table in same database it work properly. But my database is different from I went to
> create view.
> syntax is this
> CREATE VIEW VSTATE WITH SCHEMABINDING AS
> SELECT STATE, ID, NAME
> FROM [gujarat-villages].dbo.[FPT-GUJARAT]
> WHERE (DISTRICT = '0')
> it give me following error message :
> Cannot schema bind view 'VSTATE' because name 'gujarat-villages.dbo.FPT-GUJARAT' is invalid for
> schema binding. Names must be in two-part format and an object cannot reference itself.
>
>