the identity element is removed by design. Replication tells you about this
in the statement:
If you want the IDENTITY property transferred to the subscription table, you
must do the following:
» Create the subscription table manually using the IDENTITY property
and the NOT FOR REPLICATION option. You can have SQL Server execute a custom
script to perform this action before the snapshot is applied. To specify a
custom script, create the publication, create the script, and enter the
script name on the Snapshot tab of the publication properties.
that appears in the Articles issues dialog box.
To replicate the identity column modify your sp_addarticles proc call to
look like this
1) first script out the table with the identity element on it. In the script
modify the identity property to be not for replication. Lets say you call is
sqltable.sql and save it in c:\temp
2) modify your sp_addarticle proc call to look like this
sp_addarticle @publication = 'publicationName',
@article = 'TableName',
@source_object = 'TableName',
@destination_table = 'TableName',
@creation_script = 'c:\temp\sqltable.sql',
@pre_creation_cmd = 'none',
@schema_option = 0x02,
@status = 16
--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html [quoted text, click to view] "Mike" <MichaelLopez@inds.com> wrote in message
news:OQe0pgCZEHA.2972@TK2MSFTNGP12.phx.gbl...
> Hi, Hilary. Thanks for responding.
>
> You hit the nail on the head - kind of ...
>
> One of the tables being replicated has an Identity column, which is the
last
> column in the table, that is the primary key. There is a stored procedure
> that inserts into this table but does not specify a value for the Identity
> column in the list of column names.
>
> When the table is replicated the "Identity" attribute and primary key
> designation are removed. Thus, the stored procedure must include a field
for
> the INSERT.
>
> My question now is: Why is the "Identity" attribute being removed? I've
> tried specifying "Identity=Yes" and "Identity="Yes (not for replication)"
> but the same thing happens.
>
> Do you know why?
>
> Thanks again,
>
> Mike
>
> "Hilary Cotter" <hilaryk@att.net> wrote in message
> news:ult97s6YEHA.3304@TK2MSFTNGP09.phx.gbl...
> > is the schema for the table you are replicating identical on the
publisher
> > and subscriber?
> >
> > Normally you get this error when they are different.
> > "Mike" <MichaelLopez@inds.com> wrote in message
> > news:uWkX$a5YEHA.3128@TK2MSFTNGP09.phx.gbl...
> > > Hello.
> > >
> > > I'm setting up a Push subscription. The articles of comprised of
tables,
> > > views, and stored procedure.
> > >
> > > The Snapshot agent finishes successfully and then the Distribution
agent
> > > kicks in. I'm getting the following error:
> > >
> > > Insert Error: Column name or number of supplied values does not match
> > table
> > > def"
> > >
> > > I can't find the specific error. It looks like a stored procedure may
be
> > the
> > > culprit, but the agent history detail doesn't pinpoint which.
> > >
> > > Can anyone tell me how I can find exactly what the agent is doing, and
> > where
> > > it is failing?
> > >
> > > Thanks in advance,
> > >
> > > Mike
> > >
> > >
> >
> >
>
>
