Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update raise error Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 4:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 513, Visits: 1,123
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




If you need to work better, try working less...
Post #1466113
Posted Friday, June 21, 2013 6:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:05 AM
Points: 285, Visits: 334
Hi
Did you try:
UPDATE mytable SET mycol = 'new vlaue' WHERE 'col' = 'value'
IF @@ROWCOUNT = 0
RAISERROR('No rows updated',16,1)

Regards
Mike
Post #1466151
Posted Friday, June 21, 2013 7:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 513, Visits: 1,123
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




If you need to work better, try working less...
Post #1466174
Posted Friday, June 21, 2013 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466250
Posted Friday, June 21, 2013 9:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 513, Visits: 1,123
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




If you need to work better, try working less...
Post #1466257
Posted Friday, June 21, 2013 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466282
Posted Friday, June 21, 2013 10:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:49 AM
Points: 513, Visits: 1,123
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




If you need to work better, try working less...
Post #1466286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse