Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Aveiro, Portugal
    Posts
    1

    Unhappy Unanswered: 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.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to this KBA.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •