none
Unable to add new publication RRS feed

  • Question

  • I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard.
    I programmatically did the following:
    1.  I set up m
    y local server as a distributor, and created a distribution database  (@distributor set to server sysname)
    execute sp_adddistributor @distributor = @distributor
    execute sp_adddistributiondb @database = @distributionDB,  @security_mode=1;

    2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor')
    SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher +
    ''', @distribution_db = ''' + @distributionDB +
    ''', @working_directory = ''' + @directory + ''', @security_mode = 1'
    SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql '
    EXEC @sp_executesql @sql
    3. I enabled my local database for transactional replication (@replicationDB)
    exec sp_replicationdboption
    @dbname = @replicationDB,
    @optname = 'publish',
    @value = 'true
    4. I added a logReaderAgent on the ReplicationDB
    SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1'
    SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql
    EXEC @sp_executesql @sql

    All is well up to this point, no problems, all looks great.  When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy.  Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.

    I tried programmatically, and also by using the wizard. 
    I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'.
    This is the sql I try to run:
    EXEC sp_addpublication
    @publication = 'test3333',
    @status
    = N'active',
    @allow_push = N'true',
    @allow_pull = N'true'
    This is the error:
    Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320
    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348
    Could not create the publication.
    If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording):
    Creating Publication
    - Creating Publication 'NewPublication' (Error)
           
    Messages
           
    * SQL Server could not create publication 'NewPublication'. (New Publication Wizard)
           
           
    ------------------------------
           
    ADDITIONAL INFORMATION:
           
           
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
           
           
    ------------------------------
           
           
    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.

            Could not create the publication.
           
    Object 'NewPublication' does not exist or is not a valid object for this operation.
           
    Changed database context to 'CypressMaster'.
           
    The statement has been terminated. (Microsoft SQL Server, Error: 515)
           
           
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

           

    - Adding articles (Stopped)

    - Starting the Snapshot Agent (Stopped)

    - Generating a script file (Stopped)

    Friday, June 2, 2006 11:16 PM

Answers

  • 
    Don't add a log reader agent before you create the publication. It should be done after.

    --
    Hilary Cotter
    Director of Text Mining and Database Strategy
    RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
     
    This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
     
    Looking for a SQL Server replication book?
    http://www.nwsu.com/0974973602.html
     
    Looking for a FAQ on Indexing Services/SQL FTS
    http://www.indexserverfaq.com
     
     
     

    I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard.
    I programmatically did the following:
    1. I set up m
    y local server as a distributor, and created a distribution database (@distributor set to server sysname)
    execute sp_adddistributor @distributor = @distributor
    execute sp_adddistributiondb @database = @distributionDB, @security_mode=1;

    2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor')
    SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher +
    ''', @distribution_db = ''' + @distributionDB +
    ''', @working_directory = ''' + @directory + ''', @security_mode = 1'
    SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql '
    EXEC @sp_executesql @sql
    3. I enabled my local database for transactional replication (@replicationDB)
    exec sp_replicationdboption
    @dbname = @replicationDB,
    @optname = 'publish',
    @value = 'true
    4. I added a logReaderAgent on the ReplicationDB
    SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1'
    SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql
    EXEC @sp_executesql @sql

    All is well up to this point, no problems, all looks great. When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy. Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.

    I tried programmatically, and also by using the wizard.
    I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'.
    This is the sql I try to run:
    EXEC sp_addpublication
    @publication = 'test3333',
    @status
    = N'active',
    @allow_push = N'true',
    @allow_pull = N'true'
    This is the error:
    Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320
    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348
    Could not create the publication.
    If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording):
    Creating Publication
    - Creating Publication 'NewPublication' (Error)
    Messages
    * SQL Server could not create publication 'NewPublication'. (New Publication Wizard)

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.

    Could not create the publication.
    Object 'NewPublication' does not exist or is not a valid object for this operation.
    Changed database context to 'CypressMaster'.
    The statement has been terminated. (Microsoft SQL Server, Error: 515)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

    - Adding articles (Stopped)

    - Starting the Snapshot Agent (Stopped)

    - Generating a script file (Stopped)

    Saturday, June 3, 2006 10:49 AM

All replies

  • 
    Don't add a log reader agent before you create the publication. It should be done after.

    --
    Hilary Cotter
    Director of Text Mining and Database Strategy
    RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
     
    This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
     
    Looking for a SQL Server replication book?
    http://www.nwsu.com/0974973602.html
     
    Looking for a FAQ on Indexing Services/SQL FTS
    http://www.indexserverfaq.com
     
     
     

    I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard.
    I programmatically did the following:
    1. I set up m
    y local server as a distributor, and created a distribution database (@distributor set to server sysname)
    execute sp_adddistributor @distributor = @distributor
    execute sp_adddistributiondb @database = @distributionDB, @security_mode=1;

    2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor')
    SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher +
    ''', @distribution_db = ''' + @distributionDB +
    ''', @working_directory = ''' + @directory + ''', @security_mode = 1'
    SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql '
    EXEC @sp_executesql @sql
    3. I enabled my local database for transactional replication (@replicationDB)
    exec sp_replicationdboption
    @dbname = @replicationDB,
    @optname = 'publish',
    @value = 'true
    4. I added a logReaderAgent on the ReplicationDB
    SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1'
    SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql
    EXEC @sp_executesql @sql

    All is well up to this point, no problems, all looks great. When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy. Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.

    I tried programmatically, and also by using the wizard.
    I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'.
    This is the sql I try to run:
    EXEC sp_addpublication
    @publication = 'test3333',
    @status
    = N'active',
    @allow_push = N'true',
    @allow_pull = N'true'
    This is the error:
    Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320
    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348
    Could not create the publication.
    If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording):
    Creating Publication
    - Creating Publication 'NewPublication' (Error)
    Messages
    * SQL Server could not create publication 'NewPublication'. (New Publication Wizard)

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.

    Could not create the publication.
    Object 'NewPublication' does not exist or is not a valid object for this operation.
    Changed database context to 'CypressMaster'.
    The statement has been terminated. (Microsoft SQL Server, Error: 515)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

    - Adding articles (Stopped)

    - Starting the Snapshot Agent (Stopped)

    - Generating a script file (Stopped)

    Saturday, June 3, 2006 10:49 AM
  • Hi,

    I am using the below code from http://technet.microsoft.com/en-us/library/ms188738.aspx to create a new publication. I was succesful upto adding the log reader agent. Later to that, the sp_addpublication always fails with the error:

    Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1325Cannot insert the value NULL into column 'pubid', table 'tlMain.dbo.IHpublications'; column does not allow nulls.

    Tried to create publication first AND then ad the log reader agent. When I run sp_addpublication, I get this:

    Job 'NU-DB-01-tlMain-8' started successfully.
    Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.

    Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1325
    Cannot insert the value NULL into column 'pubid', table 'tlMain.dbo.IHpublications'; column does not allow nulls. INSERT fails.
    The statement has been terminated.


    Either ways, I am not able to create the publication. Please HELP!!

    EXEC sp_replicationdboption
    @dbname=@publicationDB,
    @optname=N'publish',
    @value = N'true';

    -- Execute sp_addlogreader_agent to create the agent job.
    EXEC sp_addlogreader_agent
    @job_login = @login,
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default)
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

    -- Create a new transactional publication with the required properties.
    EXEC sp_addpublication
    @publication = @publication,
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';

    -- Create a new snapshot job for the publication, using a default schedule.
    EXEC sp_addpublication_snapshot
    @publication = @publication,
    @job_login = @login,
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default)
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;


    Vijay V
    Thursday, May 21, 2009 7:49 AM
  • Don't create the log reader agent. It will be created automatically after you create the publciation.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, May 21, 2009 11:33 AM
    Moderator