Problem accessing SQL Server from PowerShell
11,993
Solution 1
Make sure the Assembly containing the Microsoft.SqlServer.Management.Common.ServerConnection type (which I think is Microsoft.SqlServer.ConnectionInfo) is loaded first:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
Solution 2
For me works with
Import-Module -Name SqlServer
It could stay in script NOTE:
Windows Server 2016
Before I have installed
Install-Module -Name SqlServer
- With [Reflection.Assembly]::LoadWithPartialName works only on windows 10 for me.
Comments
-
Rana Hamza almost 2 years
I'm trying to implement a backup management script i found at http://sev17.com/2011/03/restore-and-relocate-database-files-using-powershell/
The article says it depends on SQLPSX version 2.3.2.1 or higher - I have the most current version.
attempting to execute this line:
$server = get-sqlserver $sqlserver
results in:
New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: make sure the assembly containing this type is loaded. At C:\Users\...\Documents\WindowsPowerShell\Modules\sqlserver\SQLServer.psm1:68 char:24 + { $con = new-object <<<< ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver }
result of get-module -listAvailable
ModuleType Name ExportedCommands ---------- ---- ---------------- Script adoLib {} Manifest Agent {Get-AgentTargetServerGroup, Get-AgentProxyAccount, Get-AgentJobSchedule... Script ISECreamBasic {} Script mySQLLib {} Script OracleClient {} Script OracleIse {} Script PBM {} Script PerfCounters {} Manifest Pscx {} Manifest Repl {Get-ReplEnumSubscriptions2, Get-ReplPublisherMonitor, Get-ReplEnumPubli... Manifest ShowMbrs {Get-ShowMbrs, Set-ShowMbrs, New-ShowMbrs, Get-GroupUser} Script SQLIse {} Manifest SQLMaint {Get-SqlIndexFragmentation, New-UserMember, Invoke-SqlIndexRebuild, Get-... Manifest SQLParser {Test-SqlScript, Out-SqlScript} Script SQLProfiler {} Script SQLPSX {} Manifest sqlserver {Get-SqlScripter, Get-SqlIndexFragmentation, Remove-SqlServerRoleMember,... Manifest SSIS {New-ISItem, Get-ISPackage, Get-ISItem, Copy-ISItemFileToSQL...} Manifest WPK {} Manifest AppLocker {} Manifest BitsTransfer {} Manifest PSDiagnostics {} Manifest TroubleshootingPack {} Manifest WebAdministration {}
RE: SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
results:
True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: make sure the assembly containing this type is loaded.
-
MrKWatkins almost 13 yearsDoes it produce any output? You should see some info about the assembly. If it doesn't produce any output then you don't have that assembly in your GAC, meaning it won't be able to load it and therefore won't be able to load the ServerConnection type, giving you the error above.
-
Rana Hamza almost 13 yearsTrue v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\10.0.0.0__89845...