June 21, 2013 at 4:21 am
Hi,
Is there any setting in SQL Server that makes an UPDATE statement raise an error if no rows are affected?
I'm using Red Gate SQL Data Compare to sync databases but there are some issues with the GUIDs (on a first phase they where NEWID() and not static) and when updating old databases the sync script can update 0 rows.
I'd like for the script to raise an error if no rows are affected. Is there any thing to do this, rather than writing code after every update statement checking @@ROWCOUNT?
Thanks,
Pedro
June 21, 2013 at 6:19 am
Hi
Did you try:
UPDATE mytable SET mycol = 'new vlaue' WHERE 'col' = 'value'
IF @@ROWCOUNT = 0
RAISERROR('No rows updated',16,1)
Regards
Mike
June 21, 2013 at 7:23 am
Hi,
That's what I'm doing for some cases, just like I say "rather than writing code after every update statement checking @@ROWCOUNT?".
But since I'm using SQL Data Compare some scripts can have more than 1.000 update statements and it's not practicable to do that check after every update.
Pedro
June 21, 2013 at 9:22 am
PiMané (6/21/2013)
Hi,That's what I'm doing for some cases, just like I say "rather than writing code after every update statement checking @@ROWCOUNT?".
But since I'm using SQL Data Compare some scripts can have more than 1.000 update statements and it's not practicable to do that check after every update.
Pedro
You are going to have to add that manually. There is nothing wrong with an update not affecting any rows. It is not an error.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 9:30 am
In this case it is an error... a serious one.
One year ago when the databases where created some tables add NEWID() for the GUID columns.
Our clients used the applications and insert data, modify, what ever...
The scripts were corrected and putted static GUIDs a few months ago.
Every month there are upgrades: we compare the last month "base" scripts with this month and create the upgrade script.
Some upgrade scripts have data updates compared by the GUID column.
In old databases the GUID may be different so no row will be updated and we don't want that to happen. An error must be raised and we will modify the script so it will, by some other column or what ever it may be needed, modify the desired row.
Since the upgrade software is in C# I'll probably use the InfoMessageEvent to catch the "silent" output from SQL Server (the messages) and see if there's any 0 rows affected message.
Thanks,
Pedro
June 21, 2013 at 10:02 am
PiMané (6/21/2013)
In this case it is an error... a serious one.
It is NOT an error with SQL. It is a logic error in your code that violates business rules. The difference may be subtle but if we started raising errors everytime an update affected no rows it would be a massive issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 10:07 am
I know it's not a SQL error. Just wanted to know if there's any flag, setting, what ever, ... that can activate a behavior like the one I need.
But, like you say, it's not a SQL error, so there's probably nothing inside SQL to do that.
I'll just use the output message in c# and see it there's any 0 rows affected message.
Thanks,
Pedro
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply