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 creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > However, I want the
> > view to return a row for each row of the PURCHASE table, but with the
> > ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> > in ITEM or CUST.
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.