Schema change in client db

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Compact Edition » Schema change in client db

Schema change in client db

Rate Topic

Display Mode

Topic Options

Author

Message

Pratibha_Pillai

Posted Wednesday, May 27, 2009 6:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 01, 2009 5:15 AM
Points: 5, Visits: 13

Hi,
I came across default trace offered by sql server 2005.
1)Can it act as a substitute to DDL triggers?What's the difference between them?Also I didn't find any column in the log file where i can get the sql command which was fired last.

2) Also can anyone give me a brief idea as to what is the pupose of scripting(wherein we use the wizard to script different objects) is it also a way to track database schema changes?

Actually I am in a way to find the best way by means if which i can track the schema changes on server db and notify the same to the client(sql ce).

3)How can i propagate the schema changes from server to the client(mobile) and are the sql command compatible to sql ce commands?

Thanks in advance.

Post #724006

Jack Corbett

Posted Wednesday, May 27, 2009 9:25 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Pratibha_Pillai (5/27/2009)

No, the Default Trace cannot be substitute for DDL triggers. The Default Trace only traces very generic events like Object:Created or Object:Altered which do not actually contain the SQL command.

2) Also can anyone give me a brief idea as to what is the pupose of scripting(wherein we use the wizard to script different objects) is it also a way to track database schema changes?

Basically scripting is just a way to "backup" the database structure or re-create the structure elsewhere. You can use it to track changes if you put the original scripts in source control, make all changes there and then apply the changes to the database

Actually I am in a way to find the best way by means if which i can track the schema changes on server db and notify the same to the client(sql ce).

I would guess that using DDL triggers to get the changes and put in a queue (a table, xml, service broker) then have a process that pushes the changes out to the clients, checking a version # in the clients or something similar.

3)How can i propagate the schema changes from server to the client(mobile) and are the sql command compatible to sql ce commands?

I've never really worked with CE/Mobile so I don't know the best way to do this. I thought that most basic commands like Create Table, Alter Table were the same in CE.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #724247

Pratibha_Pillai

Posted Thursday, May 28, 2009 6:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 01, 2009 5:15 AM
Points: 5, Visits: 13

Hi Jack,

Thanks for the reply.I need few more guidelines from you.
Generally I got the answer as to how can I track the schema changes.My application is used for synchronization of database (client-sql ce and server-sql server 2005). As my application is of type synchronization the client can perform changes on the client db...now once its done ...there can be places where we can find conflicts when we try to execute the downloaded scripts(like if I am imposing primary key constraint I can get an error in the client db incase if the client has entered any duplicate rows).

1) how can I prevent such issues?Any strategy?

2)Also in my client db I have 2 additional columns other than the columns in the server(added after the first download of tables from server).And now if i try to insert any columns and then try to insert data there will be conflict becuase there is change in the ordinal position of the new column in the server and the new column in the client.
Any idea as to how can I resolve this issue ?

Post #724963

Jack Corbett

Posted Thursday, May 28, 2009 7:04 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Pratibha_Pillai (5/28/2009)

I believe you can use merge replication between SQL Server 2005 and SQL Mobile. You just need to define who wins.

2)Also in my client db I have 2 additional columns other than the columns in the server(added after the first download of tables from server).And now if i try to insert any columns and then try to insert data there will be conflict becuase there is change in the ordinal position of the new column in the server and the new column in the client.
Any idea as to how can I resolve this issue ?

If define your process using named columns then ordinal position should not matter.

Post #725014

itamar-863608

Posted Saturday, May 30, 2009 4:07 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 29, 2009 8:36 AM
Points: 13, Visits: 84

Pratibha_Pillai (5/27/2009)

A trace is not a substitute to a DLL trigger. a trace simply shows you the commands the sql server engine is performing at any different time. a DLL Trigger is a piece of code that gets fired on a schema change. The main question is what are you trying to do. Since what i believe that what you're trying to do is your question 2 and 3, I can recommend Randolph (SQL version control tool) which is really the best way to get those things done.

--------

SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools.

Post #726255

« Prev Topic | Next Topic »

Permissions