February 21, 2012 at 4:39 am
I have implemented several indexed views in a production database which I replicate as part of a transactional model onto another server for reporting users. Included in the list of replicated objects are several stored procedures which reference these indexed views using the WITH (NOEXPAND) hint. The problem I’m having is that when I initialize a subscription for the first time, SQL server tries to create the stored procedures before the indexes on the indexed views have been created, rendering the WITH (NOEXPAND) hint invalid and stopping the initialization in its tracks. Can anyone advise a way around this? Thanks. Ps, I'm on SQL2008, but there wasn't a replication forum for that.
May 26, 2012 at 10:38 am
I am new to SQL server.
Received same error "hint noexpand invalid object" during subscription initialization.
Is there a workaround or a fix?
June 11, 2012 at 3:02 pm
Have you tried setting up the views and SP's as separate publications? Should be able to then control the initialization schedule to ensure the objects get created in the proper order.
Regards,
Kyle
June 12, 2012 at 2:18 am
Hi Kyle,
Yeah this is exactly what I've done to get around this issue. I've now got one transactional publication for the tables and indexed views, and one snapshot publication for the views and SPs. However, it's left me with another issue (see http://www.sqlservercentral.com/Forums/Topic1130135-291-2.aspx).
JD
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply