Replication Issue with ERROR 20026 publication '%s' does not exist

We have replication between two Microsoft SQL2000 servers. We took
down replication (had no problem) then backed up the database (no
problem). Tried to make some changes but had to delete the publication
first and then we ran into some other issuse (not a big problem). So
we decided to restore the database from the backup. the issue came
about because the publication has been deleted but the restored
database still has it listed. Bacause of this we cannot make any
changes to the database, further more if we try to delete the
publication it wont let us because it doesnt exist. So we are kind
caught in a bind (This is the main issue).
can you drop the publications using sp_droppublication?
Follow these instructions - http://support.microsoft.com/kb/324401
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
<ebade2000@.gmail.com> wrote in message
news:1174060376.222600.269530@.p15g2000hsd.googlegr oups.com...
> We have replication between two Microsoft SQL2000 servers. We took
> down replication (had no problem) then backed up the database (no
> problem). Tried to make some changes but had to delete the publication
> first and then we ran into some other issuse (not a big problem). So
> we decided to restore the database from the backup. the issue came
> about because the publication has been deleted but the restored
> database still has it listed. Bacause of this we cannot make any
> changes to the database, further more if we try to delete the
> publication it wont let us because it doesnt exist. So we are kind
> caught in a bind (This is the main issue).
>
|||Fixed it yeeeha. Thanks for your help Hilary what ended up working for
me was "sp_removedbreplication". The other stored procedures will work
if the publication pr replication existed (which was not my case)

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 With Cisco VPN Client

If anyone has run across this before, I'm having the same
issue with the same components.
If I figure anything out, I'll post findings here.
Thanks,
David
>--Original Message--
>Morning Guys,
>I really need you help, I am just about to seriously
pull my hair out. Here
>is the situation.
>I have 1 server running SQL Server 2000 sp3a with merge
replication setup,
>and 150 users on laptops running MSDE 2000 with the
latest service pack and
>dialling up to the server via a 56k modem and using a
Cisco VPN Client to
>gain access.
>If I communicate on the network, all the replication
works fine, if I
>communicate on a ADSL Line all the communicates is fine,
if I dialup,
>communication will just fail, it will process the
articles and then fail. I
>really need some help here. I have adjusted the MTU rate
on the server,
>Cisco router and the client. but I just can't get this
to work correctly and
>I have this working in many other companies.
>The dial up users are getting a good connection with 50k
>If you need anymore information give me a shout.
>Thanks Tim.
>
>.
>
I believe Paul Ibison has seen problems with this. If he doesn't chime in
here, contact him offline.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"David England" <anonymous@.discussions.microsoft.com> wrote in message
news:40e101c4900f$40544430$a301280a@.phx.gbl...[vbcol=seagreen]
> If anyone has run across this before, I'm having the same
> issue with the same components.
> If I figure anything out, I'll post findings here.
> Thanks,
> David
> pull my hair out. Here
> replication setup,
> latest service pack and
> Cisco VPN Client to
> works fine, if I
> if I dialup,
> articles and then fail. I
> on the server,
> to work correctly and

Replication Issue With Cisco VPN Client

Morning Guys,
I really need you help, I am just about to seriously pull my hair out. Here
is the situation.
I have 1 server running SQL Server 2000 sp3a with merge replication setup,
and 150 users on laptops running MSDE 2000 with the latest service pack and
dialling up to the server via a 56k modem and using a Cisco VPN Client to
gain access.
If I communicate on the network, all the replication works fine, if I
communicate on a ADSL Line all the communicates is fine, if I dialup,
communication will just fail, it will process the articles and then fail. I
really need some help here. I have adjusted the MTU rate on the server,
Cisco router and the client. but I just can't get this to work correctly and
I have this working in many other companies.
The dial up users are getting a good connection with 50k
If you need anymore information give me a shout.
Thanks Tim.
Tim,
have you tried following the recommendations in this article:
http://support.microsoft.com/default...49&Product=sql
It refers to a transactional setup but if you substitute 'merge agent' for
'distribution agent' it should apply eqally.
HTH,
Paul Ibison

Replication Issue Syntax error or access violation

I am replication on a SQL Server 2000 env on Windows Server 2003. I randomly
get an access violation error stating "Syntax error or access violation" ODBC
error 37000. After further investigation using sp_browsereplcmds I see that
a blank field in the db is sending the following repeated about 30 times.
''+{fn CHAR(0)}+''+{fn CHAR(0)}
I am using sql server generated procs. I also have 4 other publishers using
the same table without an errors. I am looking for a solution rather than a
work around. Any ideas?
Thanks,
TK
This looks like a bug. Can you call PSS on it.
If you are looking for a work around, study the transaction which caused
this sequence of commands. Sometimes if you set columns to "", you can get
some weird occurences. Most of these problems have been patched however.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"TK" <TK@.discussions.microsoft.com> wrote in message
news:7DAEE7FE-2CA7-495B-8E8D-4B65EB40BA30@.microsoft.com...
>I am replication on a SQL Server 2000 env on Windows Server 2003. I
>randomly
> get an access violation error stating "Syntax error or access violation"
> ODBC
> error 37000. After further investigation using sp_browsereplcmds I see
> that
> a blank field in the db is sending the following repeated about 30 times.
> ''+{fn CHAR(0)}+''+{fn CHAR(0)}
> I am using sql server generated procs. I also have 4 other publishers
> using
> the same table without an errors. I am looking for a solution rather than
> a
> work around. Any ideas?
> --
> Thanks,
> TK