SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update raise error


Update raise error

Author
Message
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1040 Visits: 1334
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...
michal.lisinski
michal.lisinski
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1105
Hi
Did you try:

UPDATE mytable SET mycol = 'new vlaue' WHERE 'col' = 'value'
IF @@ROWCOUNT = 0
RAISERROR('No rows updated',16,1)



Regards
Mike
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1040 Visits: 1334
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...
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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.

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)
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1040 Visits: 1334
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...
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
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.

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)
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1040 Visits: 1334
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search