Last week we have seen this error when we have initialized new Subscriber from snapshot that was generated for transactional replication after published database schema was updated:
‘An error has occurred: Incorrect syntax near where’
This error occurred after about one hour of applying snapshot to subscribers database and caused restart of the whole process.
Investigation of error details in distribution database tables was lost time. First after monitoring of the full traffic against the subscriber database we have discovered that our error was caused by this statement:
EXEC(N' CREATE UNIQUE NONCLUSTERED INDEX [UX_RecordCategory_Record_ID_Category_ID] ON [ts].[RecordCategory]([Record_ID],[Category_ID]) WITH (DATA_COMPRESSION = PAGE ) WHERE ([Record_ID] IS NOT NULL AND [Category_ID] IS NOT NULL)')
What’s wrong there? It’s maybe not visible at the first look but yes, there is serious syntax error: WITH (DATA_COMPRESSION = PAGE ) should be after the WHERE first:
EXEC( N'CREATE UNIQUE NONCLUSTERED INDEX [UX_RecordCategory_Record_ID_Category_ID] ON [ts].[RecordCategory]([Record_ID],[Category_ID]) WHERE ([Record_ID] IS NOT NULL AND [Category_ID] IS NOT NULL) WITH (DATA_COMPRESSION = PAGE ) ')
This invalid statement was hidden in one of *.idx files in snapshot folder. *.idx files are executed as last during snapshot application so this is the reason why we were waiting for this error more than one hour (and 60 GB of data inserted to subscriber DB).
The general message for the whole audience is that you simply cannot generate valid snapshot in case that your database contains filtered indexes with data compression at the same time. MS guys should do better job when testing new features:)
You can check your database for this kind of indexes with this query:
SELECT * FROM sys.[indexes] [i] INNER JOIN sys.[partitions] [p] ON [p].[object_id] = [i].[object_id] AND [p].[index_id] = [i].[index_id] WHERE [i].[has_filter] = 1 AND [p].[data_compression_desc] <> 'NONE'
I have created Microsoft Collaborate item for this.