Problem accessing SQL Server from PowerShell

Question 1

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

1 2	$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 }

1

2

3

4

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         {}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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")

1

2

3

4

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

1

2

3

4

5

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.

Question 2

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")

1 2	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

Problem accessing SQL Server from PowerShell

Question:

Answer:

Source:

Problem accessing SQL Server from PowerShell by stackoverflow.com licensed under CC BY-SA | With most appropriate answer!

Leave a Reply Cancel reply