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.
No comments:
Post a Comment