… and I’ve done the same so I can’t blame them, was they failed to use Get-Member; they made assumptions (bad idea)
If they had run $array | gm, they would have seen that the $array is not an array but a System.Data.DataRow, and we’ve seen them before.
The mistake I made…
…was running $array | gm and seeing that there was no method called .Contains.
So I was going down the route of using a mix of foreach and -eq.
This wouldn’t have been great though as we would have to iterate over every single row and seeing if the value existed. I assumed that the reason the questioner wanted to use .Contains was to get around iterating over every single row, so this wasn’t going to work either.
What we both should have done…
…was use Get-Member.
The Questioner should have used $Array | gm and I, knowing the next step, should have used $Array.code | gm
It’s nice to see the way that NULLs are treated differently in PowerShell. 🙂
If we drop down from the DataRow into the property of the DataRow, it becomes a string! Perfect because the string contains the method .Contain.
I recently ran into a problem with the QUOTED_IDENTIFIERS option in SQL Server, and it got me to thinking about these SET options.
I mean the fact that, on tables where there are filtered indexes or computed columns with indexes, QUOTED_IDENTIFIER is required to be on to create any other indexes is just not intuitive. But if you can’t create indexes because of it then I’d argue that it’s pretty damn important! I also found out that this problem is not just limited to QUOTED_IDENTIFIER but to ARITHABORT and ANSI_WARNINGS as well.
SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
And for ANSI_WARNINGS it says:
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
It’s not just Indexes
So, like a dog when it sees a squirrel, when I found out about the problems with ARITHABORT and ANSI_WARNINGS I got distracted and started checking out what else I could break with it. Reading through the docs, because I found that it does help even if I have to force myself to do it sometimes, I found a little gem that I wanted to try and replicate. So here’s a reason why you should care about setting ARITHABORT and ANSI_WARNINGS on.
Default to on
At one stage or another if you’re working with SQL Server, you’ve probably encountered the dreaded “Divide By 0” error:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
If you want to check this out, then here’s the code below for our table:
-- Create our test table...
CREATE TABLE dbo.ArithAborting (
id tinyint NULL
And our attempt at inserting that value into the table:
SET ARITHABORT ON;
SET ANSI_WARNINGS ON;
-- Check can we insert a "divide by 0"...
INSERT INTO dbo.ArithAborting (id) SELECT 1/0;
And we get our good, old, dreaded friend:
We check our ArithAborting table and nothing is there, like we expected!
What about if we were to turn our ARITHABORT and ANSI_WARNINGS off though, what happens then? Well that’s a simple thing to test, we just turn them off and run the script again:
--Turn ARITHABORT off;
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
-- ...insert into our table...
INSERT INTO dbo.ArithAborting (id) SELECT 1/0;
Now before I freak out and start thinking that I’ve finally divided by zero, let’s check the table:
During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
Do I like this?
If I have a terminating error in my script, I quite like the fact that SQL Server is looking out for me and won’t let me put in bad data, but if you have these options turned off, even if you wrap your code in an TRY...CATCH block, it’s going to bypass it.
Plus if you are trying to divide by 0, please stop trying to break the universe. Thank you.
These topics have come about as they are both issues that I’ve had to deal with. Well, another of those issues is dealing with exporting special characters out of SQL Server using PowerShell.
We already have our table called “dbo.NewLineNotes” from before when we were trying to copy new lines out of SQL Server so we’re going to add another row.
Now personal experience for me centered around the German Eszett (“ß”) but you may encounter this with other characters.
-- Insert some special characters...
INSERT INTO dbo.NewLineNotes (Notes)
VALUES (N'This is a ß')
Now if you were to use the code from keeping new lines post…
and open up the csv file we would get…
“What do we do when we fall down?”…
Well with SQL Server, I normally break things down into the smallest parts and slowly build it up until it breaks. For this, it breaks when we get to Export-CSV as everything before it works!
PowerShell is even easier for troubleshooting methodology as , and we’ve talked about it before, Get-Member and Get-Help are there to help us!
We know that it’s Export-CSV that is somehow screwing up our special character so the obvious next step…
help Export-CSV -Full;
And we can see a parameter just shine at us!
So we have to define an “Encoding” do we? I used “UTF8” and modified my query…
And special characters are no longer an issue for us 🙂
Now this isn’t a problem of itself, it can be easily achieved by using a CREATE TABLE statement and then using 2 ALTER TABLE statements to create the constraints.
The problem that I had with this was that, so far, I was going along and creating these tables & columns with the constraints created in-line and it just galled me to have to break this flow and create these constraints as ALTER statements.
Checking the examples in the new Microsoft Docs didn’t show any examples that I could find of creating both constraints together on the same column so I experimented and found out that you can!
Notice 2 things here:
There is no need to specify a FOR <column name> on the default constraint because SQL Server can tell the constraint is to work on the column it is currently defining.
There is no comma separating the two constraints. This would break the inline property of these statements and SQL Server would think you’ve messed up syntax on a constraint (this got me for a sec).
Great, I can keep my constraints inline!
That’s a wrap
Documentation is useful but they do not cover every situation. Have a test environment; Hypothesize, test, and verify. You never know what you’d find.
A lot of the time, DBAs are asked to run adhoc reports for various business people and, more often than not, the expected medium for these reports is Excel.
Now for the most part this seems simple enough…
Run the T-SQL report
Highlight the results
Copy the results
Paste into an Excel worksheet
How do you deal with carriage returns though? New line feeds? Tabs? Commas when you’re trying to comma delimit?
Try and copy them into an Excel worksheet and what you’re going to get is confusion, alarm, and vexation.
Not exactly the clear reporting that the business people are hoping for.
So what can we do? Panic? Grab another coffee? Roll your “r’s”?
Yes, yes, and not yet…
I have mentioned before that we can use CHAR(10) and CHAR(13) for new lines and carriage returns in SQL Server so I’ll leave it up to an exercise to the reader to create a table with these “troublesome” bits of information in them (plus if you came here from Google, I assume you already have a table with them in it).
For me, I’ve just created a single table dbo.NewLineNotes that has a single entry with a new line in it.
So a straight-up copy and paste isn’t going to cut it here. If we have more than 1 row, we’re not going to get a 1 entry to 1 row in the report that we are looking for. How do people deal with this?
I make no effort to hide the fact that I am not the biggest fan of GUIs, and I’ve been fortunate enough to turn that dislike into an admiration of command line tools. I said “an admiration” not that I’m any good at them yet! I have been fortunate enough to provide a function for dbatools.io (have you helped them out yet?) but just goes to show that anyone can help out, regardless of skill level.
In case you ever wondered where this dislike came from, let me tell you a hypothetical story about…my friend that I used to work with.
Now my friend wasn’t a DBA then, he wasn’t even an Accidental DBA, he was more a “that guy is good with databases, ask him” kind of guy. In short, my friend knew just enough to be dangerous without knowing that he could be.
Back in the SQL Server 2012 days…
…which was either today or 5 years ago, depending on what version of SQL Server you’re running but we’ll say 5 years ago, my friend was working as a SQL Support Engineer for a software provider.
The provider didn’t handle backups, that was all taken care of by 3rd parties. In case something went wrong, these 3rd parties provided the backups and either the software provider, or the in-house I.T. would restore them. (FYI, I’m very cautious of 3rd party backup tools as well).
One Friday, we did a release…
…and eventually a bug was discovered in the release that could have potentially had some data impact (no particular reason to say Friday, I just don’t think you should release on one).
So a plan was made to request a 2 week old backup and to compare the current data against the current production database.
My friend goes to the Object Explorer, opens the “Databases” node, and sees that there is two databases there; Live ([TheEarlyBird]) and a disused copy of Live ([TheEarlyBird2]) that is a day old and can be overwritten.
Not knowing any better, my friend right-clicks the old copy, clicks “Tasks”, then “Restore”, then “Database…”, and a lovely GUI pops up.
Now my friend doesn’t know any better, he thinks that the GUI is here to help him and in most of the cases it is. What my friend failed to realize is that there is a difference between helping him and doing the work for him…
The 3rd party backup file has not yet been retrieved but that stops my friend not! This is a urgent case so my friend forges ahead, thinking that he can get everything set up and ready then all he would have to do is select the file when it was made available.
My friend would be overwriting the disused database so this would not need to be changed.
Checked the box “Overwrite the existing database (WITH REPLACE)” as we are overwriting the disused database
File is now available…
So my friend goes back to the General Page, clicks the “Device” radio button, and selects the backup file…
…and clicks “OK” to start the restore!
Errors! Errors galore…
My friend encounters errors:
Exclusive access could not be obtained because the database is in use.
This confuses my friend as this is a disused copy of the database, the only person who should be on it is himself.
Does my friend go and maybe check out EXEC sp_Who2; to see who else could be on this database? No, remember that my friend knows just enough to be dangerous. My friend goes back to “Tasks”, “Restores”, “Databases”, goes to the Options Page and checks the box labelled “Close existing connections to destination database”….
With that, my friend clicks the “OK” to restore the database and continues on his merry way…the dumb fool that he is.
SQL Server 2012 GUIs…
…have this little “optimization” technique where it looks at the name on the database backup file and matches up with the database name.
Now what this actually meant was the moment that my friend clicked the “Device” button, all his work was gone and his destination database reverted to the Live Database!
The first time my friend clicked “OK” to restore wasn’t a problem since there were connections and the Live database wasn’t affected.
But then my friend goes back and clicks “Close existing connections to destination database”…just enough knowledge to be dangerous…
So in summary, what my friend had done was kick every single connection off of Live and then effectively wiped 2 weeks worth of data.
Thank goodness for tail-log backups!
GUIs are good for….
They give you the option to script out the configurations you have chosen. If my friend had chosen to script out the restore, rather then clicking “OK” to run it, maybe he would have caught this mistake when reviewing it – rather than overwriting the Live database with 2 week old data and spending a weekend in the office with 3 colleagues fixing it.
What we had was INSERT statements, directly after a MERGE statement, that inserted into a table variable a hard-coded name of the column, the old value, and the new value.
Is that what I would do? Doesn’t matter, it was there before I got there, seems to work, and is low down on the list of priorities to change.
The question was, every time that they needed to add a column to a table, and change log it, they had to add multiple lines to the change tracking procedure and the procedure was getting gross and hard to maintain.
You know the drill by now, I quite like to play along so let us facilitate that (from now on I’m going to use Gist, formatting with native WordPress is starting to annoy me).
This will create our table and, luckily, all of it’s columns are important enough to warrant capturing when they get changed.
Old, Way WHERE old=way
Let’s take a look at the code that they were using, shall we?
And the results?
You can probably see the problem here.
Hey! It’s legacy code, let’s focus on just 1 problem at at time!
The main issue that I was asked about was every time a column was deemed important and needed to be added to the list, they had to insert another INSERT INTO @ChangeLogTemp... and they thought that it wasn’t sustainable in the long run.
Hmmm it also comes across as very RBAR doesn’t it? Every time we want to include another column to the change tracking, we have to add them row by agonizing row. The script is already big enough, if we keep adding more, it will get massive!
Set based is 90% of the time the right way to go but how do we do set based solutions on the same table?
New JOIN Way ON new = way
The first thing I do is to change that table variable into a temp table. Stats, indexes (if necessary), and I can query the results as we go along. Much better!
The second thing is that, whether by luck or by design, the legacy code has the same naming conventions for the columns; new column values are have the prefix “New%” in the column name and old columns have the “Old%” prefix.
This works for us because we can now split the new columns into 2 derived tables, New and Old, and that way we have the differences.
Have you ever tried to find the differences between two consecutive rows of data? It’s fiendishly difficult. WHERE Column1 on row1 != Column1 on row2 apparently just does not work, le sigh.
I say “little brother” because the whole document talks about PIVOT, with only brief mentions of UNPIVOT in the notes.
If you’re writing documentation like this, please stop.
With UNPIVOT we can create a table of our rows around our ID and Column names…
… and with this, we can join on our ID and Column names and get to our more intuitive WHERE OldValue != NewValue.
Bringing it all together!
And it works!
It’s not great though.
The whole thing was supposed to be to reduce the amount of changes required when they need to include or exclude columns. All in all though, it’s just 6 lines less. Not exactly the great return that you’d expect.
Yeah, true with the old way for every column we want to add we have to add an extra 6 lines while the new way adds 2.
That means for 1,024 columns:
The old way could have at least 6,144 lines per table. (1024 * 6)
The new way could have at least 2,048 lines per table (not explaining this calculation 😡 )
So, is there anything else that we can do?
I’ve talked before about T-SQL automation with Dynamic SQL and this should be a good candidate for that.
What can we make dynamic here though? How about…
The new and old columns bit?
The FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6]) bit?
The CAST(ISNULL([Old/NewColumn], '') AS nvarchar bit?
This is basically the same as the above. Don’t be put off by needing to add CAST(ISNULL( before the column names, it’s not as complex as you’d think.
Now that we have our dynamic bits, let’s create the full statements.
Full Dynamic Script
Results are good!
Overall, the script is longer at nearly double the lines but where it shines is when adding new columns.
To include new columns, just add them to the table; to exclude them, just add in a filter clause.
So, potentially, if every column in this table is to be tracked and we add columns all the way up to 1,024 columns, this code will not increase. Old way: at least 6,144. New way: at least 2,048. Dynamic: no change
Like the script, this was a massive post. Back at the start, I said that a developer came to me because they wanted to get more DRY (?) and stop needing to add more content to the stored procedure.
Do you think the developer used this?
I can’t say that I blame them, it’s slightly ugly and unwieldy, and I wrote it so I should love it.
Yet if something was to go wrong and the need was there to open the procedure and troubleshoot it, the first person to open this up is going to let out a groan of despair!
So this request turned into a proof of concept and nothing more. No skin off my back, I have a growing list of tasks to accomplish by 5 minutes ago. Better get back to them.