February 5, 2025 at 3:47 pm
We have three servers that use SQL replication; PROD, BI, and PARTS.
We have one database that replicates from PARTS to PROD and we have a around five databases that replication from BI to various Azure servers.
We used to have a database called IRES that replicated from PROD to Azure - but we deleted that replication. This is the root of our problem.
If we try to do add a new publication or subscription, we get the following error:
Invalid object name 'IRES..syspublications'.
Could not use view or function 'dbo.syspublications' because of binding errors. (Microsoft SQL Server, Error: 208)
I know that when a database is part of a publication, that creates syspublications on that database; and it apparently deletes the view when you delete the replication. So why is it looking for a view in that database? And how do we get it to stop?
February 5, 2025 at 5:07 pm
UPDATE:
I've managed to work out that when you try to create a new publication or subsciption, it runs this:
exec sp_helpreplicationdboption @reserved = 1
Which loops through all of your databases and for each one, runs a procedure called sys.sp_MSrepl_helpreplicationdboptionex
I can't find that second procedure. The script appears to execute it against each database, but I'm not finding it in any of them. Regardless, in my test environment (basically a clone of production) I manually created the table syspublications by copying 0 rows from another database that had it. This seems to get me past the error.
Still not sure why sp_helpreplicationoption is expecting to find that table in database IRES in the first place. My assumption is that when we deleted the publication for IRES that something didn't get cleared out somewhere, or some flag didn't get unset - but cannot find anything.
As a last resort we can create the table - but I really don't like that because I don't know what else might be incorrect.
Any advice/ideas would be greatly appreciated.
thanks
February 5, 2025 at 6:04 pm
Found it...
The issue actually had nothing to do with IRES.
We have five identical databases, each one representing a business unit. At some point in the past, we created a new database that consists entirely of views that union together the tables of the five databases. Basically so that instead of having to use unions in all of our application code, we can just select from the view of the same name.
We created all these views dynamically; and at the time, syspublications got created because it was a table that existed on IRES - so sitting in this views database, there is a view called syspublications that is "select * from IRES..syspublications" - which doesn't exist anymore.
Apparently the stored procedure is looking in each database for something called "syspublications" to select from, and it finds it, but it doesn't work because the table no longer exists.
February 7, 2025 at 2:18 pm
Appreciate the reply!
I did run that procedure.
The issue turned out to be unrelated to replication - at least not directly. We have a database that is essentially just a bunch of views that union together tables from multiple databases; and these views were created dynamically. As it turns out, we unintentionally included the tables related to replication - so there is a view called syspublications that is "select * from IRES..syspublications" - and since that table does not exist anymore, the view is unusable.
Apparently when you run the wizard to create a publication or subscription, it looks for that object in each database and tries to select from it. Since it found the object in IRES but the object was an invalid view, it threw an error.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply