September 29, 2004 at 5:07 am
I have this problem:
I have big table (on linked server) and I would like to store (physicaly syncronize) sample data (select from linked server table) on my SQL server. Originaly I thing I use somthing like materialized view in Oracle, but I cannot create indexed view from linked server. Do you have any ideas how automatically insert data into my table?
I try create indexed view but it is not possible on linked server table. I must use with schemabinding, this is problem/error. Can I create indexed view on linked server tables? Is any possibilities?
PRO is linked server (oracle), SAPR3 is owner
CREATE VIEW BKPF2
WITH SCHEMABINDING
AS
SELECT BELNR, GJAHR, BLART, USNAM, TCODE, CPUDT, PPNAM
FROM PRO..SAPR3.BKPF
result>
Server: Msg 4512, Level 16, State 3, Procedure BKPF2, Line 4
Cannot schema bind view 'BKPF2' because name 'PRO..SAPR3.BKPF'
is invalid for schema binding. Names must be in two-part format and
an object cannot reference itself.
--CREATE UNIQUE CLUSTERED INDEX bkpf_belnr ON BKPF (BELNR)
zd
October 1, 2004 at 1:40 am
I have two ideas (neither perfect)
1. Would it be adequate to have a local table and a SQLAgent job refreshing the local table every hour ? (There are lots of possible reasons against this - volume?; or you need consistency ?)
2. Are you able to create the materialised view in the Oracle database ? Then you can have a simple view on it from SQLServer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply