Replication of Dynamic SQL Stored Procedures

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:
>