I receive the following error (taken from replication monitor):
The option 'FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME' is only valid when used on a FileTable. Remove the option from the statement. (Source: MSSQLServer, Error number: 33411)
The command attempted is:
CREATE TABLE [dbo].[WP_CashCenter_StreamLocationLink](
[id] [bigint] NOT NULL,
[Stream_id] [int] NOT NULL,
[Location_id] [numeric](15, 0) NOT NULL,
[UID] [uniqueidentifier] NOT NULL
)
WITH
(
FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UC_StreamLocation]
)
Now, for me there's two things unclear here.
Table already existed on subscriber, and I've set @pre_creation_cmd = N'delete' for the article. So I don't expect the table to be dropped and re-created. In fact, table still exists on subscriber side, although create table command failed to complete. What am I missing? Where does this create table command come from and why?
I don't understand why does this FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME option appear in creation script. I tried generating create table script from table in SSMS and indeed, it's there. But what's weird, I can't drop and re-create the table this way - I get the very same error message.
EDIT: Ok, I guess now I know why the table is still there - I noticed begin tran in sql server profiler.
If your table on the publisher is truly not defined as a FileTable, then the issue has to do with the column named "Stream_id". I believe there is a known issue in SQL 2012 where if you have a column named "Stream_id", which is kind of reserved for FileTable/FileStream, it will automatically add that constraint, and unfortunately break Replication. The workaround here is to rename the column to something other than "Stream_id".
Another workaround is to set the schema option to not replicate constraints (guessing this will work). If you require constraints on the subscriber, you can then try to manually apply them on the sbuscriber after the fact (or script them out and use @post_snaphsot_script).