[PowerShell] Using .Contains with System.Data.DataRow

I realised I hadn’t done a blog post this week and didn’t think I had anything planned, so here is a random PowerShell/SQL Server encounter on Twitter. Hope you enjoy


Recently a question came up on the #sqlhelp hashtag on Twitter asking about a problem that a user was having with using .Contains with an array.

Normally when I see a question regarding PowerShell, Arrays, and Contains I keep an eye on them, not to answer them but to read the responses and learn from them.

However, this one caught my eye for two reasons; it had an image with Invoke-Sqlcmd in it , and it was on the #sqlhelp hashtag. So I said let’s see if I can help out here.

The Question…

… was if you have a table like below…

TableCapture
a…b…c…d…e…f…NULL

and you are running the following PowerShell command to check if the results contain a value…


$String = "abc"
$Array = @(Invoke-Sqlcmd -ServerInstance "SQLServer" -Database "Database" -Query "SELECT code FROM dbo.users")
$Array.Contains($string)

 

InitialQuery
liar!!!

It will return FALSE.

Now we know that the FALSE is false because we know that the string is in there!
This code is proven to work with arrays as stated here by the “Hey, Scripting Guy!”s so this was getting filed under “WTF PowerShell”

The mistake they made…

… 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.

ArrayGM
Does not contain 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

ArrayCodeGM
2 TYPES!!!

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.

And we have our answer…


$Array.code.Contains($String);

BeautifulOneLiner

…and we have our True.

PowerShell…beautiful!

Transact-SQL to Powershell: Substring

In my ongoing attempt to learn Powershell to help automate my workloads, I’ve come across the need to use the Transact-SQL SUBSTRING() function but, in using it, I got the following error:

StartIndexCannotBeLargerThanLengthOfString

Now if you are like me, that is very hard to read but the error is saying

StartIndex cannot be larger than length of string

Compare-Object ‘SQLServer’ ‘PowerShell’

The main difference that I can see when using SUBSTRING() in SQL Server versus in PowerShell is that SQL Server is very forgiving.

If you have a string that is 20 characters longs and you ask for everything from the 5th character to the 100th character, SQL Server is going to look at this, see that the string does not go to the 100th character, and just give you everything that it can.

SQLServer_Substring

PowerShell on the other hand, while being amazingly forgiving with some things….

Examples:

  • "a" + 2 =  a2
  • "a" * 2 = aa
  • 2 + 2 = 4
  • "2" + 2 = 22

…is surprisingly less forgiving than SQL Server here.

If we checked the length of the results we can see the length of each individual row:

foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
  RowName = $row
  RowLength = $row.Length
 }
}
PowerShellStringLength
As you can see, none of these are near 100

So PowerShell goes to find the 5th to the 100th character, sees that the 100th character is outside the length of the string, and freaks out!

The PowerShell Hammer…

…can also be a PowerShell Scalpel as well. You can get as precise as you need to and in this case, with the error complaining about the length, we should probably be more specific about the length we want.

So let’s get more specific about the length! Now we could go and input all the different values for substring function but let’s get a bit more dynamic about it.

It is PowerShell after all…


#...now check the substring function...
#...with proper values...
foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
    RowName = $row
    RowSubString = $row.Substring(5, ($row.Length) - 5)
  }
}

PowerShellSubstringWorks
I should probably be more concise with my T-SQL scripts too

So there we go, SQL Server substring and PowerShell substring are basically the same. We just have to be concise about it!

Update: 2017-08-15

Thanks to Michael Villegas ( blog | twitter ) for pointing out in the comments that PowerShell has a simpler syntax to deal with this.

While SQL Server requires 3 arguments for the substring function (expression, start, length); PowerShell has the same thing but it also has a simpler syntax for getting the characters from a starting point all the way to the end.

#...simpler syntax...
foreach ($row in ($dbResults.name)) {
  [PSCustomObject]@{
    RowName = $row
    RowSubString = $row.Substring(5)
  };
};

PowerShellSubstringWorksSimpler

 

The more you know… 🙂

 

TSQL Tuesday #93: Interviewing Patterns & Anti-Patterns.

TSQL Tuesday, the brain-child of Adam Machanic ( blog | twitter ), has come around once more and this time it is being hosted by Kendra Little ( blog | twitter ). The topic? Interviewing Patterns & Anti-Patterns.

tsql2sday150x150

Truth be told…

… I have not had that many interviews. A combination of not being that long in the working community since college and staying at the same company for quite a while means that it’s just not something at which I’ve had a lot of practice.

I suppose that I should do one or two, if not to look for a new place to work, then to practice them, see how I measure up, and test my skill.

Mainly though to answer some questions that I have.
Questions about interviewing for DBAs.

I hope you don’t find them too boring or basic.

How do you interview a DBA?

This question is one that I couldn’t really wrap my head around. How do you interview a DBA? If the purpose of an interview is to evaluate a candidate for a position then how do you measure them?

Technical wise, what do you do? Most of the interviews that I’ve been to have involved some aspect of testing, but the thing is there are different types of DBAs, all to do with what they focus on.

Do you judge a DBA, who is focused on Virtualisation, on the intrinsics of SQL Internals?
Or a DBA, focused on Azure, on their knowledge of SQL Server 2005 and when certain T-SQL functions came in?
A company who is looking for a database design expert is going to focus on that and may not care about a DBA’s expertise in HA/DR options.

How do you ensure that you are adequately testing the competency of a DBA?

How do you interview a company?

Interviews go two ways though, and companies can be more wrong that right (it happens).
The question here is when you run into a company with the wrong beliefs, what do you do?

If the company interviews you and says that you’re wrong in saying that TRUNCATE TABLE can be rolled back, what do you do?
If they say that index rebuilds doesn’t update index statistics on the columns in the index, again what do you do?

What do you do if they won’t listen, if they won’t look at any examples, if they won’t see reason when given proof to the contrary?

How do you deal with a company that is incorrect in their basic assumptions and unwilling to learn?

They say that the DBA role is changing…

…and that we, as DBAs, have to learn to change with it otherwise we’ll get left behind.

A concern for me is that maybe the way that we interview DBAs isn’t right, and that it needs to change or it, too, will get left behind.

Unfortunately, like most things, I don’t have the answer yet…

I’m learning though…

 

ARIGHTABORT-ing & Anti-ANSI_WARNINGS

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.

Just check out the Microsoft Docs and what it has to say about it:

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:


USE Pantheon;

-- Create our test table...
CREATE TABLE dbo.ArithAborting (
    id tinyint NULL
);
GO

And our attempt at inserting that value into the table:

SET ARITHABORT ON;
GO
SET ANSI_WARNINGS ON;
GO
-- Check can we insert a "divide by 0"...
BEGIN TRY
INSERT INTO dbo.ArithAborting (id) SELECT 1/0;
END TRY
BEGIN CATCH
  PRINT 'NOPE!';
  THROW;
END CATCH;

And we get our good, old, dreaded friend:

DivideByZeroError
Terminate!

We check our ArithAborting table and nothing is there, like we expected!

SELECT *
FROM dbo.ArithAborting;
EmptyTable
I got nothing…

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;
GO
SET ANSI_WARNINGS OFF;
GO
-- ...insert into our table...
BEGIN TRY
  INSERT INTO dbo.ArithAborting (id) SELECT 1/0;
END TRY
BEGIN CATCH
  PRINT 'NOPE!';
  THROW;
END CATCH;
DivideByZeroWarnings
Termin-wait…

Now before I freak out and start thinking that I’ve finally divided by zero, let’s check the table:

NULLTable
I got NULL-ing

What’s going on here? Checking the docs

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?

Nope!

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.

 

 

DBA Fundamentals August 2017

What’s On?

DBA Fundamentals VG has 3 different sessions ready for you all this August, with 2 sessions from our American Group and 1 from our down-under group.

If any of these sessions catch your eye, sign up here: DBA Fundamentals

Sessions

Damien Widera ( blog | twitter )

“SQL Server 2017 – What’s New?”

2017-08-08, 12:30 – 13:30 Brisbane

Damien would like to show you the most interested features you can find in SQL Server 2017. He will briefly talk about SQL Server on Linux (Security), how to use Python scripts, Adaptive query processing and many more.
Very demo heavy.


 

Warner Chaves ( blog | twitter )

“Azure SQL Data Warehouse for the SQL Server DBA”

2017-08-08, 11:00 – 12:00.

The power of cloud storage and compute power has made data warehousing possible for businesses of all sizes. What was once a large capital expenditure and multi-year implementation can now be deployed and ready to use within minutes and allow any organization to collect, query and discover insights from their structured data sources. With a full T-SQL interface and compatibility with the rest of the Microsoft data stack, Azure Data Warehouse can fit transparently into your business data strategy and leverage already existing and familiar development and management skills. In this session we will look at the main concepts of the Azure SQL Data Warehouse service, how it’s different than SQL Server and the advantages it provides to an on-premises solution.


 

David Klee ( blog | twitter )

“Linux for the SQL Administrator”

2017-08-22 , 11:00 – 12:00

I bet never in your career you thought SQL Server would run on anything other than Windows. Times are changing, and are you ready? SQL Server on Linux is right around the corner! What is Linux – really? Why run your mission-critical databases on an unfamiliar operating system? This introductory session will help seasoned SQL Server DBA understand the basics of Linux and how it is different from Windows, all the way from basic management to performance monitoring. By the end of this session, you will be able to launch your own Linux-based SQL Server instance and get hands-on with this paradigm shift to see if this new platform is right for your organization.

Other Items

We’re continuing work on getting a list of past presenters up on our website. It’s a slow, deliberate progress but it’s ongoing.

You can check it out here: Past Presenters

Apart from that, anything you want to get in touch about, hit us up on Slack or Twitter!

YouTube Keyboard Shortcuts

As SQL Server related as YouTube is…so depends on what you watch I guess

By the time this goes to post 24 Hours of PASS will have just finished (it was great!) and PASS ( twitter | blog ) will be busy converting all the recording of the sessions and putting them up so we can watch them again.

This made me realize how much resources are available up on YouTube (I use it a lot for learning but there is a difference between doing and realizing). Not just with 24 Hours of PASS but with the Local Groups, Virtual Groups, and even some individuals have started to use it as well (Robert Cain, Bert Wagner, Rob Sewell, etc.)

Here are a few of the YouTube shortcuts that I use while watching these videos:

Go To Percentage of the Video:

Press a number to go to that percentage of the video…
1 = 10%,
2 = 20%,
3 = 30%,
etc.

Funnily enough, I found this by pressing the keys at the top of my keyboard, sequentially going up and getting to the end of the video. Pressing 8 brought me to 80%, 9 brought me to 90% and 0, thinking it would end the video, brought me back to the start again!

So 0 = 0%!

“Home” and “End” go to the start and end of the video respectively.

Pause the video…
“K” or “Spacebar”.

Say that you want more control though, and don’t want to go to a certain percentage of the video, but just skip a few sections…

Forwards and backwards….
“Left arrow” = Back 5 seconds,
“J” = Back 10 seconds…
“Right arrow” = Forward 5 seconds…
“L” = Forward 10 seconds.

Video too loud for you?

Volume Control…
“Up arrow” = Increase volume,
“Down arrow” = Decrease volume.

Not enough time in the day?

Speed up the playback
“Shift” + “.”

Then when you’ve encountered it at full speed and if you’re like me, your Imposter-Syndrome kicks in and you start freaking out at how eloquently the presenters are speaking and how fluidly they can type, let’s slow it back down again.

Slow down the playback
“Shift” + “,”

Having trouble hearing?

Turn on Subtitles/Closed Captions
“C”

Subtitles too small? You can adjust them.

“+” = Increase the subtitles font
“-” = Decrease the subtitle font

That’s it! Check these out an up your YouTube watching game.
Also check out those links, there’s more informative videos coming every day!

Exporting Special Characters out of SQL Server using PowerShell.

PowerShell is ußer-useful!

So I’ve talked before about keeping new lines when copying results to a different window in SQL Server and about copying new lines out of SQL Server into reports.

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.

The Lay-out.

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…

EncodingIssue
My german is non-existant but I know that’s wrong!

“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!

Troubleshooting
What we want…

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!

Encoding
Looks like ASCII is not for me!

So we have to define an “Encoding” do we? I used “UTF8” and modified my query…

EncodingIssueResolved
Eszett? More like EZ-zett!

And special characters are no longer an issue for us 🙂