0

EDIT 1 : I am able to create replication objects successfully via Windows PowerShell ISE but NOT PowerGUI. The script used to run successfully on my 32 bit machine and now errors on my 64. Issue looks to be an environmental issue.

I'm attempting to connect to my distribution SQL Server instance using SQL Authentication from powershell and create a replication object. I am unable test connecting using Windows Authentication.

The following code which originates from http://sqlpsx.codeplex.com/ and the loaded module Repl (Repl.psm1). 

$sqlServer  = "SQL Server"
$Username   = "UserName" 
$Password   = "Password"

[Microsoft.SqlServer.Management.Common.ServerConnection]$con = Get-SqlConnection $sqlServer $username $password

Write-host "Get-ReplServer $($con.ServerInstance)"

$repl = new-object ("Microsoft.SqlServer.Replication.ReplicationServer") $con

or more specifically 

new-object ("Microsoft.SqlServer.Replication.ReplicationServer") $con

generates the following error 

New-Object : Cannot find an overload for "ReplicationServer" and the argument count: "1".
At line:25 char:19
+ $repl = new-object <<<<  ("Microsoft.SqlServer.Replication.ReplicationServer") $con
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

The server connection code

[Microsoft.SqlServer.Management.Common.ServerConnection]$con = Get-SqlConnection $sqlServer $username $password

completes successfully and returns correct server information

The SQL server version is

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
    Jun 28 2012 08:36:30 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

and PowerShell version 

Major  Minor  Build  Revision
-----  -----  -----  --------
2      0      -1     -1

My issues appears to be the same as Issue with connecting to ReplicationServer with sql authentication. However the solution from Chad Miller does not resolve my issue i.e.

$con.Connect()
$repl = new-object ("Microsoft.SqlServer.Replication.ReplicationServer") $con

Any ideals would be much appreciated.

Community
  • 1
  • 1
Pixelated
  • 1,531
  • 3
  • 15
  • 35

1 Answers1

0

Try this out and see if it works for you:

[Microsoft.SqlServer.Replication.ReplicationServer] $repl = New-Object Microsoft.SqlServer.Replication.ReplicationServer($Con)

Edit: If it is failing to connect to the server that sounds like an underlying issue. Can you $con.serverinstance.connect() without an error? Perhaps connect first and then try and create the replicationserver?

Or perhaps create the ReplicationServer object without a connection and assign the connection after the fact ala:

[Microsoft.SqlServer.Replication.ReplicationServer] $repl = New-Object Microsoft.SqlServer.Replication.ReplicationServer()
$repl.connectioncontext = $con
TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
  • Hi, unfortunately I get the same error "New-Object : Cannot find an overload for "ReplicationServer" and the argument count: "1".". I also tried appending ServerInstance to $con ie $con.ServerInstance which I suspect is futile but returns a slightly different error message all the same "New-Object : Exception calling ".ctor" with "1" argument(s): "Failed to connect to server ."" – Pixelated Mar 31 '14 at 17:14
  • $con.serverinstance.connect() errors with Method invocation failed because [System.String] doesn't contain a method named 'connect' and the second suggestion after I add the additional $con gives me the original error. Interestingly I get the same error whenever I attempt to create any replication object i.e. New-Object "Microsoft.SqlServer.Replication.ReplicationServer" "Microsoft.SqlServer.Replication.ReplicationMonitor", but the server connection object new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") doesn't – Pixelated Apr 01 '14 at 05:51