combined merge + custom conflict resolution

I have what seems to be a unique case, but logically, would think that
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:

> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:

> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>

Column is constrained to be unique. Value 123 already

I'm using sqlDataAdpater to call a stored procedure for insert
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.