Database replication on SQL 2022 via PowerShell

Question

If I configure replication manually using SQL Server Management Studio (GUI or query) everything runs fine, but with PowerShell, it does not work. I'm using a customized script that uses Microsoft .NET assemblies to configure replication. The assemblies list“

The same script is successfully running with other SQL server versions, except SQL 2022.

The error message I‘m getting is this:

$ReplicationServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SqlInstance

Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=16.200.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system 

cannot find the file specified.

    + CategoryInfo          : NotSpecified: (:) [New-Object], FileNotFoundException

    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.NewObjectCommand

In general, when I‘m trying to import a new object „Microsoft.SqlServer.Replication.ReplicationServer“ the MS.NET assemble starts looking for a later dll version (16.200.0.0) than it is installed by default (16.0.0.0).

Answer 1

I am trying to generate a replication scripts for all publications that uses powershell script calling through a SQL Agent job.

Code works in lower version, but i am getting below error for 2022.
Could you please help me alter the step!

A job step received an error at line 37 in a PowerShell script. The corresponding line is '$repsvr=New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $sqlserver

'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=17.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

'

Answer 2

Hi,

My problem was solved when I added import-module sqlserver to my Powershell script. It is not enough to have this module installed, it should imported at the begging of the script.

Hope this info helps

Answer 3

Hello Saulius,

Thank you for posting on the Microsoft Community Forum.

May I ask what kind of code you need to write? Please use the corresponding forum to ask questions about the code you're using.

If it's C# or C, you should go to the Q&A forum. Q&A - Microsoft Q&A

For other forums, you may need to inquire in forums related to coding. Our forum does not provide coding services. Free technical support on the forum does not include coding. Engineers in this forum generally do not provide coding services. We suggest consulting professional coding forums or websites.

I hope the information above is helpful to you.

If you have any questions or concerns, please feel free to let us know.

Best Regards,

Neuvi Jiang

Answer 4

got same error after upgrade from SQL 2019 to SQL 2022

#Reference RMO Assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | out-null

New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $sqlserver

New-Object : Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=17.100.0.0, Culture=neutral,

PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

At line:1 char:1

• New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $sqlse ...

• 
      + CategoryInfo          : NotSpecified: (:) [New-Object], FileNotFoundException 
  
      + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.NewObjectCommand
  

Answer 5

@Saulius Zebrauskas & @Prabhu ShankarM

Facing the same issue with Replication using PowerShell script.

Exception calling "LoadWithPartialName" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file

specified."