May 16, 2006 at 2:43 am
We have a Bloomberg trade feed that comes in and updates a SQL table, with a unique constraint to stop duplicates. On violation you get the following message "Violation of UNIQUE KEY constraint 'CustIDunique'. Cannot insert duplicate key in object 'Customers'. The statement has been terminated."
The feed will then retry for 30 secs. This just slows everything down, especially if we need to role back trades. Is there a way to suppress the violation message so it just goes onto the next trade.
May 16, 2006 at 3:55 am
You should have the feed not trying to insert already existing data.
It can be done in several different ways, but the common factor is to modify the feeds insert statement.
/Kenneth
May 16, 2006 at 4:26 am
How can I modify the insert statement as it's coming through a Bloomberg gateway trade feed.
May 16, 2006 at 5:17 am
If you cannot modify the insert statement from the feed because it is not under your code control, you can have an interface table in which the feed inserts the values and this table will not have that unique constraint. Then, have a process that takes this data, does a insert into..<main_table>...select ...from <interface_table> where not exists (....)
to select only the unique value records...there are other ways as well to write such an insert statement.
May 16, 2006 at 5:42 am
Thanks. Will look into.
May 16, 2006 at 6:33 am
To stop duplicates in Unique constraint turn it into an index and use the IGNORE_DUP_KEY, it is what it was created for. Try the links below for detail. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueindex.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueconstraint.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
May 18, 2006 at 8:12 am
I'd also suggest going back to Bloomberg. As a vendor of yours (who I'm sure you're paying very handsomely), they shoud not subject your DB to any kind of integrity issues.
Terry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply