Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: hybrid replication (2000 and 2005)

    Ok here is the deal...

    We have our current dev box running SQL 2000 SP3
    We have two new desktop boxes that we are using as a test, they are both running SQL 2005 SP1

    The Dev Box = PUBLISHER
    Desktop #1 = DISTRIBUTOR
    Desktop #2 = SUBSCRIBER

    All three SQL Servers are using the same Domain Account to run SQL Agent and SQL Server processes.

    When I try to create a publication from the publisher, while running the sp_addpublication step through the UI (or thru SQL Query Analyzer) I get the following error message:

    Msg 15404, Level 16, State 10, Procedure sp_grant_publication_access, Line 136
    Could not obtain information about Windows NT group/user 'crumpte', error code 0xffff0002.

    So as a test I tried using our subscriber box as the publisher and had it point to the same distributor box to act as a distributor - and the publication was created fine.

    It seems to be an issue using SQL 2k as the Publisher and SQL 2005 as the distributor. From the research that I have done, I found this KB article:

    http://support.microsoft.com/default.aspx?kbid=830596

    Which essentially says to install the latest SP for SQL 2k, which would be SP4, in the past I know there were issues with AWE memory allocation.

    Q1. Has SP4 been hotfixed to resolve this AWE issue?
    Q2. Has anyone here successfully setup replication from SQL 2k -> SQL 2005 using SQL 2005 as the distributor?

    Thanks!

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Assumming that the s1 service is owned by dbo, to fix the problem simply change the owner of the database from 'domain\user' to a SQL login (i.e. SA)
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Hi Satya -

    Thanks for the reply, here is the current ownership:

    Publisher - SQL 2k - sa is the owner
    Distributor - SQL '05 - my NT account is the owner
    Subscriber - SQL '05 - my NT account is the owner

    I am not sure what the s1 service is that you are referring to - the first server (i.e. Publisher)? It is already owned by sa - are you recommending I make the other two Servers to be owned by sa as well?

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes, try to set the owner to sA on other databases as well.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Actually I was confused - the DB we were replicating from - had an NT account as owner - we changed it to sa and get the same error - I will take a look at the replication DB to see what the ownership is of that.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    Ok all DB's have sa as owner and we still get the error when calling:

    sp_addpublication

    do we need SP4 installed on our SQL 2k box? Do you know if the AWE issue still exists?

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes it has been fixed, refer to http://www.microsoft.com/downloads/d...DisplayLang=en link.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jun 2005
    Posts
    319
    Thanks Satya - so do you recommend we install SP4 to fix this problem?

    A little more info:

    If I configure SQL 2k machine (publisher) to act as distributor I can create the publication without any problems, I can also create it when the Publisher is running SQL 2005 and uses a SQL 2005 server as its distributor.

    The problem only exists when SQL 2k is the publisher and SQL 2005 is the distributor.

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    I am still trying to troubleshoot this.

    I was able to successfully create a publication from a desktop running SQL 2k, so at least I know it is possible, and I assume there is nothing wrong with my 2005 distribution server configuration.

    Now when I try to repeat what I did on our Development server running SQL 2k, this is the error I get when trying to create the publication (script generated by SQL 2005):

    use [DB-NAME]
    exec sp_addarticle @publication = N'cm-17', @article = N'COUNCIL_MEMBER', @source_owner = N'dbo', @source_object = N'COUNCIL_MEMBER', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000080A3, @auto_identity_range = N'false', @destination_table = N'COUNCIL_MEMBER', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboCOUNCIL_MEMBER', @del_cmd = N'CALL sp_MSdel_dboCOUNCIL_MEMBER', @upd_cmd = N'MCALL sp_MSupd_dboCOUNCIL_MEMBER'
    GO

    --OUTPUT
    Job 'JOB-NAME' started successfully.
    Server: Msg 15404, Level 16, State 10, Procedure sp_grant_publication_access, Line 136
    Could not obtain information about Windows NT group/user 'a_user_name', error code 0xffff0002.
    The statement has been terminated.

    ---------------------------------------
    All DB's are owned by SQL Account: 'sa'

    Development Server is running SQL 2K SP3 and was just hotfixed to version 818 (so it would be running the same version as the working desktop mentioned earlier)

  10. #10
    Join Date
    Jun 2005
    Posts
    319
    a little more info...

    I found the proc sp_addpublication - I scripted it out and ran it manually in SQL QA and now I get this error:

    Server: Msg 7411, Level 16, State 1, Line 845
    Server 'repl_distributor' is not configured for RPC.

    From what I can tell Remote Access (sp_option) is enabled on all servers so now I am really confused...

  11. #11
    Join Date
    Jun 2005
    Posts
    319
    ok this is REALLY REALLY strange - so I deleted the SQL Account it gave the error on - now it complains about another random SQL Account - I don't want to delete evey single SQL account to "fix" this problem - we certainly did not explicitly choose the accounts to authenticate against - did I miss a step somewhere?

    What is really puzzling is how I am able to set this up just fine from a desktop running MSDE, it is only while trying to set this up on a Server installation do I get this error when trying to create the publication.
    Last edited by Gagnon; 05-09-06 at 13:25.

  12. #12
    Join Date
    Jan 2006
    Posts
    2
    I'm having the same issue when attempting to publish from a 2K box to a 2K5 distributor. Did this issue get resolved?

  13. #13
    Join Date
    Jun 2009
    Posts
    1
    Hi,

    Is this issue resolved?

    I am trying to create a snapshot replication between 2008 and 2000.

    Publisher : 2000
    Distributor : 2008
    Subscriber : 2008

    I am facing the same problem.

Posting Permissions

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