Manage changes in SQL object.

1. How do I manage changes in SQL Server objects? Not schema.

SQL Server does not have built-in version control. so can we manage or not.

Use an external source control provider to store the sql server object scripts in.|||

Help me understand what you mean by

SQL Server objects? Not schema

SQL Server objects ARE schema...

|||

Hi Arnie,

I have many databse.

Schema means- like procedure or function etc.

I want to use data in VSS not schema.

|||

VSS is typically NOT for data. VSS is source control for schema objects, i.e., table definitions, view definitions, stored procedure and function code, etc. In other words, source control for the schema objects source code.

If you want a tool to manage data, then perhaps you should examine some of the third party products that will be able to compare databases and provide you reports and scripts about the differences in data.

Comparison Tools
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare
DTS Comparison
Red Gate – DTS Package Compare
Server Comparison
Quest - ServerCompare
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

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.