Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!
Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegr oups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>
|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilarysql

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:
> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem (please help if you can)

Since our outsourced database team made some changes to the database we
are getting the following from the Distribution Agent Error Details:
{call sp_MSget_repl_commands(5, ?, 0, 7500000)}
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
(Source: INTRANETSVR (Data source); Error number: 2627)
----
--
The it's a transactional replica that has been running happily for some
time now, and I am only a db admin with very little experience in
trouble shooting.
thanks in advance.
Kev
kevintomlinson@.hotmail.comOn the destination database run
select object_name(79C50FD3)
That should give you the name of the table that's having the problem.
If you are pushing from the distributor (simplest to administer) then look
at the distribution agent - it should be showing an error.
If you restart it then it should give the same error. I think you can get he
command from one of the windows there but I usually use the profiler on the
subscriber, start the agent and the last instruction recorded will be the on
e
in error.
Then you have to find out why this is causing a problem.
Could be that the index on the destination is not correct and doesn't match
the source (just change it) ot the destination data could be out of step wit
h
the source.
If that's the case then you have a choice between changing the data on the
destination of re-snapshotting (just the table if possible - depends on your
settup).
"Kev" wrote:

> Since our outsourced database team made some changes to the database we
> are getting the following from the Distribution Agent Error Details:
>
> {call sp_MSget_repl_commands(5, ?, 0, 7500000)}
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> (Source: INTRANETSVR (Data source); Error number: 2627)
> ----
--
> The it's a transactional replica that has been running happily for some
> time now, and I am only a db admin with very little experience in
> trouble shooting.
> thanks in advance.
> Kev
> kevintomlinson@.hotmail.com
>

Replication on SQL Server

Hi again,

I have a problem with replication on SQLServer 2000. I've started "Configure
Publishing and Distribution Wizard" and SQLSrvr posted message "SQLServer
Enterprise Manager colud not configure 'DANILOPOPOVIC' as the Distributor
for 'DANILOPOPOVIC'. Error 14234: The specified '@.Server' is invalid (valid
values are returned by sp_helpserver'. I didn't type server name "@.Server",
because I've used wizard.What is a problem?

Thank you in advance,

Danilo Popovi, B. Sc.
Trgostil d.d. - IC
Toplika 16
49240 Donja Stubica
CroatiaHi again,

I've tried the same thing on another server TRGO2000 and SQLSrvr posted
message "SQLServer Enterprise Manager colud not configure 'TRGO2000' as the
Distributor for 'TRGO2000'. Error 14114: '(null)' is not configured as a
Distributor. '.

Danilo Popovi, B. Sc.
Trgostil d.d. - IC
Toplika 16
49240 Donja Stubica
Croatia|||I had a similar problem using the Wizards to set replication with the
Version 7 of EM. It was because the servers were registered in the EM with a
different name to the Actual Server Name. So I would check that the Servers
are registered with the correct Server/Instance Name.

Hope this Helps

Cheers
Dean

"Danilo Popovi" <adf@.trgostil.hr> wrote in message
news:bku3va$6jj$1@.sunce.iskon.hr...
> Hi again,
> I have a problem with replication on SQLServer 2000. I've started
"Configure
> Publishing and Distribution Wizard" and SQLSrvr posted message "SQLServer
> Enterprise Manager colud not configure 'DANILOPOPOVIC' as the Distributor
> for 'DANILOPOPOVIC'. Error 14234: The specified '@.Server' is invalid
(valid
> values are returned by sp_helpserver'. I didn't type server name
"@.Server",
> because I've used wizard.What is a problem?
> Thank you in advance,
> Danilo Popovi, B. Sc.
> Trgostil d.d. - IC
> Toplika 16
> 49240 Donja Stubica
> Croatia
>
>

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