Problem in IDENTITY COLUMNS

Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?Originally posted by TALAT
Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?

You will have to find the max value and do something like this.

DBCC CHECKIDENT('table',RESEED,@.max_value)|||Howdy!
Running: DBCC CHECKIDENT('table', RESEED). when i run it second time for the table; the identity value gets normal, i.e. it gets the same as the last value in the column. But it's rather painful to run it for each table. I never had this problem at the publisher it's only at the subscriber. Is there a permanent solution?

Thanx for the reply.

Problem dropping replication support

I am working on establishing a merge replication process between SQL Server 2005 and SQL Mobile 2005. I started with a new SQL Server 2005 instance and went through the Sample for SQL Mobile merge replication. So far so good.

Today, I tried to drop all support for replication on my current SQL 2005 test instance, so that I can start from a fresh instance and do another. I used Management Studio to drop all publisher and distribution settings and had several errors occur, where some roles were not allowed to be dropped because they had membership in them.

I dropped all the users that I added to the SQL logins and tried again.

Now I am trying to run the following script:

use AdventureWorks
exec sp_replicationdboption @.dbname = N'AdventureWorks', @.optname = N'merge publish', @.value = N'false', @.ignore_distributor = 'true'

-- Dropping the distribution publishers
exec sp_dropdistpublisher @.publisher = N'XP-MIKED-LAPTOP'
GO

-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @.database = N'distribution'
GO

/****** Uninstalling the server XP-MIKED-LAPTOP as a Distributor. Script Date: 1/14/2006 2:16:29 PM ******/

use master
exec sp_dropdistributor @.no_checks = 1, @.ignore_distributor = 1
GO

The error I am getting from the first batch (sp_replicationdboption) is this:

Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.

To me it looks like all the publication objects have been already removed from AdventureWorks, but in sysdatabases, the category column still says 4 (merge publication). Since I can't just do this anymore:

UPDATE MASTER.DBO.SYSDATABASES
set category = 0
where dbid=8

I just don't know what I can do at this point. I can't even create a new publication in AdventureWorks because it thinks there is a sysmergepublications table in there and fails when there isn't.

try sp_removedbreplication.

|||

Greg Yvkoff wrote:

try sp_removedbreplication.

Yes, that was exactly it. I got to the end of the KB article 324401 and found that stored proc. It worked like a charm.

Problem dropping a column from a table in a publication

Hi,
I create a column in a table that is in a merge publication with this code
USE JMI
GO
sp_repladdcolumn @.source_object='ztest',@.column='dateDepart',@.typet ext='
datetime NULL DEFAULT getDate()' ,@.publication_to_add='JMI_articles'
GO
I realise that a made a mistake and i want to drop the column
So i try this code
USE JMI
GO
sp_repldropcolumn @.source_object='ztest',@.column='dateDepart'
GO
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'JMI_articles' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ztest__dateDepar__66D9612E' is dependent on column
'dateDepart'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN dateDepart failed because one or more objects access
this column.
It is possible to drop a column from a replication ?
Yes, it is possible, however in this case it looks like other tables are
dependent on this column, so you it looks like you have to drop the
constraint before you do this.
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
"GC" <GC@.discussions.microsoft.com> wrote in message
news:DEFC493F-23E9-40C3-816D-6ADFE710581C@.microsoft.com...
> Hi,
> I create a column in a table that is in a merge publication with this code
> USE JMI
> GO
> sp_repladdcolumn @.source_object='ztest',@.column='dateDepart',@.typet ext='
> datetime NULL DEFAULT getDate()' ,@.publication_to_add='JMI_articles'
> GO
> I realise that a made a mistake and i want to drop the column
> So i try this code
> USE JMI
> GO
> sp_repldropcolumn @.source_object='ztest',@.column='dateDepart'
> GO
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'JMI_articles' because schema replication is performed.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ztest__dateDepar__66D9612E' is dependent on column
> 'dateDepart'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN dateDepart failed because one or more objects
> access
> this column.
>
> It is possible to drop a column from a replication ?
>
>
>
>

Problem downloading the snapshot agent in a merge pull subscription.

Hello.

I've been having an error when downloading the snapshot agent from our Publisher.

The articles selected are all the tables and all the stored procedures.

The Subscription is created programmatically and so is the synchronization. When trying to synchronize for the first time and when the subscriber tries to download the snapshot I have the following error:

---

Error messages:
The schema script 'Distrito_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
The process could not read file 'GESZif\unc\AFRODITE$SILVITEST_GESZIF_GESZIF\20070621182845\Distrito_2.sch' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20143)
Get help: http://help/MSSQL_REPL20143
The system cannot find the path specified.
(Source: MSSQL_REPL, Error number: MSSQL_REPL3)
Get help: http://help/MSSQL_REPL3

---

The snapshot folder is at c:\GESZifSnapshotFolder and the unc is \\Afrodite\Snapshot.

I'm getting a little desperate with this because we really need to propagate the schema changes around the subscribers.

Thanks in advance,

AVD

Has this ever worked before? Does agent account have the read access to your snapshot folder?

Gary

|||Hello Gary, thank you for your reply.

No, this has never worked before. When I was studying a way to implement replication I mostly used the management studio and management studio express. I created a database with backup/restore and put it at the client, so I disabled the initial snapshot when the error ocurred for the first time.

When you refer the agent account I suppose you mean the one at the Publication server (since SQLExpress doesn't have the SQL Agent)? Yes, it has full rights over the directory.

Best regards,

AVD
|||

Gary Chen wrote:

Has this ever worked before? Does agent account have the read access to your snapshot folder?

Gary

Ok, Gary i took your advice and found out that the permissions must be set for the UNC share and not the directory security.

After that it all worked wonders. Thanks for the help.