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