Materialized Views

I'm having a problem creating a materialized view. Here's the view
definition:
Create View audit_Report
WITH SCHEMABINDING
AS
select a.audit_id, a.status_id, a.audit_date, a.reference_name...
from dbo.audits a
join dbo.references r
on a.reference_id=r.reference_id
The view is created successfully.
I then tried to create a clustered index:
Create clustered index ar_idx on audit_report(audit_id)
and got this result:
When the audit table was created the following Set options were set to off:
ANSI_NULLS
Is there anyway of getting around this? The table has info in it, so I
don't want to drop it and re-create it.MAS wrote:
> I'm having a problem creating a materialized view. Here's the view
> definition:
> Create View audit_Report
> WITH SCHEMABINDING
> AS
> select a.audit_id, a.status_id, a.audit_date, a.reference_name...
> from dbo.audits a
> join dbo.references r
> on a.reference_id=r.reference_id
> The view is created successfully.
> I then tried to create a clustered index:
> Create clustered index ar_idx on audit_report(audit_id)
> and got this result:
> When the audit table was created the following Set options were set to off
:
> ANSI_NULLS
> Is there anyway of getting around this? The table has info in it, so I
> don't want to drop it and re-create it.
AFAIK the only solution is to SET ANSI_NULLS ON and then re-create the
table. Always leave ANSI_NULLS set to ON.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--