SQL Server Performance Forum – Threads Archive

CreateIndex disallowed

I’m trying to create an index on a view but I can’t becaues one of the tables the view is based on was not created with SET ANSI_NULLS ON. This table is part of a control database of a third-party product so I can’t recreate it. Is there a way I can alter the original table or some other way to get around this problem? Here’s the code.
CREATE view preq_activity_view
with SchemaBinding
as select b.procid as procid,
b.svrid as svrid,
b.witemseq as witemseq,
b.prtcpname as prtcpname,
b.prtcp as prtcp,
b.state as item_state,
b.creationdtime,
b.cmpltdtime,
b.deadlinedtime,
b.state,
c.name as respname
from dbo.witem as b
INNER JOIN dbo.resp as c ON b.procid = c.procid and b.respgrpseq = c.respgrpseq and b.respseq = c.respseq
INNER JOIN dbo.procs as a ON a.procid = b.procid
WHERE a.preprocdefname LIKE ‘%PurReq%’ GO
CREATE INDEX creationdtime_index
ON preq_activity_view( creationdtime)
GO and here’s the error message
Cannot create index. Object ‘resp’ was created with the following SET options off: ‘ANSI_NULLS.’.

---

Then in your creation script, start with set ansi_nulls off, and end with set ansi-nulls on.

---

]]>