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
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
|||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
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hbbbf92amjt6a@.corp.supernews.com...
> 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--
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTduWcTfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> 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--
I'll be able to post the procedure later today. Thanks.
--Steve
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> 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
>
> "Steve Klein" <sklein@.singular.com> wrote in message
> news:10hbbbf92amjt6a@.corp.supernews.com...
Win2003[vbcol=seagreen]
> pushing
a[vbcol=seagreen]
retrieve[vbcol=seagreen]
row
> row
>
|||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
|||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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uGr2g3qfEHA.3148@.TK2MSFTNGP10.phx.gbl...
> 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
>
|||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
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hg0lla3jcmu8f@.corp.supernews.com...[vbcol=seagreen]
> Hilary--
> I'll be able to post the procedure later today. Thanks.
> --Steve
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...
proc[vbcol=seagreen]
> Win2003
via[vbcol=seagreen]
> a
> retrieve
> row
parent
>
|||Steve,
many thanks for the update.
Cheers,
Paul
No comments:
Post a Comment