Making schema changes

I have been reading up on how replicating schema changes work, but I must be missing something. When I configured a database for replication, it added a rowguid column to each table. This I understand.

I want to test making a schema change and replicating that change. For example, I tried to add a column to an existing table. When I tried to save the table i get the following error:

"Error validating the default for column rowguid".

"- Unable to modify table.
It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted."

So, what am I missing? Do i need to drop the rowguid column, add the new column, and then re-add the rowguid column?

Many thanks...

Well, it works if you do it via T-SQL. So, my question now is, what does the GUI (SSMS) do that T-SQL does not that let's T-SQL add a column to a table that is marked for replication?|||

It's documented in BOL that schema changes via SSMS is not supported. See BOL topic "Making Schema Changes on Publication Databases", http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

|||

Thanks Greg. I figured out schema changes via SSMS was not supported, but I was wondering why. And I know that SSMS drops and re-creates the table for any changes. So my question was more out of curiosity than anything, wondering why SSMS does that and T-SQL doesn't.

Thanks again...

|||You should post that question in the Tools forum then |||

The current SSMS design is that when you alter a table, it will drop and re-create the table underneath. Now one cannot drop a table when it is replicated. Because of this reason when SSMS is trying to drop the constraint/table, it throws up this error.

To workaround you can continue to make changes in TSQL.

Maintenance plan wizard missing

Under Management, all I have is SQL Server Logs and Activity monitor, no option to create a maintenance plan or run the wizard ?Under management you have Maintenance plans,Sql server logs,activity monitor,dataabse mail etc........your question sounds bizzare.....just check if you are logged in with sysadmin privilege.......|||

which edition of sql server is this. I thing Express edition u have . in that case MP not supported in this edtion

Madhu

|||I am running SQL 2005, not Express.|||SQL 2005

maintenance plan stops with a xpstar.dll is missing msg

Hi,

I used to have a maintenance plan for my database, I just realized that the backup wasn't taken for more than 2 weeks . and when ever i try to view the Jobs or the maintenance plan i get this error msg : cannot load the DLL xpstar.dll, reason126:(the specified moudle could not be found).

I looked under the Bin directory and couldn't find the xpstar.dll file and i'm sure i didn't delete it .so what should i do ? could i just copy paste the file or i need to reinstall the SQL ?

Can you check whether SQLAgent has been started correctly, also confirm what account has been used to start SQLAgent service.

It seems the XPSTAR.DLL is missing and try to find the file from any other location in the server, if not try to copy from another machine which is similar to this installation.

|||

I copied the file and it worked :)

Thank you.