We have recently migrated to a new server where first the database was restored and then the replication has been setup. The same Merge agent works with the existing server but not with the new one.Below is the error which pops up when the merge agent is run:
The schema script 'Subscribed_Userdb99ffc3_18.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Could not drop object 'dbo.Subscribed_Users_Temp_Storage' because it is referenced by a FOREIGN KEY constraint. (Source: MSSQLServer, Error number: 3726) Get help: http://help/3726
When I am trying to drop the table it shows up table is being referenced by a foreign key.When i am trying to disable the foreign key it shows
Msg 3733, Level 16, State 2, Line 1 Constraint 'FK_Subscribed_User_Paypal_Details_Subscribed_Users_Temp_Storage' does not belong to table 'subscribed_users_temp_storage'. Msg 3727, Level 16, State 0, Line 1
And when I checked the schema of the table it shows the foreign key is there.
Does that means that foreign key is an orphan one,and if so then what is the possible solution to it. Could not drop constraint. See previous errors.
Looking at the drop FK error message in your question I think that you are trying to drop it on the wrong table. Note, that originally replication Agent tries to drop dbo.Subscribed_Users_Temp_Storage table, but reports that there is a Foreign Key, referencing it.
You have located the mentioned FK_Subscribed_User_Paypal_Details_Subscribed_Users_Temp_Storage foreign key, but it is NOT residing in the dbo.Subscribed_Users_Temp_Storage, instead based on its name I think it resides in the Subscribed_User_Paypal_Details table, while you are trying to drop it in dbo.Subscribed_Users_Temp_Storage.
I think the correct DROP FK statement should look like this:
ALTER TABLE [dbo].[Subscribed_User_Paypal_Details]
DROP CONSTRAINT [FK_Subscribed_User_Paypal_Details_Subscribed_Users_Temp_Storage]
Note, I assume the Subscribed_User_Paypal_Details table is also residing in [dbo] schema. If it resides in different schema, change the statement accordingly.
Note, there may be other Foreign Keys referencing the dbo.Subscribed_Users_Temp_Storage table. You will need to drop/disable and then recreate/enable them all after replication Agent recreates the dbo.Subscribed_Users_Temp_Storage table.
To find out all FKs referencing the dbo.Subscribed_Users_Temp_Storage execute the following command:
exec sp_help 'dbo.Subscribed_Users_Temp_Storage'
and scroll down to the SELECT result called Table is referenced by foreign key.
HTH