Replication triggers

Dear All,
the subscriber database contains no longer the (upd ,
ins , del) triggers of the replication.
How do i create them again , or do i need to drop the
subscription and do it all over again.
Please feedback urgently
Thanks in Advance
Dalia,
you should be able to script them out by creating a seaparate (fake)
subscription, and then apply them to the subscriber. I'd be a little
concerned that other details may be missing though and be tempted to
reinitialize. Also, I suspect that the excahnge type value is incorrect
which has caused this issue.
HTH,
Paul Ibison
|||I urge you to re-initialize your merge subscription(s), rerun your snapshot, and deploy it by rerunning your merge agent.
sql

Replication question

Dear Friends,
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Replication Question

Dear All,
After marked re-initialize an existing publication, a new snapshot has been
generated and applied to the subsciber, after that, there is an error on
Distributor agent saying that can't apply command as duplicate key error. I
have checked those insert commands and those records are already exists on
subsciber as new snapshot has been applied.
What I can do to resolve the problem? Do Distributor agent is smart enought
to ignore those old not yet replicated transactions which is older than my
latest snapshot being applied? What is the correct action sequence for
re-initialization?
Yours sincerely,
Henry
Henry,
see if this applies:
http://support.microsoft.com/default...b;en-us;813494
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Henry,
also, have a look at the post 'Subscription Choking' from yesterday, as
there might need to be an additional step if the Hotfix is applied:
http://groups-beta.google.com/group/...8?dmode=source
Rgds,
Paul Ibison
sql

Replication question

Dear Sir/Madam,
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
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

Replication Query

Dear friends

I want to know one thing in replication.I try to apply all three types of replication.in all these whenever I start initilization it is trying to drop & create database objects.I only want to only replicate data.Most of the times i am getting error like this' this particular table//view is not able to drop it's in replication'.If i am selecting no trnsfer of data & schema.Then it's giving error like that 'snapshot sequence is not correct' when synchronization.So please tell me some solution for this
Thanks in Advance
FilsonHai friend

I hope this link helpfull for u
http://docs.openlinksw.com/virtuoso/SNAPSHOT.html

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 problem - 1024 column

Dear Friends
I am facing the problem in my accounting database
replication there is one table with 1024 column and hence
the replication is failing as the maximum size is 1024
and the rowguide column can not be created.
Please guide if there is any way out to do the replicaion
with 1025 column in the table.
I can not do anything in this table as the applicaion is
third party. ( Can not delete any column)
Your earlier reply would be a great help.
Best regards
Sharad
Hi
SQL Server does not allow more than 1024 columns for a Table.
A system with 1024 columns...especially accounting. I hope you got it for
free and are looking at replacing it soon. No DBA who knows what he is doing
would design such a table. It is totally un-manageable, and with a
limitation of 8k per row, if every column were populated, each column could
be only 8 bytes.
There is no solution for you as you have hit a system limitation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sharad" <Niitmalad@.yahoo.co.in> wrote in message
news:2a6201c4eb7e$811d3c40$a401280a@.phx.gbl...
> Dear Friends
> I am facing the problem in my accounting database
> replication there is one table with 1024 column and hence
> the replication is failing as the maximum size is 1024
> and the rowguide column can not be created.
> Please guide if there is any way out to do the replicaion
> with 1025 column in the table.
> I can not do anything in this table as the applicaion is
> third party. ( Can not delete any column)
> Your earlier reply would be a great help.
> Best regards
> Sharad
>
|||Hello Sharad,
what about splitting of your article (table) into two publications?
Greetings from Cologne
Thomas
"Sharad" wrote:

> Dear Friends
> I am facing the problem in my accounting database
> replication there is one table with 1024 column and hence
> the replication is failing as the maximum size is 1024
> and the rowguide column can not be created.
> Please guide if there is any way out to do the replicaion
> with 1025 column in the table.
> I can not do anything in this table as the applicaion is
> third party. ( Can not delete any column)
> Your earlier reply would be a great help.
> Best regards
> Sharad
>
>
|||obviously Mike has never heard of PeopleSoft.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:exm6Ea46EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi
> SQL Server does not allow more than 1024 columns for a Table.
> A system with 1024 columns...especially accounting. I hope you got it for
> free and are looking at replacing it soon. No DBA who knows what he is
doing
> would design such a table. It is totally un-manageable, and with a
> limitation of 8k per row, if every column were populated, each column
could
> be only 8 bytes.
> There is no solution for you as you have hit a system limitation.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Sharad" <Niitmalad@.yahoo.co.in> wrote in message
> news:2a6201c4eb7e$811d3c40$a401280a@.phx.gbl...
>

Replication probelm

Dear All,
I am currently reading the chapter of replication of MS SQL Server 2000
online book.
I just startup to read it.
I have some misunderstanding.
What is the different between Publisher and Distributor?
If I have a application, which database I should connected to? Publisher onr
or distributor one?
Or no matter how which I connected to?
Thanks~
Here's my idea
Application is connected to Publisher, and Publisher will make a copy of
data (publication) to Distributor.
And Distributor will use that data to copy to other Subscriber, or
Subscriber requests data from Distributor.
Am I right?
Thanks
|||If the application makes the changes that you want to replicate then you are
correct in connecting to the publisher. The Publisher and the distributor can
be on the same machine. How you described it in your second post is how it
works. As an example the replication setup that was used at my last company
was, one top level publisher under this there where 5 distributors that then
relayed any updates out to the subscribers.
"Utada P.W. SIU" wrote:

> Here's my idea
> Application is connected to Publisher, and Publisher will make a copy of
> data (publication) to Distributor.
> And Distributor will use that data to copy to other Subscriber, or
> Subscriber requests data from Distributor.
> Am I right?
> Thanks
>
>
|||Utada,
this arrangement, where data 'flows' through the distributor, is true of
'vanilla' snapshot and transactional replication. In each case it is
slightly different: in transactional replication, data modifications are
posted into the MSrepl_commands table in the distribution database, and in
snapshot replication, it is the location/names of the ODBCBCP snapshot files
that is posted into this table, while the files themselves are stored on a
share on the distributor. For merge replication the situation is different
and the distributor's job is to merely record metadata about replication -
dates of synchronization etc, but the data itself never flows through the
distributor - it remains on the publisher and subscribers.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks Russell~~
|||Thanks Paul~

Replication probelm

Dear All,
I am currently reading the chapter of replication of MS SQL Server 2000
online book.
I just startup to read it.
I have some misunderstanding.
What is the different between Publisher and Distributor?
If I have a application, which database I should connected to? Publisher onr
or distributor one?
Or no matter how which I connected to?
Thanks~Here's my idea
Application is connected to Publisher, and Publisher will make a copy of
data (publication) to Distributor.
And Distributor will use that data to copy to other Subscriber, or
Subscriber requests data from Distributor.
Am I right?
Thanks|||If the application makes the changes that you want to replicate then you are
correct in connecting to the publisher. The Publisher and the distributor can
be on the same machine. How you described it in your second post is how it
works. As an example the replication setup that was used at my last company
was, one top level publisher under this there where 5 distributors that then
relayed any updates out to the subscribers.
"Utada P.W. SIU" wrote:
> Here's my idea
> Application is connected to Publisher, and Publisher will make a copy of
> data (publication) to Distributor.
> And Distributor will use that data to copy to other Subscriber, or
> Subscriber requests data from Distributor.
> Am I right?
> Thanks
>
>|||Utada,
this arrangement, where data 'flows' through the distributor, is true of
'vanilla' snapshot and transactional replication. In each case it is
slightly different: in transactional replication, data modifications are
posted into the MSrepl_commands table in the distribution database, and in
snapshot replication, it is the location/names of the ODBCBCP snapshot files
that is posted into this table, while the files themselves are stored on a
share on the distributor. For merge replication the situation is different
and the distributor's job is to merely record metadata about replication -
dates of synchronization etc, but the data itself never flows through the
distributor - it remains on the publisher and subscribers.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||thanks Russell~~|||Thanks Paul~

Replication over different computers

Dear All,
I can successfully create merge replication on the same
computer with two database instances. However, it didn't
success if I replicate over two computers.
I use push subscription and the subscriber seems
nothing to set (set the replication on the publisher).
Therefore, how can I control the access rights or else to
replicate to another computer?
Patrick
Patrick,
the sql server agnt's login must be in the db_owner role on the subscriber
database, db_owner in the distribution database and db_owner of the
publishing database and have the correct rights to the snapshot share.
What is the error when you run the merge agent?
Regards,
Paul Ibison
|||Is there any step-by-step guide to setup replication over
different computers

>--Original Message--
>Patrick,
>the sql server agnt's login must be in the db_owner role
on the subscriber
>database, db_owner in the distribution database and
db_owner of the
>publishing database and have the correct rights to the
snapshot share.
>What is the error when you run the merge agent?
>Regards,
>Paul Ibison
>
>.
>
|||Patrick,
BOL has all the details but not as a setup guide.
These links have step-by-step graphical setups:
http://www.mssqlcity.com/Articles/Re...MR/SetupMR.htm
http://www.sqlservercentral.com/arti...?categoryid=26
HTH,
Paul Ibison

Replication ntext only by UPDATE

Dear group,
in the SQL-Server documentation it says:
"Merge replication supports the replication of text, ntext, and image
columns only if they have been updated explicitly by an UPDATE statement
because it causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers."
Is this also valid for INSERT-Statements?
In our case we have a table where only INSERTS of new rows occur (incl.1
ntext column). Can we replicate this by merge replication without problems?
Do you have a URL to 'prove' this? ;-)
thanks a lot,
regards,
ERic
I understand that you have to have a trigger that fires on insert, to update
the text column in the row again.
I haven't tried this myself.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Eric Voigt" <eric@.ericvoigt.de> wrote in message
news:2ubq8aF28taboU1@.uni-berlin.de...
> Dear group,
> in the SQL-Server documentation it says:
> "Merge replication supports the replication of text, ntext, and image
> columns only if they have been updated explicitly by an UPDATE statement
> because it causes a trigger to fire that updates meta data ensuring that
the
> transaction gets propagated to other Subscribers."
> Is this also valid for INSERT-Statements?
> In our case we have a table where only INSERTS of new rows occur (incl.1
> ntext column). Can we replicate this by merge replication without
problems?
> Do you have a URL to 'prove' this? ;-)
> thanks a lot,
> regards,
> ERic
>
>

Replication newbie, help please...

Dear all,
I have a web application that uses sql2005 database. And a local windows
application that uses exactly same sql2005 database structure. I want to
synchronize databases. When new record added from web I want them into my
local db. When my local db changes I want them to go my server database.
Is this "replication" concept for this kind operation. there are many
resources on the net but I could not figure out how.
Any sample documentation, link on tghe web, sample project, anything is
wellcome. but I need to synchronize 2 databases in different locations.
Best regards.
Have a look in BOL for setting up merge replication. The exact setup depends
on how the web application and the local windows application can connect -
over tcp/ip (trusted?) or if not https?
Rgds,
Paul Ibison

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

*** Apologies for the post in multiple newsgroup ***
Dear All
We have a publication on Server 1, using Transactional
Replication. Server 2 has the distribution database, and
the database we are replicating too.
We switched off the publication using EM Tools ->
Replication -> Disable Publication and Distribution, and
attempted to change some columns on Server 1, and recieved
a cannot change column as used in replication message.
I have tried a range of sql commands with no luck.
Can anyone sugest to me a way of going forward on this ?
Thanks
JThanks this has been solved.
>--Original Message--
>*** Apologies for the post in multiple newsgroup ***
>Dear All
>We have a publication on Server 1, using Transactional
>Replication. Server 2 has the distribution database, and
>the database we are replicating too.
>We switched off the publication using EM Tools ->
>Replication -> Disable Publication and Distribution, and
>attempted to change some columns on Server 1, and
recieved
>a cannot change column as used in replication message.
>I have tried a range of sql commands with no luck.
>Can anyone sugest to me a way of going forward on this ?
>Thanks
>J
>.
>