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 in connecting two sqlserver

Hi,..Please help me..

We have two server on pulbilc ip A , B in other country, and there replication will working fine. But i want to replicate data from one online server(A) to my local office(in my country) server(C). means from public ip to private(A->C). then some one told me that you have to put both server on public for replication. after the i put my local server(c) on public ip. but now i am facing the problem that i am able to connect C to A(means my office server to other country server) but not able to connect A to C(means other country server to my office server). when i am connecting from A to C then error shows is "defaut sqlserver server does not allow remote connection". I have change all the sql settings..and also done required changes in firewall but not successed. my operating system 2003 and database sql2005........ please help me................ASAP

thanks and regards

Vipin yadav

Can you check the following on machine C (aka: your office server),

Open "SQL Server Configuration Manager",

- expand the "SQL Server 2005 Network Configuration" node

- select "Protocols for MSSQLSERVER"

- enable "TCP/IP" if it's not already enabled and restart MSSQLSERVER service

See if that would solve your problem.

Regards,

Gary

|||He might want to have a look on

http://www.sqlserver2005.de/Screencast/Screencast.aspx?ScreencastId=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I have already done this.. still not connecting|||Check http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx for troubleshooting connectivity issues with SQL Server 2005. Also, run SQL Server Surface Area Configuration. Click Surface Area Configuration for Services and Connections and check the Remote Connections option. Make sure you have Local and remote connections option selected

Problem in configuring Replication

Hi to all!
i m tryiing to establish replication between two servers but when i
start to configure replication it gives me a message "SQL Server agent"
on "Computer Name" currently using system account,which causes
replication between servers to fail" . which account i should use to
configure it.
Thanx
Farid.
*** Sent via Developersdex http://www.codecomments.com ***
Farid,
the simplest way is to use the same account for the publisher and
subscriber's sql server service and sql agent service. Use a domain account
that is in the local admin group of each server. This is not very
sophisticated, but it should get you going.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Problem in Adding column in table

Dear Friends
I am getting the following problem while adding a column
in the replication table.
Please suggest.
Problem:
Code:
sp_repladdcolumn [ @.source_object = ] 'OTHALLOCATION'
, [ @.column = ] 'CCICAPPNUM' ]
[ , [ @.typetext = ] 'NVARCHAR(50)' ]
[ , [ @.publication_to_add = ] 'GEMREPLGNETINFOTECH' ]
[ , [ @.schema_change_script = ] 'NVARCHAR(1000)' ]
[ , [ @.force_invalidate_snapshot = ]
force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ]
force_reinit_subscription ]
Your earlier reply would be a great help.
Best regards
Sharad
Did you forget to paste the error message?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:06dd01c48b52$703f6b00$a501280a@.phx.gbl...
Dear Friends
I am getting the following problem while adding a column
in the replication table.
Please suggest.
Problem:
Code:
sp_repladdcolumn [ @.source_object = ] 'OTHALLOCATION'
, [ @.column = ] 'CCICAPPNUM' ]
[ , [ @.typetext = ] 'NVARCHAR(50)' ]
[ , [ @.publication_to_add = ] 'GEMREPLGNETINFOTECH' ]
[ , [ @.schema_change_script = ] 'NVARCHAR(1000)' ]
[ , [ @.force_invalidate_snapshot = ]
force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ]
force_reinit_subscription ]
Your earlier reply would be a great help.
Best regards
Sharad
sql

problem in - replication

Two Servers,
1st 2nd
database a --> after replication a'
b' <-- after replication b
on 1st server a is replicated to 2nd
on 2nd server b is replicated to 1st
I tried with merge replication it is working fine.But if i tried to use
transactional replication it give some error like "access
violation".Using tansactional replication is it possible.plz help me to
slove this problem.
Regards,
Senthil prabu R
When you used so_addsubscription, did you set @.loopback_detection=true?
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
"senthilprabu" <senthilpraburaju@.gmail.com> wrote in message
news:1125118308.314373.138490@.f14g2000cwb.googlegr oups.com...
> Two Servers,
> 1st 2nd
> database a --> after replication a'
> b' <-- after replication b
> on 1st server a is replicated to 2nd
> on 2nd server b is replicated to 1st
> I tried with merge replication it is working fine.But if i tried to use
> transactional replication it give some error like "access
> violation".Using tansactional replication is it possible.plz help me to
> slove this problem.
> Regards,
> Senthil prabu R
>

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 deleting publication

Hi All,

I have a problem, a user deleted some tables from a couple db's dealing
with replication. I now get this error on those db's.

The process could not execute 'sp_repldone/sp_replcounters on
servername

I have deleted one of the database's and neither are listed under
publications but they still show up under replication monitor.

Any ideas on how to delete them would be great. When I try I get

Error 208: Invalid object name 'sysarticles'
Invalid object name 'sysschemaarticles'
Could not use view or function 'sysextendedarticlesview' because of
binding errors.

TIA
DaveI figured it out, hope this helps someone.

I went into the distribution db and went thru every table and removed
any reference to the databases that were giving me problems. That got
rid of the ghost entries in the replication monitor folder. Oddly I
still had the red X's on the folders them selves but nothing under the
folders had X's and a refresh didnt make them go away. A
sp_MSload_replication_status did though.

Now just the database of the two that were needed still had the repl
hand icon on the database. The good old sp_removedbreplication got rid
of that.

Thanks,
Dave

Problem deleting

I have a sick subscriber in a transactional replication.
I want to delete it but I don't succeed.
When I try from Tools\Replication\Create and manage publications Entreprise Manager crush.
I try with
exec sp_dropsubscription @.publication = N'PublicationName', @.article = N'all', @.subscriber = N'SubscriberName', @.destination_db = N'DatabaseName' but execution take a long, long time ..without any end.
I don't want to delete all replication.
Any ideeas?
10x a lot!Whats the size of Subscriber?
Check for any other errors from SQL error log.|||In the end I deleted all replication and recreated it. Now it's working.

problem creating trans. replication /sp_MShelp_replication_table

I'm using the wizard to create a trans. replication. When it tries to
retrieve the tables to publish
I get this error:
New Publication Wizard encountered one or more errors while retrieving
the list of articles on 'production'. The list of articles may not be
complete.
Invalid column name 'options'. (Microsoft SQL Server, Error: 207)
The details of the error refer to sp_MShelp_replication_table.
When I run that stored proc I get:
Msg 207, Level 16, State 1, Procedure sp_MShelp_replication_table, Line
59
Invalid column name 'options'.
What could be the issue?
-Phil
I found the problem. The database was originally a SQL2000 db that was
being replicated. It still had some tables like sysarticles. I
dropped those tables and it's fine now.
|||The reserved replication keywords are :
If your publishing table has column names reserved for merge processing, you
will not be able to generate an initial snapshot because of duplicate column
names. Reserved column names are:
a.. reason_code
b.. source_object
c.. reason_text
d.. Pubid
e.. conflict_type
f.. origin_datasource
g.. tablenick
h.. create_time
Option is a reserved sql keyword. Could you post the schema for this table
here?
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
"Phil Cruz" <philcruz@.gmail.com> wrote in message
news:1144171822.881370.203540@.z34g2000cwc.googlegr oups.com...
> I'm using the wizard to create a trans. replication. When it tries to
> retrieve the tables to publish
> I get this error:
> New Publication Wizard encountered one or more errors while retrieving
> the list of articles on 'production'. The list of articles may not be
> complete.
> Invalid column name 'options'. (Microsoft SQL Server, Error: 207)
> The details of the error refer to sp_MShelp_replication_table.
> When I run that stored proc I get:
> Msg 207, Level 16, State 1, Procedure sp_MShelp_replication_table, Line
> 59
> Invalid column name 'options'.
>
> What could be the issue?
> -Phil
>