What’s in your $Profile?

Don’t be afraid.
Show me what you got!

Advertisements

Words: 332

Time to read: ~ 1.5 minutes

Continue reading “What’s in your $Profile?”

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 🙂