error when changing published article at the subscriber

Hi

Using merge replication - the stored procs are published articles.

When trying to change the article at the subscriber (sql express) I get the following errors:

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_alterschemaonly, Line 45
The DDL statement cannot be performed at the Subscriber or Republisher.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.

Msg 3609, Level 16, State 2, Procedure usp_Anniversary_upd, Line 21
The transaction ended in the trigger. The batch has been aborted.

Does this mean it can only be changed at the publisher ?

Bruce

correct. If you want to make schema changes at the subscriber, you need to set @.replicate_ddl to false in sp_addmergepublication.|||

Schema changes cannot be made at the subscriber. They have to come from the Publisher.

Message: correct. If you want to make schema changes at the subscriber, you need to set @.replicate_ddl to false in sp_addmergepublication.

I believe Greg meant "publisher only" instead of "subscriber" in the above statement.

|||

Hi

Just to clarify this.....

1. You cannot change it at the subscriber, irrespective of whether @.replicate_ddl is set to 0 or 1 ?

2. If @.replicate_ddl is set to 1, then changing the proc on the publisher will result in the proc being changed at the subscriber next time it is synchronised (if a published article)

Is this correct ?

I ask, because @.replicate_ddl is set to 1, but I get that error mentioned earlier if I try and change it at the subscriber.....

Thanks
Bruce

|||

1. you're correct, i was wrong in my earlier post.

2. correct.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?
|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.

Error Scripting Procs: Failed to retrieve dependency information

How can I resolve the following error when I try to script out some procs. In SSMS I right click the db and I choose 'Tasks > Generate Script', then I select 10 stored procs (without changing any of the default options). If I don't include the proc mentioned in the error below, then it works OK.

TITLE: Microsoft.SqlServer.SqlEnum

Failed to retrieve dependency information (object_id(System.Int32) 1271727633.object_type(System.Int16) 4.relative_id(System.Int32)

1879729799.relative_type(System.DBNull) .object_name(System.String) usp_SEL_RCEPCompanyHistory.object_schema(System.String)

dbo.relative_name(System.DBNull) .relative_schema(System.DBNull) .).

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Specified cast is not valid. (Microsoft.SqlServer.SqlEnum)

I'm having this problem, too. Any resolution?
|||

Please help us understand what kind of object caused the problem better by running the following queries and telling us what do they return.

select * from sys.objects where object_id = xxxx

select * from sys.sql_dependencies where object_id = xxxx or referenced_major_id=xxxx

select * from sys.assembly_modules where object_id = xxxx

You want to replace xxxx with the number that shows in the error message after relative_id.

Thanks,

Ciprian Gerea

|||

The object is a user-defined aggregate function ('Microsoft.Samples.SqlServer.Concatenate'). I think this problem has already been submitted in feedback here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125881

|||

I set up my machine as directed in the above issue report, using our current sp2 build, and was unable to reproduce the error reported here.

It may be the case that the two feedback items are evidence of two issues, with distinct repro scenarios, but I am assuming that we have fixed this, and that you can expect to see it in the upcoming release of sql server sp2.

|||

I was experiencing the same type of error. Your script above was helpful to me in identifying the SQL_dependencies for the procs that was throwing the errors. It turns out in my situation there were procs which were referencing the service queue objects and for what ever reasons it did not like those procs. I excluded those procs from my script generator and it worked.