replication technique

Hello,
I read serveral articles about the mechanism of replication from a sql
server 2000 database on
desktop with an mobile device running windows mobile.
I want to remark my understanding fo the replication mechanism and wanted to
ask you if you
could give me feedback if I understand it in a correct way.
-----
1) First of all I create a publication - which is a collection of articles -
rows. It is
possible to define certain tables and rows for publication.
2) After the publication a windows ce-based application can call the methods
of the SQL Server
Ce Replication object to subscribe to the publication. When the mobile
application has executed
the subscription the initial snapshot of the published table is stored on
the mobile device and
so the database file is created on the device.
3) The subscription database on the device can then be updated by
synchronzing it with the desktop
database. For that the synchronisation is always intiated by the device. For
that the SQL Server CE
Client Agent extracts all modified rows from the subscription database put
these rows in a message
file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
Server CE Server Agent
creates a new message file on the desktop where the IIS is running and
writes in that file the
modified rows which are sent by the SQL Server CE Client Agent.Then the SQL
Server CE Server Agent
initiates the SQL Server Reconciler process. The SQL Server Reconciler
Process loads the SQL Server CE
Replication Provider which reads the message file which was created by the
SQL Server CE Server Agent
and apply the changes to the publisher database. The SQL Server CE
Replication Provider informs the SQL Server
Reconciler about the changes which were made in the subscription
database.The SQL Server Reconciler
also solves conflicts if more than one subscriber or publisher updates the
same record.
On the other hand the SQL Server Reconciler informs the SQL Server CE
Replication Provider of
changes which occured at the publisher database. The SQL Server CE
Replication writes this
information - especially the modified rows - to an output message file.
After the Reconciler
process is completed the SQL Server CE Server Agent locates the the output
message file reads
the information and transfer it to the SQL Server CE Client Agent which
apply the changes from
the output message file to the subscription databse on the windows ce device.
-----
a) Are these steps correct or have I forgotten anything important or have I
understand something
wrong?
b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
Server CE Server Agent,
SQL Server Reconciler and the SQL Server CE Replication Provider? What are
these terms exactly?
c) When I publish a database, especially tables in the enterprise manager
the tables get automatically
a row guid. What part plays these row guid's in my above mentioned
replication process?
d) The Replication Mechanism uses static and dynamic filtering. Is it right
that the dynamic filter
could not use on the application layer? - means that it is not possiblt to
replicate for example
the information according to the user which is currently logged in the
application? - I think it
is only possible to synchronize according to a certain device especially
certain host, is that right?
I know this is a extensive posting but I would appreciate it very much if
anybody find time to
answer me my questions. :-/
Regards.
patrick
answers inline
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pat" <pat@.discussions.microsoft.com> wrote in message
news:273513F0-8240-4E1A-9598-9ED04BC448CE@.microsoft.com...
> Hello,
> I read serveral articles about the mechanism of replication from a sql
> server 2000 database on
> desktop with an mobile device running windows mobile.
> I want to remark my understanding fo the replication mechanism and wanted
> to
> ask you if you
> could give me feedback if I understand it in a correct way.
> -----
> 1) First of all I create a publication - which is a collection of
> articles -
> rows. It is
> possible to define certain tables and rows for publication.
Yes, you will need to set up a merge publication. When you say rows for
publication you mean horizontal filtering.
> 2) After the publication a windows ce-based application can call the
> methods
> of the SQL Server
> Ce Replication object to subscribe to the publication. When the mobile
> application has executed
> the subscription the initial snapshot of the published table is stored on
> the mobile device and
> so the database file is created on the device.
Yes, you will code something using vb or vc embedded or the .Net compact
framework to pull a merge subscription. This will use a SQL CE database on
your PocketPC.
> 3) The subscription database on the device can then be updated by
> synchronzing it with the desktop
> database. For that the synchronisation is always intiated by the device.
> For
> that the SQL Server CE
> Client Agent extracts all modified rows from the subscription database put
> these rows in a message
> file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
> Server CE Server Agent
> creates a new message file on the desktop where the IIS is running and
> writes in that file the
> modified rows which are sent by the SQL Server CE Client Agent.Then the
> SQL
> Server CE Server Agent
> initiates the SQL Server Reconciler process. The SQL Server Reconciler
> Process loads the SQL Server CE
> Replication Provider which reads the message file which was created by the
> SQL Server CE Server Agent
> and apply the changes to the publisher database. The SQL Server CE
> Replication Provider informs the SQL Server
> Reconciler about the changes which were made in the subscription
> database.The SQL Server Reconciler
> also solves conflicts if more than one subscriber or publisher updates the
> same record.
> On the other hand the SQL Server Reconciler informs the SQL Server CE
> Replication Provider of
> changes which occured at the publisher database. The SQL Server CE
> Replication writes this
> information - especially the modified rows - to an output message file.
> After the Reconciler
> process is completed the SQL Server CE Server Agent locates the the output
> message file reads
> the information and transfer it to the SQL Server CE Client Agent which
> apply the changes from
> the output message file to the subscription databse on the windows ce
> device.
>
This is basically it.
> -----
> a) Are these steps correct or have I forgotten anything important or have
> I
> understand something
> wrong?
that's basically it

> b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
> Server CE Server Agent,
> SQL Server Reconciler and the SQL Server CE Replication Provider? What are
> these terms exactly?
> c) When I publish a database, especially tables in the enterprise manager
> the tables get automatically
> a row guid. What part plays these row guid's in my above mentioned
> replication process?
it uniquely identifys each row, so SQL Server can figure out which row has
changed or needs changing.

> d) The Replication Mechanism uses static and dynamic filtering. Is it
> right
> that the dynamic filter
> could not use on the application layer? - means that it is not possiblt to
> replicate for example
> the information according to the user which is currently logged in the
> application? - I think it
> is only possible to synchronize according to a certain device especially
> certain host, is that right?
>
No, you define your filter on the publication. I don't believe you can
filter your subscribers.

> I know this is a extensive posting but I would appreciate it very much if
> anybody find time to
> answer me my questions. :-/
> Regards.
> patrick
>
|||Hello,
Thank you very much for your reply but few things are still unclear:
1) Why do I need the row guid? - the changed rows are saved in a file and
are sent to the SQL Server CE Server Agent, so he knows which rows were
modified when he looks into the message file. Why does he need then a row
guid?
2) The SQL Server CE Client Agent, the SQL, Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - are these
processes or components or parts of the os? what I have to imagine behind
these terms?
3) Referring to dynamic filtering I thought it is possible to synchronize
after a certain host on the mobile device? - What can I then realize with
dynamic filterin?
(static filtering is when I define some rows or columns or tables which I
can then synchronize or?)
Hopefully you could answer me once more.
regards
patrick
3)
|||Hi,
Thank you very much for your reply but unfortunately few things are unclear:
1)Why do I need this row guid's, because the SQL Server CE Server Agent
reads the modified rows from the message file which is sent by the SQL
Server CE Client Agent and so the SQL Server CE Replication Provider knows
what rows he has to modify. So why do I neet this row guids?
2)Tthe SQL Server CE Client Agent, the SQL Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - what do I have
to imagene behind these terms? Are these processes or components or part of
the os or anyting else?
3)Referring to the filtering. So it is not possible to define a filter
according to a certain host on a mobile device? - The static filtering allows
to define some rows or columns or tables which can be modified, but then for
what is the dynamic filtering?
regards
patrick
|||hi,
is it possible to answer me the last time on my three posted questions?
regards
patrick
sql

Replication problem with SQL Mobile

Dear ppl,

I have a windows mobile 5.0 pocket PC application that performs replication between a Publihser (PC, SQL SERVER 2005), and subscriber (SQL MOBILE) on the pocket PC. The problem I am having is as follows

I created a bidirectional publication (SQL SERVER 2005). Then the pocket pc application subscribe to it (SQL Mobile). Pocket PC app add some records and then synchronise the data. Lets suppose the Pocket PC app adds 2 records on SQL Mobile in a table with primary key 2000 and 2001, which also reflected on the server after syncronisation.

Now for some reason I had to delete the publication and recreate it. My pocket PC resubscribed to the new publication, synchronise the data and get records 2000 and 2001 in SQL Mobile db. Now when the pocket Pc app tries to add a new record to it, it comes up with the error something like "duplicate values not allowed". What it is trying to do is, it is trying to add a new record with id 2000, which is already there in the table and therefore ending up throwing exception.

Can anyone please tell me what is the solution to this problem. Am i missing anything? Do i need to do something with configuring publication while recreating it after deleting it.

Regards
Nabeel Farid

Is this PK column an identity column on the table?

Or is the app generating the value 2000?

Can you subscriber to this publication on a different mobile database and try inserting there?

Replication over Replication...

Hi all.

I have created two replications. One from the main server (ERP) to my Mobile Manager Application (MMA) and the other one is from the MMA to all Mobile Devices (Tablet PCs). The last replication works fine (MMA => MD). But when the first replication (ERP => MMA) tries to update the tables (which are the same on both replications) I get the error that the first table to update cannot be updated because it is included in a replication.

I'm not that much experienced working with replication, so I'm looking for some answers to try to solve this problem. Maybe there are some other methods to work this issue.

I'd really appreciate any help or suggestions to work this around.

Thanks.

Fabio.

Fabio,

It would help if you can provide more details. For example, do you use transactional replication (readonly or updatable?) between ERP and MMA and merge replication between MMA and MD? And what is the exact error message your are seeing? What are you trying to do when the error happens (like a DML update or running replication agent etc). Plus the version and edition of SQL Server on each machine.

Thanks,

Zhiqiang Feng

|||Make sure you setup ERP to MMA first and then setup MMA to MD.|||

Thanks Zhiqiang for your reply.

In order to give you a better vision of this issue I have to say that:

1. Things are configured exactly as you said (ERP => MMA - Transactional (but Readonly) it only updates MMA, and MMA => MD - Merge Replication).

2. I configured first the ERP => MMA Replication, create my initial snapshot and the propagate it to MMA. After that created my MMA => MD replication and followed the previous steps detailed before.

3. Everything works fine between MMA => MD publication and subscriptions.

4. SQL Sever versions for ERP and MMA are 2000 SP4 and MD are MSDE SP3a.

My problem issues when ERP => MMA replication tries to update the data at MMA Database. It seems to try to modify some tables on the MMA Server, which I don't understand because it is supposed to be done when propagating the initial snapshot. Another thing I didn't say (sorry for that) is that I have this 2 replications because my customer doesn't want to expose his main server to the internet.

On the MMA machine I control some data like orders approval, customer payments and deposits procedures.

Thanks again for the help. I appreciate so much.

Fabio

|||

Fabio,

I was trying to setup a test environment to simulate what you described, will update my findings with you once I have some results. By the way, what's the exact error message your re seeing? error number, description etc?

Thanks,

Zhiqiang Feng

|||

Hi Zhiqiang.

I have found the answer for my problem. Thanks a lot for your interest in helping me.

In this link: http://www.replicationanswers.com/Republishing2005.asp I found exactly what I was looking for.

It may help others which have the same case.

Thanks again. I really appreciate your effort.

Fabio.

|||

Glad to hear that problem got solved!

Thanks,

Zhiqiang Feng

Replication Monitor Question

Hello,

We have some mobile devices that are setup to replicate with our sql 2005 server via web sync. It seems that the devices are added multiple times within replication monitor. Is there a way to purge the bogus items. For example we have a mobile unit labeled as Device12 we have it listed under the subscriptions 5x. I would like to purge four of them as they are not currently being the replicated one.

Device12-A6EE69A0944 is the valid subscription

Device12-EE44D1EF3E24 is an invalid one.

Thanks in advance.

John

You may want to query table dbo.MSsubscriptions in the distribution database to see if it has those extra entries. I'm not saying it's safe to delete them, but I'm guessing that's where sql monitor is pulling those rogue entries from.

Replication mechanism

Hello,
My situation is as follows:
I use Replication in my Mobile Application and when the PocketPc is
connected via USB the replication works fine. But when I tried to synchonize
the data via WLAN it does not work. My quoted url in my code looks as follows:
oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also tried
it to replace the IP-Address with the machineName of my Desktop but it does
also not work.
I also tried to type in this address in my PocketPc when the WLAN is
activated but it occurs always an error that this page could not be loaded.
Does anybody know what else could be go wrong?
thx
patrick
what is the error message you are getting?
Also what happens when you use IE to connect to this url from your PocketPC?
You should get a message saying SQL Server CE agent or something like that.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:09678E54-FBB5-44EA-9327-6ED5FCBD28D7@.microsoft.com...
> Hello,
> My situation is as follows:
> I use Replication in my Mobile Application and when the PocketPc is
> connected via USB the replication works fine. But when I tried to
> synchonize
> the data via WLAN it does not work. My quoted url in my code looks as
> follows:
> oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also
> tried
> it to replace the IP-Address with the machineName of my Desktop but it
> does
> also not work.
> I also tried to type in this address in my PocketPc when the WLAN is
> activated but it occurs always an error that this page could not be
> loaded.
> Does anybody know what else could be go wrong?
> thx
> patrick
|||That's the strange thing no error message occurs. When it is possible to
call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc and
the SQL Server CE Agent message appears, does then the replication must work?
patrick
|||Not necessarily, all it means is that your Pocket PC can connect to the web
server and the virtual directory. I think it also means that your web server
security is set up correctly. Now check the underlying security of your
snapshot folder. Does this reflect read access for the anonymous account
(IUSER_MachineName, or whatever account you are authenticating with)?
Then you should run profiler to determine how far into the replication
process your pull is going. This will help to trouble shoot further.
What would be really helpful is if you could post the error message your
program is raising.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:732A1E5F-E844-4D0C-B119-B75692989AF7@.microsoft.com...
>
> That's the strange thing no error message occurs. When it is possible
> to
> call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc
> and
> the SQL Server CE Agent message appears, does then the replication must
> work?
> patrick

Replication issues between SQL mobile and SQL2k?

I meant to cross-post this over here, but not having much response in SQLCE
forum ...
I'm in the process of a 'forced' upgrade (my client bought newer PocketPCs).
Soooo .. what I would like to know is whether there are any issues
connecting SQL Mobile to SQL 2000 for replication. Currently, replication
works "as advertised" between SQLCE and SQL2000 (enterprise).
The models seem quite symmetrical. I have not had any problems ...yet.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eQT3Y76BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>I meant to cross-post this over here, but not having much response in SQLCE
>forum ...
> I'm in the process of a 'forced' upgrade (my client bought newer
> PocketPCs).
> Soooo .. what I would like to know is whether there are any issues
> connecting SQL Mobile to SQL 2000 for replication. Currently, replication
> works "as advertised" between SQLCE and SQL2000 (enterprise).
>
|||We're using this setup in a production environment and so far things work in
a similar fashion to our SQL CE to SQL 2000 replication. And...we get the
added benefit of background or async replication, which is very nice.
- will
"Earl" wrote:

> I meant to cross-post this over here, but not having much response in SQLCE
> forum ...
> I'm in the process of a 'forced' upgrade (my client bought newer PocketPCs).
> Soooo .. what I would like to know is whether there are any issues
> connecting SQL Mobile to SQL 2000 for replication. Currently, replication
> works "as advertised" between SQLCE and SQL2000 (enterprise).
>
>

Replication Issue with Dynamic Filter using HOST_NAME()

Dear ppl,

I am having a couple of problems with using dynamic filters in Merge Replication (SQL Server 2005 - SQL Mobile)

1) I am trying to add a dynamic filter to my publication using the wizard as follows:

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

This gives me an error Conversion failed when converting the nvarchar value 'MSSWS02' to data type int. because my computer name cannot be converted to an integer.

After reading on http://msdn2.microsoft.com/en-us/library/ms152478.aspx I have found that it has been recommend specifying CONVERT(nchar,AuditID) = HOST_NAME() in the wizard and then use sp_changemergearticle to change the clause to AuditID = CONVERT(int,HOST_NAME()) before creating a snapshot for the publication. Therefore I tried to run the following sql inside SQL Server, but it gives me the same error.

DECLARE @.publication AS sysname;
DECLARE @.article AS sysname;
SET @.publication = N'HASSystem';
SET @.article = N'Audit';

USE [HASSystem]

EXEC sp_changemergearticle
@.publication = @.publication,
@.article = @.article,
@.property = N'subset_filterclause',
@.value = N'AuditorID = CONVERT(int,HOST_NAME())',
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1;
GO

Conversion failed when converting the nvarchar value 'MSSWS02' to data type int.

Anyone knows whats wrong with it ? Is there anything i m missing?

2) The second problem I am having is that I added a dynamic filter to my publication

SELECT <published_columns> FROM [dbo].[Audit] WHERE CONVERT(nchar,AuditorID) = HOST_NAME()

which works fine but later on I decided to drop this dynamic filter and reinitialise the subscriptions and creating a new snapshot. But after deleting the dynamic filter and when snapshot agent tries to create a new snapshot, it gives me the following error.

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

Anyone knows whats wrong with it? How can i redefine my publication so that it doesn't use dynamic filters?

Regards
Nabeel Farid

I wouldnt set the dynamic filters with conversions, there could be other issues down the road.

To get around the problem of

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

use sp_changemergepublication and set dynamic_filters='false'

Also make sure your articles' filter clause are nulled out too.

|||

thanx for the help Mahesh... As for the conversion issue, what would you recommend then ? I am doing replication between SQL Mobile and SQL Server 2005. I want to filter out data based on AuditorID in a table on PDA, which is an integer. The SqlReplication class exposes a HostName property which is a string and is the only parameter that i can use as far as my knowledge is concerned.

In articles in the publication i recieve that AuditorID in HOST_NAME() which is a string as well. That is why in dynamic filters I convert HOST_NAME() to int or the UserId column to string (viceversa) as follows

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

Is there any way I don't have to use conversion? and also if i can pass more parameters from the PDA ?

Regards

|||

I am not totally against the conversion. It works in some cases and it doesnt in others. I just did not want you to take this road and face some issues down the road.

You could add a new column which is string and use this for filtering. the values in this column would correspond to the ones in the AuditorID. If you cannot, then continue as per the recommendation and we can troubleshoot any problems you would face.

|||

Thanx for the help Mahesh.

I have another question for you. One of our client has asked us how is the data secured over the air during Replication?

I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.

We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and using GPRS connection, they all synchronise with a 2 Publications SQL Server 2005. Both publications are of the same database and resides on the same server. We haven't got any SSL certificates installed on the server.

Now how can i make use of TLS in my application to secure my data over the air (using TLS)?

Regards,

|||

Replication does not provide anything out of the box for secure transmission or encryption. It relies on the SQL server protocols to do that.

You can use the certificates and SSL for encrypting data sent to/from SQL Server.

Take a look at this article and see if that helps you. http://msdn2.microsoft.com/en-us/library/ms189067.aspx

|||I would still like to know how to do the conversion. Using sp_changemergearticle still generates the same "cannot convert" error message that the New Publication Wizard generates.

What is the workaround? I would really like to use a UserID (int) in the filter clause.
|||
I had the same problem and i coudn't figure out how to convert the hostname() either though wizard or through the sp_changemergearticle ... it gives the same error....therefore I had to convert the column to string to make it work instead of Hostname...(which is not a recommended practice)... and u might face problems with that later on...depends|||I agree that there are a number of messy workarounds. However, in the article you cited early in this thread (concerning Parameterized Filters) there is a large, bolded note about using sp_changemergearticle.

If this is out of date and no longer works for security/whatever reasons, it'd be nice to hear from an MS dev/test. However, if that note in the article still applies but with some restrictions (i.e. some system parameters need to be set initially), I'd like to know that as well.
|||Did anyone figure out if this can be done without converting the column to a string. I really want to keep the column as an int and be able to override the hostname() with an int I pass in.

Replication Issue with Dynamic Filter using HOST_NAME()

Dear ppl,

I am having a couple of problems with using dynamic filters in Merge Replication (SQL Server 2005 - SQL Mobile)

1) I am trying to add a dynamic filter to my publication using the wizard as follows:

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

This gives me an error Conversion failed when converting the nvarchar value 'MSSWS02' to data type int. because my computer name cannot be converted to an integer.

After reading on http://msdn2.microsoft.com/en-us/library/ms152478.aspx I have found that it has been recommend specifying CONVERT(nchar,AuditID) = HOST_NAME() in the wizard and then use sp_changemergearticle to change the clause to AuditID = CONVERT(int,HOST_NAME()) before creating a snapshot for the publication. Therefore I tried to run the following sql inside SQL Server, but it gives me the same error.

DECLARE @.publication AS sysname;
DECLARE @.article AS sysname;
SET @.publication = N'HASSystem';
SET @.article = N'Audit';

USE [HASSystem]

EXEC sp_changemergearticle
@.publication = @.publication,
@.article = @.article,
@.property = N'subset_filterclause',
@.value = N'AuditorID = CONVERT(int,HOST_NAME())',
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1;
GO

Conversion failed when converting the nvarchar value 'MSSWS02' to data type int.

Anyone knows whats wrong with it ? Is there anything i m missing?

2) The second problem I am having is that I added a dynamic filter to my publication

SELECT <published_columns> FROM [dbo].[Audit] WHERE CONVERT(nchar,AuditorID) = HOST_NAME()

which works fine but later on I decided to drop this dynamic filter and reinitialise the subscriptions and creating a new snapshot. But after deleting the dynamic filter and when snapshot agent tries to create a new snapshot, it gives me the following error.

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

Anyone knows whats wrong with it? How can i redefine my publication so that it doesn't use dynamic filters?

Regards
Nabeel Farid

I wouldnt set the dynamic filters with conversions, there could be other issues down the road.

To get around the problem of

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

use sp_changemergepublication and set dynamic_filters='false'

Also make sure your articles' filter clause are nulled out too.

|||

thanx for the help Mahesh... As for the conversion issue, what would you recommend then ? I am doing replication between SQL Mobile and SQL Server 2005. I want to filter out data based on AuditorID in a table on PDA, which is an integer. The SqlReplication class exposes a HostName property which is a string and is the only parameter that i can use as far as my knowledge is concerned.

In articles in the publication i recieve that AuditorID in HOST_NAME() which is a string as well. That is why in dynamic filters I convert HOST_NAME() to int or the UserId column to string (viceversa) as follows

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

Is there any way I don't have to use conversion? and also if i can pass more parameters from the PDA ?

Regards

|||

I am not totally against the conversion. It works in some cases and it doesnt in others. I just did not want you to take this road and face some issues down the road.

You could add a new column which is string and use this for filtering. the values in this column would correspond to the ones in the AuditorID. If you cannot, then continue as per the recommendation and we can troubleshoot any problems you would face.

|||

Thanx for the help Mahesh.

I have another question for you. One of our client has asked us how is the data secured over the air during Replication?

I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.

We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and using GPRS connection, they all synchronise with a 2 Publications SQL Server 2005. Both publications are of the same database and resides on the same server. We haven't got any SSL certificates installed on the server.

Now how can i make use of TLS in my application to secure my data over the air (using TLS)?

Regards,

|||

Replication does not provide anything out of the box for secure transmission or encryption. It relies on the SQL server protocols to do that.

You can use the certificates and SSL for encrypting data sent to/from SQL Server.

Take a look at this article and see if that helps you. http://msdn2.microsoft.com/en-us/library/ms189067.aspx

|||I would still like to know how to do the conversion. Using sp_changemergearticle still generates the same "cannot convert" error message that the New Publication Wizard generates.

What is the workaround? I would really like to use a UserID (int) in the filter clause.|||
I had the same problem and i coudn't figure out how to convert the hostname() either though wizard or through the sp_changemergearticle ... it gives the same error....therefore I had to convert the column to string to make it work instead of Hostname...(which is not a recommended practice)... and u might face problems with that later on...depends|||I agree that there are a number of messy workarounds. However, in the article you cited early in this thread (concerning Parameterized Filters) there is a large, bolded note about using sp_changemergearticle.

If this is out of date and no longer works for security/whatever reasons, it'd be nice to hear from an MS dev/test. However, if that note in the article still applies but with some restrictions (i.e. some system parameters need to be set initially), I'd like to know that as well.

Replication Issue with Dynamic Filter using HOST_NAME()

Dear ppl,

I am having a couple of problems with using dynamic filters in Merge Replication (SQL Server 2005 - SQL Mobile)

1) I am trying to add a dynamic filter to my publication using the wizard as follows:

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

This gives me an error Conversion failed when converting the nvarchar value 'MSSWS02' to data type int. because my computer name cannot be converted to an integer.

After reading on http://msdn2.microsoft.com/en-us/library/ms152478.aspx I have found that it has been recommend specifying CONVERT(nchar,AuditID) = HOST_NAME() in the wizard and then use sp_changemergearticle to change the clause to AuditID = CONVERT(int,HOST_NAME()) before creating a snapshot for the publication. Therefore I tried to run the following sql inside SQL Server, but it gives me the same error.

DECLARE @.publication AS sysname;
DECLARE @.article AS sysname;
SET @.publication = N'HASSystem';
SET @.article = N'Audit';

USE [HASSystem]

EXEC sp_changemergearticle
@.publication = @.publication,
@.article = @.article,
@.property = N'subset_filterclause',
@.value = N'AuditorID = CONVERT(int,HOST_NAME())',
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1;
GO

Conversion failed when converting the nvarchar value 'MSSWS02' to data type int.

Anyone knows whats wrong with it ? Is there anything i m missing?

2) The second problem I am having is that I added a dynamic filter to my publication

SELECT <published_columns> FROM [dbo].[Audit] WHERE CONVERT(nchar,AuditorID) = HOST_NAME()

which works fine but later on I decided to drop this dynamic filter and reinitialise the subscriptions and creating a new snapshot. But after deleting the dynamic filter and when snapshot agent tries to create a new snapshot, it gives me the following error.

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

Anyone knows whats wrong with it? How can i redefine my publication so that it doesn't use dynamic filters?

Regards
Nabeel Farid

I wouldnt set the dynamic filters with conversions, there could be other issues down the road.

To get around the problem of

The publication "HASSystem" was defined as having dynamic filters, but it does not contain any dynamic filters.

use sp_changemergepublication and set dynamic_filters='false'

Also make sure your articles' filter clause are nulled out too.

|||

thanx for the help Mahesh... As for the conversion issue, what would you recommend then ? I am doing replication between SQL Mobile and SQL Server 2005. I want to filter out data based on AuditorID in a table on PDA, which is an integer. The SqlReplication class exposes a HostName property which is a string and is the only parameter that i can use as far as my knowledge is concerned.

In articles in the publication i recieve that AuditorID in HOST_NAME() which is a string as well. That is why in dynamic filters I convert HOST_NAME() to int or the UserId column to string (viceversa) as follows

SELECT <published_columns> FROM [dbo].[Audit] WHERE AuditorID = CONVERT(int,HOST_NAME())

Is there any way I don't have to use conversion? and also if i can pass more parameters from the PDA ?

Regards

|||

I am not totally against the conversion. It works in some cases and it doesnt in others. I just did not want you to take this road and face some issues down the road.

You could add a new column which is string and use this for filtering. the values in this column would correspond to the ones in the AuditorID. If you cannot, then continue as per the recommendation and we can troubleshoot any problems you would face.

|||

Thanx for the help Mahesh.

I have another question for you. One of our client has asked us how is the data secured over the air during Replication?

I have read that Merge Replication uses TLS (Transport Layer Security) protocol to secure the data over the air. But I was wondering if it is all done automatically ? Or do we need to install certificates like for SSL.

We have a Windows Mobile 5.0 application using SQL Mobile that runs over 50 devices and using GPRS connection, they all synchronise with a 2 Publications SQL Server 2005. Both publications are of the same database and resides on the same server. We haven't got any SSL certificates installed on the server.

Now how can i make use of TLS in my application to secure my data over the air (using TLS)?

Regards,

|||

Replication does not provide anything out of the box for secure transmission or encryption. It relies on the SQL server protocols to do that.

You can use the certificates and SSL for encrypting data sent to/from SQL Server.

Take a look at this article and see if that helps you. http://msdn2.microsoft.com/en-us/library/ms189067.aspx

|||I would still like to know how to do the conversion. Using sp_changemergearticle still generates the same "cannot convert" error message that the New Publication Wizard generates.

What is the workaround? I would really like to use a UserID (int) in the filter clause.|||
I had the same problem and i coudn't figure out how to convert the hostname() either though wizard or through the sp_changemergearticle ... it gives the same error....therefore I had to convert the column to string to make it work instead of Hostname...(which is not a recommended practice)... and u might face problems with that later on...depends|||I agree that there are a number of messy workarounds. However, in the article you cited early in this thread (concerning Parameterized Filters) there is a large, bolded note about using sp_changemergearticle.

If this is out of date and no longer works for security/whatever reasons, it'd be nice to hear from an MS dev/test. However, if that note in the article still applies but with some restrictions (i.e. some system parameters need to be set initially), I'd like to know that as well.

Replication Issue

When Trying to Replicate I got an issue with the Snapshot Agent.

Please be kind enough to help me I am developing a system using SQL server Mobile for a Construction Site. This is for my final Project in Bachelors Degree.

Please reffer the Error that I got. bellow.

Creating Publication

- Creating Publication 'SQLMobile' (Success)

· SQL Server created publication 'SQLMobile'.

- Adding article 3 of 3 (Success)

· Article 'FlightData' was added.

· Article 'MembershipData' was added.

· Article 'sysdiagrams' was added.

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent.(New Publication Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantStartSnapshotAgent&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQLServerAgent is not currently running so it cannot be notified of this action.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 22022)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

Thanks

Mark.

The error is:

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent. (New Publication Wizard)

You need to see why the snapshot agent could not be started. You can start by drilling down into the history of the snapshot agent in Replication Monitor, you can also query the distribution database for table MSsnapshot_history.