Catching duplicate inserts into SQL Server Database Table

[Stuart Ferguson]

I am currently in the process of developing a transaction loading
service which parses an xml file and loads these transactions to a
Database.

The problem i am having is if i try to process the same file twice
(accidentally copied into the listening folder twice) then the
transactions are inserted twice, i have ovecome this by creating a
unique index on the DB Table holding the txns but my next problem is
that when the duplicate txn is being loaded the error is being handled
by the Database (Exception is displayed in SQL Server Profiler) but no
error is reaching my database dll this is carrying on as normal.

The method being used to execute the SP is the ExecuteNonQuery method
which is returning a -1 value, the MSDN help does not indicate if this
is thrown on the insert of a dup transaction :s i was wondering if
anyone else has had the same problem and overcome it

Thanks

Stuart Ferguson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brian Brown]

Stuart,

One way I can think of to handle this is to use a return parameter which
catches a return value that you specify from the proc. This could be a value
that you set if the proc errors. If you wish you can return the global
variable/system function @@ERROR which will return 0 if no errors happen.
Then check this value and handle any possible exceptions.

Hope this helps.
-----------------------