How can I change the table schema?

I have perform merge replication in the SQL server with access database,
however I would like to change the database schema in the SQL server. How
can I do that? e.g. such as adding a field in the table or changing the data
type of the column field.
Thank you
Regards
Jabba
use sp_repladdcolumn or sp_repldropcolumn. You can also right click on your
publication, select publication properties, go to Fitler Columns and add
your columns there.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jabba" <Jabba@.discussions.microsoft.com> wrote in message
news:C10B986C-F38D-4360-B945-11184633AD78@.microsoft.com...
> I have perform merge replication in the SQL server with access database,
> however I would like to change the database schema in the SQL server. How
> can I do that? e.g. such as adding a field in the table or changing the
data
> type of the column field.
> Thank you
>
> Regards
> Jabba

How can I change the default identity_range value in Merge replication

I have set up Merge Replication and everything is fine until a subscriber
goes offline for any length of time. The issue has to do with the setting of
the @.identity_range parameter for each article.
I am configuring the publication through the SQL Management Studio (server
is SQL 2005) and realise that I can run scripts and even alter the values
through the IDE.
What I want to do is change the default value that the IDE uses when it adds
the articles. The default seems to be 1000 which isn't really enough. We
would like to change the default to something like 10000
Does anybody know where SQL 2005 would store this value?
Thanks in advance,
Andrew Stanford
It is best that you not change meta data values in system tables. I'll
provide a way to change the indentiy range value for subscribers via the UI.
Since you already defined a merge publication, access the publication
properties by right clicking on the publication name under the
Replication:Local Publications node in SQL 2005 Management Studio. In the
left pane, select Articles. In the right pane, select the article you would
like to change the identity range values for. After highlighting the
article, use the Article Properties button. Scroll down to the identity
range management section and you should be able to change the Subscriber
Range Size value from 1000 to 10000. Hit OK, OK to confirm the changes.
Note that since you have an active merge publication already defined, the
subscriber will continue to use the old identity range values until they are
exhausted. The next time the merge agent requests a new ientitiy range from
the publisher, it will start using the new subscriber range value of 10000.
If you were to create a new publication, when you define the articles that
are in the publication you are able to drill into article properties and set
the subscriber range value to something other than 1000 up front.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Stanford" <andrew.stanford@.bakertilly.co.uk> wrote in message
news:uUeZt4j2FHA.956@.TK2MSFTNGP10.phx.gbl...
>I have set up Merge Replication and everything is fine until a subscriber
>goes offline for any length of time. The issue has to do with the setting
>of the @.identity_range parameter for each article.
> I am configuring the publication through the SQL Management Studio (server
> is SQL 2005) and realise that I can run scripts and even alter the values
> through the IDE.
> What I want to do is change the default value that the IDE uses when it
> adds the articles. The default seems to be 1000 which isn't really enough.
> We would like to change the default to something like 10000
> Does anybody know where SQL 2005 would store this value?
>
> Thanks in advance,
> Andrew Stanford
>

HostName and ReinitializeSubscription

Hello all,

I'm having trouble with my Merge Replication setup in regards to changing the HostName property of the SqlCeReplication object. As we all know, if you modify the HostName value, you must then call the ReinitializeSubscription(true) method before calling Synchronize(). Unfortunately, this isn't working for me. Following this sequence of steps consistently results in error messages saying that I need to either call ReinitializeSubscription, or change my HostName back to it's original value. Has anyone ever experienced this problem?

Regards,

Rob Tiffany

Hey Rob,

You are correct, once you specify a HostName value for a subscriber, if you want to change that value later, you must call SqlCeReplication.ReinitializeSubscription(True) followed by .Synchronize() to effectively make the change.

I built an app recently for a large field service scenario where the mobile device can change hands from field worker to field worker. I decided to use the field worker's unique identifier as the value for HostName so that I could replicate only the appropriate data for a given worker to a given SQL Mobile database. If the device changes hands, here is the process of making the change to HostName and reinitializing:

Public Function Synchronize() As Boolean

Dim retVal As Boolean = False

Dim repl As SqlCeReplication = GetReplication()

If repl Is Nothing Then
Return False
End If

Try

If Globals.GetInstance().HostNameChanged Then
repl.ReinitializeSubscription(True)
Globals.GetInstance().HostNameChanged = False
End If

repl.Synchronize()

etc etc etc

If you are doing something similar and getting an error, could you post the details on the error itself and Laxmi and I can look into it? Also, some info on your configuration (assume you are talking about SQL Mobile and SS2005?)

Regards,

Darren

|||

Yeah, that's what I'm doing too. My Sync code blocks looks like the following:

private void replicate(bool reInit)

{

SqlCeReplication repl = new SqlCeReplication();

repl.InternetUrl = Settings1.Default.InternetURL;

repl.InternetLogin = Settings1.Default.InternetLogin;

repl.InternetPassword = Settings1.Default.InternetPassword;

repl.Publisher = Settings1.Default.Publisher;

repl.PublisherDatabase = Settings1.Default.PublisherDatabase;

repl.Publication = Settings1.Default.Publication;

repl.PublisherSecurityMode = SecurityType.NTAuthentication;

repl.SubscriberConnectionString = @."Data Source=" + localFolder + "MyDB.sdf;Password=p@.ssw0rd;";

repl.Subscriber = Settings1.Default.Subscriber;

repl.HostName = txtAgentId.Text;

repl.CompressionLevel = Settings1.Default.CompressionLevel;

repl.ExchangeType = ExchangeType.BiDirectional;

try

{

if (!File.Exists(localFolder + "MyDB.sdf"))

{

repl.AddSubscription(AddOption.CreateDatabase);

}

if (reInit == true)

{

repl.ReinitializeSubscription(true);

}

// Synchronize with SQL Server 2005

repl.Synchronize();

}

finally

{

repl.Dispose();

}

}

If the user changes the HostName, then I pass "true" to the method above to call ReinitializeSubscription(true).

Thanks,

Rob

|||

there is a possible code path in your code where the database does not exist and you (are going to create it using AddOption.CreateDatabase on the first replication), but reinit can also be true - this won't work - you cannot mix CreateDatabase and Reinit in the same call to Synchronize.

Could that be what's happening? What error messages are you getting?

Darren

|||

I don't have the exact error message in front of me now, but is says something like:

Error: Changing the HostName requires a call to ReinitializeSubscription() or change your HostName back to the original value. HostName = 2, Original HostName = 1.

Something like that. Keep in mind that it will even throw this same error when I call ReinitializeSubscription().

To answer your other question, I haven't experienced the possible code path error you described where the database doesn't exist. It should also be known that this is a desktop XP project and not on Windows Mobile. Just in case we have subtle differences or issues between the desktop and handheld dlls. I'm trying to do some load testing and the desktop is the only way to create enough clients.

Rob

Hosted SQL and Replication

I have a hosted SQL database.
The hosting company does not offer to setup merge replication for me.
1) Is there a hosting company that offers replication too ?
2) If I can't do replication, is there anyway to merge two databases using a
DTS package or something ?
Thanks.
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Hi Michael Tissington,
From your descriptions, I understood that you would like to know whether
there is an workaround when you do not have the authority to make
replication. Have I understood you? If there is anything I misunderstood,
please feel free to let me know
Unfortunately, I dont think DTS could be used to replace Replication. From
BooksOnline or MSDN Online, we could fine the descriptions like these
--DTS--
Data Transformation Services (DTS) can be used to import and export data
between heterogeneous OLE DB and ODBC data sources. A DTS package is
defined that specifies the source and target OLE DB data sources; the
package can then be executed on an as-required basis or at scheduled times
or intervals.
--Replication--
Using replication, you can distribute data to different locations, to
remote or mobile users over a local area network, using a dial-up
connection, and over the Internet. Replication also allows you to enhance
application performance, physically separate data based on how it is used
(for example, to separate online transaction processing (OLTP) and decision
support systems), or distribute database processing across multiple servers.
If there is only a tiny modification in a large database, you will have to
make a whole new Data Transformation, which will must be cost huge of time
and sometimes money. while Replication will use little transactions to make
it
In the meanwhile, we could use DTS to transfer data between different data
source while replication has rather limitations.
Hope it helps and thank you for your patience and cooperation. If you have
any questions or concerns, don't hesitate to let me know. We are here to be
of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Michael,
there are many hosting companies out there who offer hosting with SQL Server
installed. Off the top of my head I don't know of any who offer replication
publishing services but the first one I found in a Google search offers
replication subscriber services:
http://www.1stchoiceinternational.com/sqlhosting.htm.
Using DTS to merge databases is not at all simple, but it is possible - you
can use binary checksums for each table to determine differences. BTW,
there's nothing inbuilt to allow you to do this, so all the code is to be
written in custom executesql tasks.
hth,
Paul Ibison
|||Paul,
Thanks for the link, just what I'm looking for.
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e7Ck3xeVEHA.2700@.TK2MSFTNGP10.phx.gbl...
> Michael,
> there are many hosting companies out there who offer hosting with SQL
Server
> installed. Off the top of my head I don't know of any who offer
replication
> publishing services but the first one I found in a Google search offers
> replication subscriber services:
> http://www.1stchoiceinternational.com/sqlhosting.htm.
> Using DTS to merge databases is not at all simple, but it is possible -
you
> can use binary checksums for each table to determine differences. BTW,
> there's nothing inbuilt to allow you to do this, so all the code is to be
> written in custom executesql tasks.
> hth,
> Paul Ibison
>