September 19, 2011 at 6:54 pm
Hey all,
Occasionally, I have to sync replicating databases manually using the TableDiff utility. For those not familiar, the utility compares two databases and generates queries to synchronize one table so that it looks like another.
Of course, sometimes one database contains the more current information, and sometimes the other. So I have to be careful about which of the queries generated I run.
One frustrating problem I've consistently had is accidentally running the entire set of queries because I didn't have any statements highlighted. Either my finger slipped or the remote control application I'm used is slow to respond or I'm careless, but this runs the entire set of queries, trashing the data and ensuring that I have to restore from a backup and setup replication again.
This is very frustrating. A colleague of mine told me I could stop this from happening by putting some nonsense like "asdf" at the top of the query, which would cause it to error out. This didn't work. Does anyone if I can use flow control or triggers or whatever to prevent this from happening?
Currently, I've resorted to copying lines from the query, which is focused on master, to another query that is focused on the database, and running them. It works OK, but it's a little tedious. I'd appreciate any advice anyone has to give. Thanks.
September 19, 2011 at 8:17 pm
How about using two different SQL accounts. the one used for checking using should be a low previlage one something like Readonly rights. The one used while synchronising should be a powerful account like DB Owner.
Regards,
Raj
September 20, 2011 at 7:52 am
How about just putting a begin transaction on the first line and a rollback on the last line? At least if you accidentally run the entire open window of queries it will roll back.
_______________________________________________________________
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/
September 20, 2011 at 7:57 am
if your table names are not fully qualified with the database names, then you could write
USE DB_that_does_not_exist
or similar
at the top line of the query window to force the query to use a DB that does not exist and cause it to fail.
September 20, 2011 at 8:37 am
You can enclose the whole block of code in comments like:
/***
my code
line 2
**/
Then it can only run when highlighted.
September 20, 2011 at 8:43 am
I tried all those tricks and frankly the one I preffer is to have implicit transactions on by default in all my ssms environements.
That way whatever you do has to have a transactions, no exceptions. You do have to remember to commit / rollback. But you never have anything broken because of you.
No the simple trick is to change the default template or just remember to type rollback as your "first" (last) line of code, then your fine to always use F5.
The one down side is that if you forget an open transaction someone could get blocked or tempdb will keep growing with snapshot isolation.
You also have to remember to turn off IT if you have to run a backup or restore command, but that's about it.
Of course you need not to promote that to prod in "real" code.
September 20, 2011 at 10:34 am
Wow, so many great responses! Thanks so much folks. I like the rollback idea (I'm not that familiar with it, I'll read up on it), as well as using a fake database. These replies gave me a lot of ideas and some great things to learn up on. Thanks again.
September 20, 2011 at 10:44 am
The other thing that will not require you to change any settings or add nonsense code is to simply disconnect the session after the query is generated. Or, if you are copying this query to the clipboard or a file first (as is with RedGate SQL Data Compare), open a new query window and make sure it is disconnected before pasting any code there. Then, edit your query and connect only after it is ready.
Of course, the best solution here is to be more careful about what you execute on your page and what is highlighted 🙂
Jared
Jared
CE - Microsoft
September 20, 2011 at 11:04 am
the one thing I would caution about using a dummy database is to watch out for GO statements...
for example, if you have
USE dummydb
select * from sys.objects
GO
select * from sys.objects
drop table importantstuff
youll get an error on the USE, but it will pick up again and start running after the GO...
but the following will never run unless some part of the code is highlighted.
/***
USE dummydb
select * from sys.objects
GO
drop table importantstuff
**/
of course, encapuslating it all in a transaction is great... if you can remember to commit 😉
September 20, 2011 at 11:09 am
NJ-DBA (9/20/2011)
the one thing I would caution about using a dummy database is to watch out for GO statements...for example, if you have
USE dummydb
select * from sys.objects
GO
select * from sys.objects
drop table importantstuff
youll get an error on the USE, but it will pick up again and start running after the GO...
but the following will never run unless some part of the code is highlighted.
/***
USE dummydb
select * from sys.objects
GO
drop table importantstuff
**/
of course, encapuslating it all in a transaction is great... if you can remember to commit 😉
That's why I said implicit tran + ROLLBACK as the first line of code I type.
That way I can always just hit F5 unless I want to run something more localized. This is where you sometimes forget to rollback and get bit in the arse.
But that would also be true with and explicit begin tran.
September 20, 2011 at 11:22 am
I still believe that we are missing a bigger issue here. We are developers/admins who work within an interface that executes script very easily. We can all come up with tricks to help us, but if this is a common error being made you need to focus on paying more attention to what you are doing. Hey, we all make mistakes, but if this is a common one you need to fix it at the source. The SSMS IDE can be very dangerous if you are not careful, no matter what tricks you use. If you are copying and pasting code into a connected query editor window that you know up front is not all intended, simply paste it into notepad or something and edit it first. Otherwise, I would disconnect the session as soon as this program puts the code into the query window, edit it, then reconnect. I'm not trying to be a jerk, but let's all understand that there is a difference solution to preventing an occasional error and repeating the same error many times over.
Thanks,
Jared
Jared
CE - Microsoft
September 20, 2011 at 11:29 am
jared-709193 (9/20/2011)
I still believe that we are missing a bigger issue here. We are developers/admins who work within an interface that executes script very easily. We can all come up with tricks to help us, but if this is a common error being made you need to focus on paying more attention to what you are doing. Hey, we all make mistakes, but if this is a common one you need to fix it at the source. The SSMS IDE can be very dangerous if you are not careful, no matter what tricks you use. If you are copying and pasting code into a connected query editor window that you know up front is not all intended, simply paste it into notepad or something and edit it first. Otherwise, I would disconnect the session as soon as this program puts the code into the query window, edit it, then reconnect. I'm not trying to be a jerk, but let's all understand that there is a difference solution to preventing an occasional error and repeating the same error many times over.Thanks,
Jared
You're saying the same thing- just giving a different "trick". If it works for you to disconnect, then do so. If it works for Ninja to use implicit transactions and start with a rollback, more power to him. For me, I tend to comment out sections of code that scare me (drop database for example) and only click Execute when I really, really mean it... but for you to arge that disconnecting and reconnecting is a superior way to commish the same thing isnt really fair... you can forget to disconnect just like I can forget to comment out some code. The only real "trick" that works 100% of the time is to be damn sure you know what you are about to execute when you click that button or hit F5 or whatever you like to do.
September 20, 2011 at 11:36 am
NJ-DBA (9/20/2011)
jared-709193 (9/20/2011)
I still believe that we are missing a bigger issue here. We are developers/admins who work within an interface that executes script very easily. We can all come up with tricks to help us, but if this is a common error being made you need to focus on paying more attention to what you are doing. Hey, we all make mistakes, but if this is a common one you need to fix it at the source. The SSMS IDE can be very dangerous if you are not careful, no matter what tricks you use. If you are copying and pasting code into a connected query editor window that you know up front is not all intended, simply paste it into notepad or something and edit it first. Otherwise, I would disconnect the session as soon as this program puts the code into the query window, edit it, then reconnect. I'm not trying to be a jerk, but let's all understand that there is a difference solution to preventing an occasional error and repeating the same error many times over.Thanks,
Jared
You're saying the same thing- just giving a different "trick". If it works for you to disconnect, then do so. If it works for Ninja to use implicit transactions and start with a rollback, more power to him. For me, I tend to comment out sections of code that scare me (drop database for example) and only click Execute when I really, really mean it... but for you to arge that disconnecting and reconnecting is a superior way to commish the same thing isnt really fair... you can forget to disconnect just like I can forget to comment out some code. The only real "trick" that works 100% of the time is to be damn sure you know what you are about to execute when you click that button or hit F5 or whatever you like to do.
Different scenario here. I work mainly with SSRS.
Drop database is a whole other issue where I would work locally (any server where I'm 100% alone). And the drop database would be preceded by create DB.
As always, it depends :hehe:.
Bottom line is be aware of what you're doing and don't work when tired. Worked very well for me over the years.
September 20, 2011 at 11:40 am
NJ-DBA (9/20/2011)
jared-709193 (9/20/2011)
I still believe that we are missing a bigger issue here. We are developers/admins who work within an interface that executes script very easily. We can all come up with tricks to help us, but if this is a common error being made you need to focus on paying more attention to what you are doing. Hey, we all make mistakes, but if this is a common one you need to fix it at the source. The SSMS IDE can be very dangerous if you are not careful, no matter what tricks you use. If you are copying and pasting code into a connected query editor window that you know up front is not all intended, simply paste it into notepad or something and edit it first. Otherwise, I would disconnect the session as soon as this program puts the code into the query window, edit it, then reconnect. I'm not trying to be a jerk, but let's all understand that there is a difference solution to preventing an occasional error and repeating the same error many times over.Thanks,
Jared
You're saying the same thing- just giving a different "trick". If it works for you to disconnect, then do so. If it works for Ninja to use implicit transactions and start with a rollback, more power to him. For me, I tend to comment out sections of code that scare me (drop database for example) and only click Execute when I really, really mean it... but for you to arge that disconnecting and reconnecting is a superior way to commish the same thing isnt really fair... you can forget to disconnect just like I can forget to comment out some code. The only real "trick" that works 100% of the time is to be damn sure you know what you are about to execute when you click that button or hit F5 or whatever you like to do.
I suppose you have a valid argument, but I think of a "trick" as something being inserted into the script to enable a rollback or to remove code; i.e. it still executes what is in the window against a database. A solution to prevent execution is to not be connected. To answer the forum question, the answer is no and no matter which "trick", solution, or whatever is used... I suggest taking a pause and double-checking your query window every time you think about hitting that F5, especially if this error is occurring frequently. Slowing down and double-checking my work has been something I have had to work on in the past and it will end up saving you time and potentially your job 🙂
Jared
Jared
CE - Microsoft
September 20, 2011 at 11:43 am
Ninja's_RGR'us (9/20/2011)
NJ-DBA (9/20/2011)
jared-709193 (9/20/2011)
I still believe that we are missing a bigger issue here. We are developers/admins who work within an interface that executes script very easily. We can all come up with tricks to help us, but if this is a common error being made you need to focus on paying more attention to what you are doing. Hey, we all make mistakes, but if this is a common one you need to fix it at the source. The SSMS IDE can be very dangerous if you are not careful, no matter what tricks you use. If you are copying and pasting code into a connected query editor window that you know up front is not all intended, simply paste it into notepad or something and edit it first. Otherwise, I would disconnect the session as soon as this program puts the code into the query window, edit it, then reconnect. I'm not trying to be a jerk, but let's all understand that there is a difference solution to preventing an occasional error and repeating the same error many times over.Thanks,
Jared
You're saying the same thing- just giving a different "trick". If it works for you to disconnect, then do so. If it works for Ninja to use implicit transactions and start with a rollback, more power to him. For me, I tend to comment out sections of code that scare me (drop database for example) and only click Execute when I really, really mean it... but for you to arge that disconnecting and reconnecting is a superior way to commish the same thing isnt really fair... you can forget to disconnect just like I can forget to comment out some code. The only real "trick" that works 100% of the time is to be damn sure you know what you are about to execute when you click that button or hit F5 or whatever you like to do.
Different scenario here. I work mainly with SSRS.
Drop database is a whole other issue where I would work locally (any server where I'm 100% alone). And the drop database would be preceded by create DB.
As always, it depends :hehe:.
Bottom line is be aware of what you're doing and don't work when tired. Worked very well for me over the years.
very good point...
Dont work when tired... hahaha, I have an 8 week old newborn son... I am ALWAYS tired!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply