May 9, 2016 at 10:25 am
Comments posted to this topic are about the item Stairway to Server Management Objects (SMO) Level 1: Concepts and Basics
May 11, 2016 at 4:37 am
Bom dia Laerte
To add the assembly I usually go with Import-Module SQLPS, but there are also three versions of that module on my PC. How do I know which one is added?
To make scripts more portable, with the Add-Type I can do this:
$HighestSMOAssembly = [System.AppDomain]::CurrentDomain.GetAssemblies() | Where {$_.location -like '*smo\*'} | sort fullname -Descending | Select -ExpandProperty fullname -First 1
Add-Type -AssemblyName $HighestSMOAssembly
Do you know of something similar for Import-Module?
greetings,
Klaas
May 11, 2016 at 6:28 am
Thanks for the article.
May 11, 2016 at 9:34 am
KlaasV (5/11/2016)
Bom dia LaerteTo add the assembly I usually go with Import-Module SQLPS, but there are also three versions of that module on my PC. How do I know which one is added?
To make scripts more portable, with the Add-Type I can do this:
$HighestSMOAssembly = [System.AppDomain]::CurrentDomain.GetAssemblies() | Where {$_.location -like '*smo\*'} | sort fullname -Descending | Select -ExpandProperty fullname -First 1Add-Type -AssemblyName $HighestSMOAssembly
Do you know of something similar for Import-Module?
greetings,
Klaas
You can use Get-Module and this will output the path of the module. The function I use on my laptop is simply to match the version I want via the path.
So example for SQL Server 2014 the path to the module is:
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules
function Load-SQLModule {
[cmdletbinding()]
param (
$version = 130
)
Get-Module -ListAvailable -NameSQLPS | where {$_.path -match $version} | Import-module -DisableNameChecking | Out-Null
}
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 11, 2016 at 9:35 am
Bom dia Meu amigo 🙂
This code is pretty cool, but you run it without load the SMO it will not returns to you the highest one ..actually will not returns anything until you load something in your session.
Perhaps a way to load the highest one that still not loaded in your session ..we could search in the GAC and would be something like :
$HighestOne = (dir C:\Windows\assembly -recurse |
where {$_.name -eq 'Microsoft.SQLServer.SMO.dll'} |
sort fullname -desc |
Select -First 1).fullname
Add-Type -LiteralPath $HighestOne
On the Import-Module you can get the modules avaliable to load it with
Get-Module -listavaliable
I have 2 versions of SQL Server installed and in the $env:modulepath just show the path of first one I have installed and consquentely in the Get-module.
If you want the details of the module use Get-Module SQLPS | Get-Member..there is some information there
May 12, 2016 at 6:17 am
Thanks Shawn
While I'm playing with these modules, I see strange things:
I have the 110, 120 and 130 versions installed, though only SQL Server Engine 2014 (120), and no SSAS. I see 3 SQLPS modules and 3 SQLAScmdlets modules.
After Import-Module SQLPS, all 3 SQLAScmdlets are imported, but only version 120 of the sqlps, although both as Manifest and as a Script.
After Import-Module SQLAScmdlets, only version 120 is imported.
My intention was to automate my scripts so they would work on any SQL version, without knowing what version(s) will be available. I think I can insert the 'sort' and 'select first 1' in your function to always import the latest version, and skip the parameter. Or could there be reasons to choose for an older version?
Furthermore it's possible that the assemblies are available and the modules are not, so loading the assembly is still a bit more reliable.
May 12, 2016 at 7:28 am
Laerte
de fato
I forgot that Import-Module SQLPS is in my profile. If I remove that, the assemblies are not there.
Searching in the GAC is a better way.
Import-Module SQLPS seems to import all versions of SQLASCMDLETS (110, 120, 130), but only version 120 of sqlps.
But Get-Command -Module SQLASCMDLETS shows that only version 120 of each cmdlet is imported.
Version 130 is only imported when I filter the path and pipe that to Import-Module, like in Shawn 's function.
This is true for both SQLASCMDLETS and SQLPS.
Do you mean that you alter $env:PSModulePath manually to force one version as the available one?
May 12, 2016 at 8:36 am
Actually my friend, I have installed the other version of SQL Server in a VM to test for you, but I forgot to restart my VM to update the environment variable. Now it is showing the path to the other version and consquentely the Get-Module is showing too. ( I dont like the idea to force the Env variables)
In this case the Shawn function is pretty cool. It will search in the modules path and will load what you want.
I did some test and what looks like it is the order in the $env:psmodulepath that makes import-module by name to choose what to load. I´ve changed my variable and it load the 110 version .I am not sure about that and I already sent the question to the Jedi ones 🙂 When I have some feedback I will update you.
May 12, 2016 at 8:26 pm
Hey man, just got the confirmation. It is by the path order in the $env:psmodulepath that import-module load when using just the name, not the full path .
The Shanw´s function will works pretty good, perhaps just add a switch parameter to load the highestone..something like
function Load-SQLModule {
[CmdletBinding( DefaultParameterSetName='Version' )]
param(
[Parameter( Position=0,
Mandatory=$false,
ParameterSetName = 'Version')]
[string]
$version,
[Parameter( Position=1,
Mandatory=$false,
ParameterSetName = 'Highest')]
[Switch]
$HighestOne
)
If ($version) {
Get-Module -ListAvailable -NameSQLPS |
where {$_.path -match $version} |
Import-module -DisableNameChecking
} else {
Get-Module -ListAvailable -NameSQLPS |
Sort-Object -Property Path -Descending |
Select-Object -First 1 -ExpandProperty Path |
Import-module -DisableNameChecking
}
}
May 13, 2016 at 2:33 am
That's nice
I added some feedback, so I can see what module has finally been imported. When you choose a version that is not present, the function will do nothing, and not throw an error. By showing the imported module, I have a check.
Also: version 110 will set location to SQLSERVER:, so I finish with Set-Location C:\scripts. (Pop-Location would be even better, but that doesn't work)
For now, the switch is not used. For me it seems best to always load the highest version. Maybe sometimes there are reasons to load an older version, but I think it's not very meaningful to let the order of PSModulePath be decisive. So, "tell what version to import, or else find the highest one" seems enough logic to me.
function Load-SQLModule {
[CmdletBinding( DefaultParameterSetName='Version' )]
param(
[Parameter( Position=0,
Mandatory=$false,
ParameterSetName = 'Version')]
[string] $version,
[Parameter( Position=1,
Mandatory=$false,
ParameterSetName = 'Highest')]
[Switch] $HighestOne
)
If ($version) {
Get-Module -ListAvailable -Name SQLPS |
where {$_.path -match $version} |
Import-module -DisableNameChecking
"Imported Module:"
Get-Module -Name SQLPS | select ModuleBase
}
else {
Get-Module -ListAvailable -Name SQLPS |
Sort-Object -Property Path -Descending |
Select-Object -First 1 -ExpandProperty Path |
Import-module -DisableNameChecking
"Imported Module:"
Get-Module -Name SQLPS | select ModuleBase
}
Set-Location C:\scripts
}
May 13, 2016 at 3:38 am
May 13, 2016 at 4:24 pm
I had to change Listing 9 as follows. I upgraded powershell on my laptop to 4.0 version. But script was being run for Windows 2008 R2 Standard server and SQL Server 2008 R2.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Smo') | out-null
$MyServerInstance = "DeathStar"
$MyDatabase = "Alderaan"
$MyScriptPath ="$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\$($MyDatabase).sql"
$My='Microsoft.SqlServer.Management.Smo'
$s = new-object ("$My.Server") $MyServerInstance
$transfer = new-object ("$My.Transfer") $s.Databases[$MyDatabase]
$transfer.ScriptTransfer() | Out-File $MyScriptPath
May 13, 2016 at 5:48 pm
May 15, 2016 at 2:39 pm
good article
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2016 at 2:38 am
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply