I'm trying to set up replication on a Db that I just took over. When
replicating the SProcs the Distribution Agent stops with an error on the
Subscriber whenever it encounters an SP that is building a dynamic SQL
statement. I typically get the following error:
are not allowed. Add a name or single space as the alias name.
(Source: EVESTMENTDB6 (Data source); Error number: 1038)
There is nothing before the "are not allowed." Lookinig for error 1038 has
so far been fruitless. I have been rewriting the Dynamic SQl but I know that
at least some of the SProc's are really going to need it.
Jay Croft
Senior Systems Architect
Can you post one of the problem procs here?
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
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:654660AB-4A31-4275-BD3E-A432D815B7E4@.microsoft.com...
> I'm trying to set up replication on a Db that I just took over. When
> replicating the SProcs the Distribution Agent stops with an error on the
> Subscriber whenever it encounters an SP that is building a dynamic SQL
> statement. I typically get the following error:
> are not allowed. Add a name or single space as the alias name.
> (Source: EVESTMENTDB6 (Data source); Error number: 1038)
> There is nothing before the "are not allowed." Lookinig for error 1038
> has
> so far been fruitless. I have been rewriting the Dynamic SQl but I know
> that
> at least some of the SProc's are really going to need it.
> --
> Jay Croft
> Senior Systems Architect
|||These SP's were written by an untrained junior programmer, but they've been
working in a production DB for two months.
/****** Object: StoredProcedure
[dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date: 02/08/2006
18:14:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE procedure [dbo].[eaSP_analytics_select_benchmark_perf_data]
@.benchmark_type varchar(20),
@.view_name varchar(40),
@.risk_benchmark_id int,
@.earliest_date datetime,
@.latest_date datetime,
@.qorm char(1)
as
begin
SET NOCOUNT ON
declare @.strSQL varchar(1000)
if @.benchmark_type = ''Index''
Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
year, value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
if @.benchmark_type = ''DB Product''
Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
product_category_code, vehicle_type, gross_or_net, base_currency, product_id
as benchmark_id, product_name, return_date, month, year, product_performance
as value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
product_performance is not null "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
SET NOCOUNT OFF
end
'
END
GO
Jay Croft
Senior Systems Architect
"Hilary Cotter" wrote:
> Can you post one of the problem procs here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hi Jay,
Replication of text objects that use " instead of ' for delimiting string
literal is not supported (or simply broken) in SQL2000 although the SQL2005
snapshot agent will automatically convert these "s into 's. If there are not
many procedures that use " for delimiting string literals, you may want to
consider rewriting them to use ' instead. Otherwise you would need to use
the Database Import\Export wizard to transfer these stored procedures
instead.
HTH
-Raymond
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:0D89C509-557E-4DA1-A99F-560BD91688C7@.microsoft.com...
> These SP's were written by an untrained junior programmer, but they've
> been
> working in a production DB for two months.
> /****** Object: StoredProcedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date:
> 02/08/2006
> 18:14:39 ******/
> SET ANSI_NULLS OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
> OBJECTPROPERTY(id,N'IsProcedure') = 1)
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
>
>
> CREATE procedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data]
> @.benchmark_type varchar(20),
> @.view_name varchar(40),
> @.risk_benchmark_id int,
> @.earliest_date datetime,
> @.latest_date datetime,
> @.qorm char(1)
> as
> begin
> SET NOCOUNT ON
> declare @.strSQL varchar(1000)
> if @.benchmark_type = ''Index''
> Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
> year, value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> if @.benchmark_type = ''DB Product''
> Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
> product_category_code, vehicle_type, gross_or_net, base_currency,
> product_id
> as benchmark_id, product_name, return_date, month, year,
> product_performance
> as value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
> product_performance is not null "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> SET NOCOUNT OFF
> end
> '
> END
> GO
> --
> Jay Croft
> Senior Systems Architect
>
> "Hilary Cotter" wrote:
>
Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts
Friday, March 9, 2012
Saturday, February 25, 2012
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.
Tuesday, February 21, 2012
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.
Subscribe to:
Posts (Atom)