SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema change in client db


Schema change in client db

Author
Message
Pratibha_Pillai
Pratibha_Pillai
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
Pratibha_Pillai (5/27/2009)
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.


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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Pratibha_Pillai
Pratibha_Pillai
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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 ?
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
Pratibha_Pillai (5/28/2009)
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?


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.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
itamar-863608
itamar-863608
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 84
Pratibha_Pillai (5/27/2009)
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.


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search