CODEWITHSUNDEEP
sql-serverpowershell
Keilaron
Keilaron

Reputation: 467

PowerShell conversion attempted with .NET class

I've read a few similar Q&As regarding PS and type conversions, but as far as I can see they're not the same situations.

I am attempting to use RMO classes in a Powershell script, but for some reason it thinks a conversion to the same type is necessary and fails to do so.

The code in question is basically:

$conn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" @($server, $dbUsernm, $dbPasswd);

$publicationDb = New-Object "Microsoft.SqlServer.Replication.ReplicationDatabase"
$publicationDb.Name = $dbName;
$publicationDb.ConnectionContext = $conn;

(A similar type error occurs if I try to use the two-argument constructor.)

The error is:

Exception setting "ConnectionContext": "Cannot convert the "(..snip..)" value of type
"Microsoft.SqlServer.Management.Common.ServerConnection" to type "Microsoft.SqlServer.Management.Common.ServerConnection"."

So what's going on here? It's clearly trying to convert to the same data types. These aren't defined in PowerShell scripts so shouldn't it be able to track the type? I've also tried casting the variable to [Microsoft.SqlServer.Management.Common.ServerConnection] in its declaration and/or in the calls / member set, to no avail.

In case it is relevant, I'm loading the RMO classes this way (which appears to be the only working method, even though from what I understand LoadWithPartialName is deprecated):

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO")

which says:

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.SqlServer.RMO\v4.0_14.0.0.0__89845dcd8080cc91\...

Does that mean it's v4.x of that class, or that it's a .NET 4.x class? If it is a .NET 4.x class, is that relevant in any way i.e. is that a problem for Powershell?

Upvotes: 1

Views: 144

Answers (1)

HAL9256
HAL9256

Reputation: 13523

TLDR: Summary of troubleshooting: @Keilaron had executed an Import-Module SqlServer earlier on in the PowerShell session which caused the odd behavior.

Personally, I wasn't satisfied that a simple restart of the PowerShell session fixed the issue, as this kind of error shouldn't happen. When I did some further digging, I think I discovered the root cause, and discovered that this is a bigger issue that could be easily missed.

First, the code to replicate the discovered behavior:

Import-Module SqlServer

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO")

$server = 'MyServer'
$dbuser = 'sa'
$dbPasswd = '1234'
$conn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" @($server, $dbUser, $dbPasswd);
$publicationDb = New-Object "Microsoft.SqlServer.Replication.ReplicationDatabase"
$publicationDb.Name = 'RandomDatabase'
$publicationDb.ConnectionContext = $conn;

Two key things happen:

  1. The Import-Module SqlServer loads the SqlServer .dll's included with the module, and not the GAC installed modules. This is by design, as the the module is not dependent on SQL Server being installed.
  2. The Microsoft.SqlServer.Rmo.dll is not a part of, or loaded with the SqlServer module, as there are no Replication commands in the SqlServer module. So to use the Replication commands, we have to manually load that .dll ourselves.

The two .dll's that we care about that the Import-Module SqlServer transparently imported were the two connection dependent .dll's from the SqlServer PowerShell module location:

[System.Reflection.Assembly]::LoadFile('C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SqlServer\Microsoft.SqlServer.ConnectionInfo.dll')
[System.Reflection.Assembly]::LoadFile('C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SqlServer\Microsoft.SqlServer.SqlClrProvider.dll')

--> Note: These .dll's were imported as 64 bit .dll's.

The RMO .dll that we had to manually import, come from the GAC, but essentially come from:

"C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.Rmo.dll"

Note: This is a 32 bit .dll. This is why we couldn't convert a "Microsoft.SqlServer.Management.Common.ServerConnection" to type "Microsoft.SqlServer.Management.Common.ServerConnection". Even though they are the same "type", as in name, their different bitness causes them to be incompatible.

Upvotes: 1

Related Questions