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
Showing posts with label publication. Show all posts
Showing posts with label publication. Show all posts
Thursday, March 22, 2012
Friday, March 9, 2012
ERROR 515: while creating a merge replication publication
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I'm going to try reinstalling SQL Server and see if that takes care of it. I've tried the same operation on other installations and it works.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:unrh$fmoEHA.3792@.TK2MSFTNGP11.phx.gbl...
I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I'm going to try reinstalling SQL Server and see if that takes care of it. I've tried the same operation on other installations and it works.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:unrh$fmoEHA.3792@.TK2MSFTNGP11.phx.gbl...
I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
Friday, February 24, 2012
Error 2812: Could not find stored procedure and
Help! When we try to to delete a test publication on our sql server
2000 Developer Edition SP3a it returns an Error 2812: "Could not find
stored procedure"
What's weird is that we configured "Publishing, Subscribers and
Distribution" last night and after some tries we got thru the wixard
and we were able to set up a push subscription to a test database.
Now when we try to set up a new publication SQL server returns a "
server is not enabled as a Publisher on distibutor server" To enable
run "Publishing, Subscribers and Distribution and clich the Publishers
tab". When we do that we can only create a Distributor. Continuing on
that we get a new error: "21045 Cannot uninstall the Distributor
because there are databases enabled for publishing or merge
publishing". There is no way as far as we can see to disconnect any
enabled databases.
We feel like we're walking in circles. Help is greatly appreciated.
Martin,
try running these commands on each database then hopefully you'll be able to
uninstall the distributor and restart.
HTH,
Paul Ibison
exec sp_dboption 'yourdb','published',false
exec sp_dboption 'yourdb','merge publish',false
|||Thanks Paul, I was just about to write that I solved the problem by
uninstalling SQL and reinstalling. It looks like THAT did the trick.
No problems so far.
On Tue, 24 Aug 2004 19:39:33 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Martin,
>try running these commands on each database then hopefully you'll be able to
>uninstall the distributor and restart.
>HTH,
>Paul Ibison
>exec sp_dboption 'yourdb','published',false
>exec sp_dboption 'yourdb','merge publish',false
>
2000 Developer Edition SP3a it returns an Error 2812: "Could not find
stored procedure"
What's weird is that we configured "Publishing, Subscribers and
Distribution" last night and after some tries we got thru the wixard
and we were able to set up a push subscription to a test database.
Now when we try to set up a new publication SQL server returns a "
server is not enabled as a Publisher on distibutor server" To enable
run "Publishing, Subscribers and Distribution and clich the Publishers
tab". When we do that we can only create a Distributor. Continuing on
that we get a new error: "21045 Cannot uninstall the Distributor
because there are databases enabled for publishing or merge
publishing". There is no way as far as we can see to disconnect any
enabled databases.
We feel like we're walking in circles. Help is greatly appreciated.
Martin,
try running these commands on each database then hopefully you'll be able to
uninstall the distributor and restart.
HTH,
Paul Ibison
exec sp_dboption 'yourdb','published',false
exec sp_dboption 'yourdb','merge publish',false
|||Thanks Paul, I was just about to write that I solved the problem by
uninstalling SQL and reinstalling. It looks like THAT did the trick.
No problems so far.
On Tue, 24 Aug 2004 19:39:33 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Martin,
>try running these commands on each database then hopefully you'll be able to
>uninstall the distributor and restart.
>HTH,
>Paul Ibison
>exec sp_dboption 'yourdb','published',false
>exec sp_dboption 'yourdb','merge publish',false
>
Friday, February 17, 2012
Error 21085: The retention period
When I try to create and manage publication in SQL 7.0, I got the following
error message:
Error 21085: The retention period must be less than the retention period for
the distribution database.
I try to create publishor and distributor on the same server.
Thank you.
can you do a sp_helpdistributor and sp_helppublication. Note the retention
period for your distributor. The retention period for your publication
should be less than or equal to the retention period of your distributor.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:D19673BD-85CD-425A-A176-610D47E0ADE9@.microsoft.com...
> When I try to create and manage publication in SQL 7.0, I got the
following
> error message:
> Error 21085: The retention period must be less than the retention period
for
> the distribution database.
> I try to create publishor and distributor on the same server.
> Thank you.
error message:
Error 21085: The retention period must be less than the retention period for
the distribution database.
I try to create publishor and distributor on the same server.
Thank you.
can you do a sp_helpdistributor and sp_helppublication. Note the retention
period for your distributor. The retention period for your publication
should be less than or equal to the retention period of your distributor.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"royHe" <royHe@.discussions.microsoft.com> wrote in message
news:D19673BD-85CD-425A-A176-610D47E0ADE9@.microsoft.com...
> When I try to create and manage publication in SQL 7.0, I got the
following
> error message:
> Error 21085: The retention period must be less than the retention period
for
> the distribution database.
> I try to create publishor and distributor on the same server.
> Thank you.
Subscribe to:
Posts (Atom)