March 30, 2018 at 12:43 pm
Hello. I have a report that sends an email to a team on the first of the month from a stored procedure in SQL. This report adds in duplicate records, and I have been trying to figure out why. It's not consistent either. One name will appear once, but another will have 4 duplicates. so I have been troubleshooting, and I commented out the lines SET NOCOUNT ON; and SET XACT_ABORT ON; to see what happens. No duplicates. But does anyone know why? Is there a rule about including this code in stored procs that I should be aware of? I didn't write the script, but if it's bad practice, I want to take them out of other reports.
Any ideas?
Thank you,
Amy
March 30, 2018 at 1:14 pm
Any chance this is a busy OLTP table and you have WITH (NOLOCK) as part of the query? Your symptoms would probably qualify... SET NOCOUNT ON does nothing but turn off the ROW counts that get returned. Try using that in a query in SSMS, and also try SET NOCOUNT OFF. Then when the query completes, check the messages tab. While ON, you won't see any indicator of the number of rows affected, but with it OFF, you will see them, so that isn't likely to have anything to do with the problem.
You'll also need to check the query to be sure there's nothing in the data that could throw it off somehow...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 30, 2018 at 1:15 pm
Update, I was wrong. neither NOCOUNT ON nor XACT_ABORT ON were causing it. I added SET TRANSACTION ISOLATION LEVEL SERIALIZABLE and WITH (updlock) and that produced no duplicates. Per this article http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply