Added a new stored proc to a snapshot publication.
-
I have a snapshot publication, full of proc/views/funcs, working all grand. I've just created a new stored proc, which I'd like to add to this publication. I added it using sp_addarticle, looks fine. The new proc is listed when I goto publication properties and view articles. However when I start the snapshot agent, it doesn't include this new proc. The agent history has no mention of it and it doesn't exist at the subscriber after a sync up. I've tried reinitialising, but still no avail.
Also my post script to set permissions fails due to its non existance.
Any ideas, is there something else I need to do?
Thanks,
Arthur.
-
You probably passed the wrong value for one of the parameters of sp_addarticle. Can you post the query you used?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Sorted this now. I had to run a system stored proc to refresh/update the subscription. Found the answer on a random google result, but forgotten it now and can't for the life of me find it again...
It was sp_****subscription or something along those lines. I'll keep looking and post back here, when I find it again. After running it I just needed ro recreate the snapshot and fire it off. BTW the params I padded into sp_addarticle were:-
exec sp_addarticle @publication = N'MyPublication', @article = N'SS_UTIL_MyProc_5', @source_owner = N'dbo', @source_object = N'SS_UTIL_MyProc_5', @destination_table = N'SS_UTIL_MyProc_5', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000002001, @status = 16
GO
PS I noticed the other day in the SQL Server 2005 docs, the @schema_option param has a bit to copy permissions, is this new to 2005? I don't remember this one and I'm having to use post-scripts to set permissions at the subscriber.
-
Yes, that is a new schem option in SQL 2005. There are a lot of new options!!
Is this what you are looking for:
sp_refreshsubscriptions
Add subscriptions to new articles in a pull subscription for all the existing Subscribers to the publication. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_refreshsubscriptions [ @publication = ] 'publication'
Arguments
[@publication =] 'publication'
Is the publication to refresh subscriptions for. publication is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_refreshsubscriptions is used in snapshot, transactional, and merge replication.
sp_refreshsubscriptions is called by sp_addarticle for an immediate-updating publication.
Permissions
Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_refreshsubscriptions.
See Also
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply