Thursday, March 22, 2012

Error 8144 creating transactional publication

This one has me going for some time now:

I am trying to upgrade my SQL Server from a NT4+SQL7 to Win2K+SQL2000.

Here are the steps:

Install/Work order on "virgin" double Xeon server:
1) Win2K Server
2) SP3 for Win2K
3) SQL Server 7.0
4) SP3 for SQL Server 7.0
5) Restore full backup of master database.
6) Restore full backups of 10 databases (one named Q5)
7) Installed SQL Server 2000 Upgrade
8) SP3 for SQL Server 2000

At this point I tested my apps with the several databases and all looked good. But now I need to create a transactional publication on the Q5 database named Q5Pub.

First I go with the wizard but when he gets to the point where he is "Creating the Publication" he offers me the following:

SQL Server Enterprise Manager could not create publication 'Q5Pub' from database 'Q5'.
Error 8144: Procedure or function sp_MSadd_publication has too many arguments specified.

I then try using the Query Analyzer with the following statement:

exec sp_addpublication @.publication = N'Q5Pub', @.restricted = N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description = N'Transactional publication of Q5 database from Publisher SQLSERVER.', @.status = N'active', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.retention = 72

This gives me:

Server: Msg 8144, Level 16, State 2, Procedure sp_MSadd_publication, Line 0
Procedure or function sp_MSadd_publication has too many arguments specified.
Job 'SQLSERVER-Q5-23' started successfully.

So I look at the sp_addpublication stored procedure in the master database where I find:

...
/*
** Add the publication to the distributor side
*/
SELECT @.distproc = RTRIM(@.distributor) + '.' + @.distribdb +
'.dbo.sp_MSadd_publication'
EXECUTE @.retcode = @.distproc
@.publisher = @.@.SERVERNAME,
@.publisher_db = @.dbname,
@.publication = @.publication,
@.publication_type = @.rfid,
@.independent_agent = @.independent_agent_id,
@.immediate_sync = @.immediate_sync_id,
@.allow_push = @.allow_push_id,
@.allow_pull = @.allow_pull_id,
@.allow_anonymous = @.allow_anonymous_id,
@.snapshot_agent = @.null,
@.logreader_agent = @.agentname,
@.description = @.description,
@.retention = @.retention,
@.sync_method = @.smid,
@.allow_subscription_copy = @.allow_subscription_copy_id,
@.allow_queued_tran = @.allow_queued_tran_id,
@.queue_type = @.queue_type_val
...

But looking at the sp_MSadd_publication stored procedure in the distribution database I see:

CREATE PROCEDURE sp_MSadd_publication
@.publisher sysname,
@.publisher_db sysname,
@.publication sysname,
@.publication_id int = 0, -- BUG REMOVE
@.publication_type int = 1, -- 0 = Transactional 1 = Snapshot 2 = Merge
@.independent_agent bit = 0,
@.immediate_sync bit = 0,
@.allow_push bit = 1,
@.allow_pull bit = 0,
@.allow_anonymous bit = 0,
@.snapshot_agent nvarchar(100) = NULL,
@.logreader_agent nvarchar (100) = NULL,
@.description nvarchar(255) = NULL,
@.retention int =60,
@.vendor_name nvarchar(100) = 'Microsoft SQL Server'
as
...

So indeed the sp_addpublication calls the sp_MSadd_publication with too many arguments.
@.sync_method, @.allow_subscription_copy, @.allow_queued_tran and @.queue_type

What am I doing wrong?

Many thanks for any suggestion or/and help on this matter.Refer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q328908).sql

No comments:

Post a Comment