Pattern Matching with the PowerShell Switch Operator

Switching to switch if I have multiple if’s

Advertisements

Words: 646

Time to read: ~ 3 minutes

Date of writing: 2017-10-19

Continue reading “Pattern Matching with the PowerShell Switch Operator”

Importing Excel into SQL Server using PowerShell

Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!

Words: 797
Time to read: ~4 minutes

Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!

Continue reading “Importing Excel into SQL Server using PowerShell”

[PowerShell] Name Parameters When Using ForEach-Object…or else!

…or else you script may not work properly. That’s all, I’m not that scary.

Words: 853
Time to read: ~ 4.5 minutes

Continue reading “[PowerShell] Name Parameters When Using ForEach-Object…or else!”

[PowerShell] What Assemblies do I have?

Before I learned the joys of dbatools, I had to always run the depreciated [System.Reflection.Assembly]::LoadWithPartialName() method to make sure that I could access the SMO objects of SQL Server Instances and Databases.

Why did I have to do this?…

…well once, pre-SqlServer Module stage, I wanted to connect to my SQL Server Instance using SMO. However, when I ran New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArguementList "server name" I got an error message complaining that the assembly wasn’t loaded.

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

AssemblyError

There’s a potential problem…

…here because a lot of my custom function rely on Microsoft.SqlServer.Smo and if that assembly isn’t loaded then I’m going to have a bad time!

But how do we check if an assembly is loaded already?

AppDomain to the rescue!

So what assemblies do I have?


[appdomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

AssembliesSQLServerNotLoaded
Nice user friendly names, them…

Now that I know what assemblies I have loaded, I can see that Smo isn’t loaded. So I would probably load it using LoadWithPartialName().

You may have noticed that I mentioned “pre-SqlServer module” and “before […] dbatools“. This is because with these two modules, I don’t need to worry about assemblies anymore.

To quote the philosopher Bruno Mars, “don’t believe me, just watch!”

We’re going to use the SqlServer module to load the required assemblies for us.

First of all, let’s use a throw away cmdlet so that PowerShell can auto-load the module for us. Normally people would chose Get-SqlDatabase but it doesn’t matter. As long as it’s a cmdlet in the SqlServer module, then this is going to work.

In this case, I’m going to use Get-SqlAgent and throw the results away.


$null = Get-SqlAgent -ServerInstance 'localhost\SQLServer2K16';

 

GetSQLAgent
$null because we just don’t care…

Now, if we check if our Smo assembly is loaded…


[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -like '*SMO*';

SmoIsLoaded
Smo AND Smo.Extended?

2 result sets? How much assemblies does it load for us? Running our original assembly query check again, it seems to be a lot bigger than just 2 assemblies.

AssembliesSQLServerLoaded
eh…a lot

So what else gets loaded for us?

Opening a new PowerShell window, and throwing the results of our assemblies check into $PreLoad, we then call a SqlServer Module cmdlet. We then throw the results of our assemblies check into $PostLoad.


$PreLoad = [AppDomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

$null = Get-SqlAgent -ServerInstance 'localhost\SQLSERVER2K16';

$PostLoad = [AppDomain]::CurrentDomain.GetAssemblies() |
Sort-Object -Property FullName |
Select-Object -Property FullName;

Then we can use our good old Compare-Object, that we’ve used before, to see what else gets loaded for us


Compare-Object -ReferenceObject $PreLoad.FullName -DifferenceObject $PostLoad.FullName;

WhatGotAdded

Look at all those SqlServer and AnalysisServices goodies!

I probably wouldn’t have even known about them, and they get loaded for me! I have to check them out.

It is nice to know that you don’t have to manually load assemblies.
LoadWithPartialName() is deprecated and I don’t think anybody wants to memorize the FullName of the assembly to do it the Add-Type way.

Don’t worry though, you don’t have to anymore.

PowerShell got you covered 🙂

[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… 🙂

 

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 🙂