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

 

Copying New Line Data out of SQL Server

 

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

Simples!…right?

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…


Karaoke…

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.

CopyingNewLineTwoLines
SQL Server is left, Report is right

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?

1 Way:

Well, depending on what tool you have, the answer could be as simple as a right-click and selecting “Open in Excel”

RedGate_OpenInExcel
Intact but on 1 line 🙂

Or Another:

Let’s proceed with the impression that you do not have RedGate tools (cough free trial cough) and cannot avail of the right-click righteousness, what do you do then.

Well…have you thought about PowerShell?

Hear me out on this but you probably already have your query but found the new lines are screwing up the report. So let’s throw that query into a variable

$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'

Then what we have to do is somehow connect to the SQL Server instance and database.

Let’s go with the very basics here as that’s all we really need. Invoke-SqlCmd, and yes I know it has problems. I’ve linked and talked about them before. It works for us in this situation though.

FirstResults
Yup, that’s good old VS code!
Now the more code-centered readers among you may have spotted and asked why I used -ExpandProperty and not just -Property , or even why I included it at all.
Well, apart from the thought that code online should be like code in scripts (legible with no aliases), we’re dealing with new lines here!
If we don’t specify ​-expand then what we actually get is…
SecondResults
comma delimited or ellipses delimited?

How does that help us with Reports?

If you work with PowerShell for the smallest amount of time, then I hope you’ve run into the command Export-CSV. See help for details…

help Export-Csv -Full

This will output a delimited file (defaults to comma but we can change that if we want) to wherever we want. We can then open it up in Excel or whatever other tool you use.

Let’s see if that splits our information into a new line!

ThirdResult
IT’S ALIVE!!! ugh I mean…IT WORKS!!!

Another another…

There are tons of different ways to do this but this is what I used.

Quick, dirty, and effective.

In the short term, I’m okay with that!

 

Table Column Differences with T-SQL and PowerShell – Part 2

If this was a horror movie, it would be called “The Differencing”…duh duh duh!

The original post for this topic garnered the attention of a commenter who pointed out that the same result could be gathered using a couple of UNION ALLs and those lovely set-based EXCEPT and INTERSECT keywords.

I personally think that both options work and whatever you feel comfortable with, use that.

It did play on my mind though of what the performance differences would be…what would the difference in STATISTICS IO, TIME be? What would the difference in Execution Plans be? Would there even be any difference between the two or are they the same thing? How come it’s always the things I tell myself not to forget that I end up forgetting?

I have no idea about the last one but at least the other things we can check. I did mention to the commentor that I would find this an interesting blog topic if they wanted to give it a go and get back to me. All I can say is – Sorry, your mail must have got lost in transit. I’m sure it is a better blog post that mine anyway.

If you’re going to do it…

For this test, we’re not going to stop at a measely 4 columns per table. Oh no! For this one we’re going to go as wide as we can.

With a recent post by Kenneth Fisher ( blog | twitter ) out about T-SQL FizzBuzz, I’m going to create two tables, both of which will have incrementing column names i.e. col00001, col00002, …, col1024. Table1 will have all columns divisible by 3 removed while Table2 will have all columns divisible by 5 removed.

See, FizzBuzz can be useful!

So our table creation scripts…

SELECT TOP (1024)
    CASE WHEN v.number = 0
      -- Change this to 02 the second run through
THEN N'CREATE TABLE dbo.TableColumnDifference01 ('
    ELSE N' col' + RIGHT(REPLICATE('0', 8) + CAST(v.number AS nvarchar(5)), 4) + N' int,'
    END
FROM master.dbo.spt_values AS v
WHERE v.type = N'P'
AND (
-- Change this to '% 5' the second run through
v.number % 3 != 0
OR v.number = 0)
FOR XML PATH('')
TableCreationScript
See Note

NOTE: When you copy and paste the results of this query into a new window to open it, it is going to fail. Why? Well the end of the script is going to be along the lines of colN int, and it needs to be colN int). Why is it like this? Well it was taking to damn long to script that out. Feel free to change this to work for you. Hey if you do, let me know!

Now, how I’m going to do test this, is run each method 3 times (PIVOT, UNION, and PowerShell), then measure the third run of each method. This is mainly as I want to get rid of any “cold cache” issues with SQL Server where the plan has to be compiled or the data brought into memory.

…do it Pivot

So first up is the Pivot method from the last blog post. In case you’re playing along at home (and go on, do! Why should kids get all the fun) here is the code that I’m running.

And here is our results:

PivotMethodGridResults
Yup, those be columns

What we are really after though is the stats, execution plan and time to complete for our 3rd execution. Now as much as I love reading the messages tab for the stats information, I feel with blog posts that aesthetics is king, so I’m going to be using the free tool by Richie Rump ( twitter ) “Statistics Parser

Stats:

PivotMethodGridStats
Elapsed time: 00:00:00.136

 

Execution Plan:

PivotMethodGridPlan
Probably the first plan I’ve seen where the SORT isn’t the most expensive!

..do it UNION

Secondly we have what I dubbed “the UNION method” (no points for figuring out why) and the only change I’ve made to this script is to add in PARSENAME() and that’s only so that the script would..you know…work.

Results be like:

UnionMethodGridResults
Yep, Yep, Yep, Yep, Nope, Yep…

Stats:

UnionMethodGridStats
Elapsed time: 00:00:00.624

hmm…less Scan Counts but 5 times the reads…also 5 times slower than the PIVOT method. Maybe the execution plan will be prettier?

Execution Plan:

UnionMethodGridPlan.png
ehh…WHAT!

Yeah…so…that’s…that’s different from the first plan! I was right in my comment though, there is a concatenation operator (there’s actually 2, you may need to zoom in to find them though)

…do it PowerShell

Finally we have the PowerShell method. No messing about here, let’s get straight to it! I’m going to lump all the code together in one gist and I’ll be wrapping it in Measure-Command to get the speed of the command.

Get-Results

PoSHMethodGridResults
Yeah I’m liking VS Code more and more…

Get-Stats:

PoSHMethodGridStats.png
Elapsed time: 00:00:00.249

help *execution*; help *plan*

Would you believe that I couldn’t figure out how to get an execution plan for PowerShell 🙂

If anybody knows, hit me up!

Finishing off

You know at the start of this, I was fully expecting the PowerShell to win out, followed by the UNION method, because it’s use of UNION, EXCEPT, and INTERSECT which are basically made for this kind of problem, and the PIVOT method bringing up a distant last since PIVOTs have this complexity stigma attached to them and what is complex is normally slow.

From a sheer speed point of view, the actual results are:

  1. Pivot
  2. PowerShell
  3. Union

Who knew!?

I don’t think this is the end of my use of PowerShell or Union operators though. I’m not going to replace all the stuff that I can with Pivots. For one I just think that PowerShell and the Union operators are just too cool!

I actually like this result for two reasons.

  1. There are multiple way to do something in SQL, there are good ways and better ways. The main point is whatever option you choose, make sure you know what it entails and can justify it.
    Whatever works for you, works for you!
  2. You don’t know something, test it and find out! What you think the outcome may be, may not be true.

Now if you’ll excuse me, I want to figure out if there’s a way to return execution plans with PowerShell.

 

Why I *try* to help with dbatools?

Can I get a couple more hours in each day please?

This post started after I created a function for dbatools, was resurrected when talking to Chrissy LeMaire ( blog | twitter ) then died down again afterwards. At this stage, I figure I publish it now or I’ll never finish it.


2 fricking hours…

I’m a Junior DBA, and as one, I get given the graft work.

For me that meant manually checking the backups. Every single file of every single database of every single server, every single day…plus whatever other jobs and alerts had come in overnight.

As you can imagine, it took a while (OVER 2 HOURS!!!) and since my youth, I had leveled-up from ‘laziness’ to ‘efficiency’, so I wanted a better option.

I had heard about PowerShell as a language before and wanted to check out if it was possible to use it to help me out.

So I opened up my PowerShell ISE, rested my fingers on the keyboard and…nothing.

So I checked out solutions online, and it was there that I found dbatools.io!

They had everything – or what I thought was everything since they have an issues page in github with over 100 items – so one Friday night I downloaded their tools at home and started getting familiar with them. (I know, rock star lifestyle that I have).

Monday morning, bright-eyed, bushy-tailed and filled with coffee, I sit down at my computer, open up PowerShell to start my graft work, and in 2 minutes I was finished.

2 fricking minutes…

With that, I was hooked; Twitter account, followed, Slack channel, signed in, anything and everything I could do to learn more about this wonderful life-saving (I figure time is life ergo this was life-saving) tool I was in!

But it wasn’t enough… they had given to me and I had no method to repay them.

Then one day, I asked a question on PowerShell help and one of their members Constantine Kokkinos ( blog | twitter ) helped me, and we got chatting.

He gave me an enhancement request to look at and I spent 3 days looking, poking and prodding it until finally I gave up and did a replace to fix it.

I then proceeded to try and push my entire computer into their Git repository but CK laughed and help me fix that too.

Then, from nowhere from my point of view, Chrissy LeMaire said she like it and, like that, it was in!

I’ve done more stuff since then, one more enhancement that wasn’t accepted (no worries) and a command that was accepted (that I am ashamed about since I think it’s not good enough), but I am constantly thankful for the work that they do and the knowledge that they impart.

2 commits later…

I know, I know, here I am trying to wax lyrical about dbatools when I’ve only done 2 commits.

What can I say, I’ve slowly gotten busier and busier to the point that I’m trying to schedule my days to fit everything in (if you had told me earlier that I would become a “not enough hours in the day” guy…).

Does this mean that my love for dbatools has weaned? Not in the slightest! I’m still impressed every single time that I look in (seeing as that is every day, I’m spending a lot of my time being impressed) and I’m still trying to get back to it.

2 things left to say…

  1. To anyone hesitant about getting started with dbatools, whether that is helping out or using them, I urge you not to be.
    They are welcoming, warm, and inviting people who are happy to receive help from anyone willing to give it.
  2. I’ll eventually get around to fixing that issue Chrissy, I swear 😦