How to retrieve identity in stored procedure for updatable subscription??? -- sql server replication

sql server replication : How to retrieve identity in stored procedure for updatable subscription???

Steve Klein
8/7/2004 9:34:50 PM

We have transactional replication setup between SQL Server 2000 on Win2003
and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is pushing
an updatable subscription (queued updating) to the MSDE instance.

We are trying to insert master and detail rows into a pair of tables via a
stored proc. After inserting the master row, doing @@IDENTITY to retrieve
the ID for the child row's foreign key returns NULL, causing the child row
insert to fail. Looking at the table after running the proc, the parent row
does have a valid identity value.

Any suggestions on how to make this work?

Thanks,

Steve Klein

Paul Ibison
8/8/2004 12:06:09 PM

Steve,
I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
publisher and the subscriber).
Also, presumably this insert was done on the subscriber?
Do you have just queued or immediate updating subscribers with queued
failover?
Are there any other triggers on the table (apart from the replication ones
if we are talking about the subscriber)?
Regards,
Paul Ibison

Hilary Cotter
8/9/2004 7:56:28 AM

I have tried to repro this and it does work. Can you post the problem proc
for us?

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Steve Klein
8/9/2004 4:00:56 PM

Paul--

My apologies for not sending the following reply to the group. That was my
goal...

-------------------------------------

Paul--

Thanks for the response. DBCC CHECKIDENT shows no problems (publisher
and subscriber).

We don't have triggers (other than those for replication) on the tables and
there are not any other transactions affecting the transaction in our
distributed database. The test insert was done via a stored procedure on
the subscriber. This
stored procedure does the following:
1) Insert into table A
2) set @x = @@IDENTITY
3) Insert into table B (using @x as foreign key back to table A)

We have replication configured for queued updating.

Further info -- if we use IDENT_CURRENT('table_name') instead of
@@IDENTITY,
we do get an ID back, but IDENT_CURRENT has (claimed) global scope.

--Steve

[quoted text, click to view]

Steve Klein
8/9/2004 4:03:10 PM

Hilary--

I'll be able to post the procedure later today. Thanks.

--Steve

[quoted text, click to view]

Paul Ibison
8/10/2004 8:42:43 AM

Steve,
there's something strange here, but I'd check to see if Scope_Identity()
also returns NULL.
Finally, I'll keep an eye on your thread with Hilary where you mentioned
posting up the script, because I'd also like to try to reproduce this.
Regards,
Paul Ibison

Steve Klein
8/10/2004 5:37:29 PM

Paul--

Thanks very much for your interest. I (sheepishly) need to report that the
problem was a form of user error. The table causing the problem had a text
column. The way that the error management was implemented was evidently
causing the @@IDENTITY to return NULL.

Why Ident_Current() worked is odd, but since removing the text column caused
the @@IDENTITY to work, we are not too inclined to look much further :)

By the way, we have changed to Scope_Identity() rather than @@IDENTITY.

Thanks again.

--Steve

[quoted text, click to view]

Steve Klein
8/10/2004 5:40:30 PM

Hilary--

Thanks for your help. As I indicated in the response to Paul's last post,
this was a stored procedure error, which I would label "user error"... Check
there for more details.

--Steve

[quoted text, click to view]

Paul Ibison
8/11/2004 8:56:42 AM

Steve,
many thanks for the update.
Cheers,
Paul

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

View Other Months
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn