Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error creating Publication Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 6:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:24 PM
Points: 36, Visits: 312
- I get the following error when I attempt to create a publication from the wizard

A exception occurred while attempting to execute a tsql statement or batch
Can not find procedure “”
Can not find procedure “”
Change the database context to “XXX” . (Microsoft sql server :2812)

- I tried creating the same from the following script and got an error message some related to the above

exec sp_addpublication @publication = N'database', @description = N'Transactional publication of database ''database'' from Publisher ''Server\I01''.',
@sync_method = N'concurrent', @retention = 0,
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true',
@enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous',
@status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'


The Error
Msg 2812, Level 16, State 62, Procedure sp_MSrepl_addpublication, Line 1227
Could not find stored procedure ''.


- I tried removing the database from the publisher property list and got the following error

The distributor has not been installed correctly, Could not disable the database for publishing , change the
database context to “dabase name” error 20029”


The last error message states that the Distributor DB is not setup correctly but there is no way to break the relationship between the Distributor DB and the attempted publication database(as I cant remove the publisher property from the database).

I finally tried changing the system table "sysdatabases" value of "category" from 16 to 0 and that failes as well.
I loged as DAC with single user mode and still failed to update the sysdatabases table( and yes I did sp_configure 'allow update',1 Reconfigure with Override)

Please let me know what I can do to over come this situation

Thanks in advance
Post #613590
Posted Thursday, December 4, 2008 11:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
jude.pieries (12/4/2008)
- I get the following error when I attempt to create a publication from the wizard

A exception occurred while attempting to execute a tsql statement or batch
Can not find procedure “”
Can not find procedure “”
Change the database context to “XXX” . (Microsoft sql server :2812)

- I tried creating the same from the following script and got an error message some related to the above

exec sp_addpublication @publication = N'database', @description = N'Transactional publication of database ''database'' from Publisher ''Server\I01''.',
@sync_method = N'concurrent', @retention = 0,
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true',
@enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous',
@status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'


The Error
Msg 2812, Level 16, State 62, Procedure sp_MSrepl_addpublication, Line 1227
Could not find stored procedure ''.


- I tried removing the database from the publisher property list and got the following error

The distributor has not been installed correctly, Could not disable the database for publishing , change the
database context to “dabase name” error 20029”


The last error message states that the Distributor DB is not setup correctly but there is no way to break the relationship between the Distributor DB and the attempted publication database(as I cant remove the publisher property from the database).

I finally tried changing the system table "sysdatabases" value of "category" from 16 to 0 and that failes as well.
I loged as DAC with single user mode and still failed to update the sysdatabases table( and yes I did sp_configure 'allow update',1 Reconfigure with Override)

Please let me know what I can do to over come this situation

Thanks in advance


First: Don't change "system tables" manually!

On the Publisher (and on the Subscriber if you got it set up right at some point )
Make sure that you can cleanup the db from any replication settings run :
exec sp_removedbreplication @dbname =  'dbname'

On The Distributor (could be the same as the publisher) clean it using:
TSQL
OR
SQL Server Management Studio

Then you can start over.

I hope this helps.



* Noel
Post #613970
Posted Wednesday, December 10, 2008 9:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:15 AM
Points: 17, Visits: 149
Sorry for not replying earlier , this did the trick:D
Post #617186
Posted Sunday, March 8, 2009 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 8, 2009 3:51 PM
Points: 1, Visits: 1
Perfect - did the job for me as well.:D
Post #671176
Posted Tuesday, October 27, 2009 2:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:38 PM
Points: 4, Visits: 208
It helped me as well. Thanks for your posting
Post #809583
Posted Tuesday, December 29, 2009 9:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:35 PM
Points: 354, Visits: 918
Hi Noel,
I run into same issues.
You mentioned “Make sure that you can cleanup the db from any replication settings”
Can you explain in details please?
Thanks,
Edwin
Post #839889
Posted Friday, August 20, 2010 5:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:33 PM
Points: 1,011, Visits: 568
Noel... you just saved me from banging my head on my desk for another 3 hours.



A.J.
DBA with an attitude
Post #972885
Posted Monday, November 15, 2010 10:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 10:54 AM
Points: 197, Visits: 65
Wanted to add my own, "Thanks Dude!" to this thread... I couldn't see this property setting any other way!
Post #1020932
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse