Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error creating Publication


Error creating Publication

Author
Message
jude.pieries
jude.pieries
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 447
- 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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
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
Thomas-428301
Thomas-428301
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 149
Sorry for not replying earlier , this did the trickBigGrin
bitfix99
bitfix99
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Perfect - did the job for me as well.BigGrin
rahmann60
rahmann60
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 227
It helped me as well. Thanks for your posting
Ed7
Ed7
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 930
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
Adam Wilbur
Adam Wilbur
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 591
Noel... you just saved me from banging my head on my desk for another 3 hours.



A.J.
DBA with an attitude
Phil Huffstatler
Phil Huffstatler
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 65
Wanted to add my own, "Thanks Dude!" to this thread... I couldn't see this property setting any other way!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search