PowerShell conversion attempted with .NET class

Question

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?

How do you load the ConnectionInfo? i.e. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") — HAL9256
– HAL9256, Commented May 31, 2019 at 0:01
It isn't explicitly loaded. Do I have to load the dependencies manually too? — Keilaron
– Keilaron, Commented May 31, 2019 at 0:03
I tried adding it; It loads, but I still get the same exception. — Keilaron
– Keilaron, Commented May 31, 2019 at 13:47
I'm sorry, I can't replicate the issue. I had to load both assemblies in order to make the code work. I would try closing PowerShell and re-opening it, and try explicitly loading the .dll's because it may be a version mismatch, e.g. loading with partial name is loading from the GAC, and what is registered in the GAC. i.e.: load [System.Reflection.Assembly]::LoadFile('C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.Replication.dll') — HAL9256
– HAL9256, Commented May 31, 2019 at 15:00
Hold up -- Yours is in Program Files. Are you doing this on the system where MS SQL is installed? The idea for this is to be able to remotely do it from a CI agent, so I'm hoping MS SQL doesn't have to be locally installed. FWIW my version for ConnectionInfo says 2.0.50727.. — Keilaron
– Keilaron, Commented May 31, 2019 at 15:02

HAL9256 · Accepted Answer · 2019-05-31 18:01:25Z

1

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:

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

edited May 31, 2019 at 18:01

answered May 31, 2019 at 16:15

HAL9256

13.7k1 gold badge39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Keilaron Over a year ago

Specifically: I was unaware importing the module would affect the console running it after execution of the script ended, that it would cause version conflicts (or at least I'm guessing that was happening) despite explicitly loading other assemblies afterwards, and so on. Thanks again for the help!

HAL9256 Over a year ago

I did some more digging, and I believe that I discovered the root issue. See my edits above.

Keilaron Over a year ago

I appreciate it, though it turns out the fact I was ignoring I didn't have Replication is a problem: It only tries to load it once I call .Script() on a publication object.. So I'll have to find some way to get it w/o MS SQL installation (preferably).

Collectives™ on Stack Overflow

PowerShell conversion attempted with .NET class

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related