Creating Index on a view based on OpenRowSet
Posted on 2001-06-12
Here's my scenario:
I have an Informix DB running my Legacy System. Nightly, I use DTS packages to transfer some of the Info from Informix to SQL Server 7.0. I recently found out that I can link the Informix DB to the SQL Server DB and see the objects. I got that to work then I created views in the SQL Server DB based on the tables in the Informix DB. The problem then was that queries ran against the views took forever - solution - create index on the views. SQL Server 7 doesn't allow that but 2000 does so I upgraded. Now I it's telling me that "'Openrowset' is not allowed on schema-bound objects".
A typical view looked something like this:
Create view MyView
FROM OPENQUERY(Linked_Database, 'SELECT * FROM MyTable')
Ultimately, I want SQL Server to have a live link with the Informix DB so that I can get rid of those nightly transfers and then create views based on the tables in the linked Informix DB and create indices on these views.