Re: SQL 2005 --> Replicate Production DB to Development DB

Hi Raymond,
We are using push replication so it looks like I will have to execute the
command by hand. But I am having problems with the 'DBCC CHECIDENT' command.
The command that I am running after the data is replicatated is the
following:

DBCC CHECIDENT('dbo.TcmLatestReport', RESEED)

I receive the following message:
Checking identity information: current identity value 'NULL', current column
value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

The table contains entries so I'm not sure why the command is not setting
the identiy seed to be the max value of the current value?

Thanks again for your help,
Terry





"Raymond Mak [MSFT]" wrote:

Hi Terry,

Unfortunately the only solution that I can think of is to manually run DBCC
CHECKIDENT on your subscriber tables with identity columns (you can simply
put that in a post-snapshot script) to reset the identity seed values to be
the max values of the identity columns. While it may be worthwhile for
replication to automatically do this for you, I can think of at least one
case where doing so is not desirable (rolling up data with different
identity ranges on a central subscriber). In any case, you may want to log a
feedback item about this @ http://connect.microsoft.com so we can consider
doing something about this in a future release.

-Raymond

"Terry Wahl" <TerryWahl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DAD169CE-0E70-40FB-97C5-51314DE515F0@xxxxxxxxxxxxxxxx
Replication of the tables is working but I am having an issue when adding
rows to the replicated tables. There seems to be a problem with dealing
with
idenity columns. For instance I have a table with three columns one of
which
is the primary key column. When a new row is added I think the system is
generating an already present value for the primary key. I am receiving a
duplicate key error. Is there a configuration setting that I need to set?

Thanks again for your help,
Terry


"Raymond Mak [MSFT]" wrote:

Hi Paul,

Actually SQL2005 snapshot processing for transactional\snapshot
replication
should be able to handle FKs that exist only on the subscriber reasonably
well. Consider the following .pre file generated from the snapshot agent
in
SQL2005 when the pre-creation command is 'drop':

SET QUOTED_IDENTIFIER ON



go



if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec
sys.sp_MSdropfkreferencingarticle @destination_object_name =
N'sometable',
@destination_owner_name = N'dbo'



go



drop Table [dbo].[sometable]

go

The magical line here is the call to sp_MSdropfkreferencingarticle if
sp_MSrestoresavedforeignkeys exists. The sp_MSdropfkreferencingarticle
system procedure has been substantially enhanced in SQL2005 to save off
meta-data of FKs referencing a published object that we are about to drop
(so we can drop the table a few lines below) and at the end of the
snapshot
delivery session, the distribution agent will call the new
sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs based
on
meta-data we saved off. Now, it is very possible that I am misreading the
whole thing or there are cases not handled by the new mechanism although
I
will encourage you to test it out :)

-Raymond





"Paul Ibison" <Paul.Ibison@xxxxxxxxxxxxx> wrote in message
news:%23Qe0tjYnHHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
Raymond - I reckon we are talking at cross-purposes, or I might be
off-track - anyway let me explain my thinking....
In Terri's case there are tables in the Dev environment that don't
exist
in the Prod environment, so when the other tables are reinitialized
using
snapshot replication from Prod to Dev, if there are any relationships
that
exist purely in the Dev environment then there would be FK issues.
Rgds,
Paul Ibison







.

Relevant Pages

  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... snapshot and remove all your subscriber data once the snapshot has been ... As part of this replication the contents of the development ... SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... snapshot and remove all your subscriber data once the snapshot has been ... As part of this replication the contents of the development ... process the inserted rows in the development database table are removed. ... SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... The DBCC CHECKIDENT command now ... SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you ... We are using push replication so it looks like I will have to execute the ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... snapshot and remove all your subscriber data once the snapshot has been ... As part of this replication the contents of the development ... inserted rows still remain in the development database table. ... violation and then run DBCC CHECKIDENT to reseed the identity value. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... snapshot and remove all your subscriber data once the snapshot has been ... As part of this replication the contents of the development ... inserted rows still remain in the development database table. ... violation and then run DBCC CHECKIDENT to reseed the identity value. ...
    (microsoft.public.sqlserver.replication)