none
Trouble running PowerShell "Failed to Connect to server".

    Question

  • Hello,

    I'm still a noob in PowerShell, I have created a script that runs smoothly on Windows Server where I have built it but it doesn't run on any other server and giving me the below error. It is failing to load some kind of SMO assemblies.

    The problem is coming from below lines:

    Add-Type -Path 'D:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 

    Error:

    Just to clarify, on the server where the script is running smoothly I have not entered any credentials, just running the script as is on both servers but the problematic server is spitting "Failed to connect to server" error.

    Now the question is whether the PowerShell version can be an issue in this case? The PowerShell version on the Server where the script is running smoothly is Higher and it is a lower version on servers where I'm getting the error.

    Another difference is there is only once instance of SQL Server where the script is running fine whereas the problematic server has more than one SQL Server instances.

    Any suggestion will be appreciated.




    • Edited by Shahid_Ali Tuesday, February 12, 2019 6:00 PM
    Tuesday, February 12, 2019 4:43 PM

All replies

  • You will have to connect to each named instance. 

    https://dba.stackexchange.com/questions/37471/how-to-connect-powershell-to-the-sql-server-named-instance

    Tuesday, February 12, 2019 6:17 PM
  • Thank for your reply, below is the code I'm using to connect with the SQL Server, for some reason the connection is not working. I also tried the code from your provided link but that is also not working:

    $serverName="Test"  
    $serverName |ForEach-Object {
    $ServerName=$_.ServerName$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
    Foreach($Database.Name) in $SQLServer.Databases)
    {Do whatever}

    Error:

    The following exception occurred while trying to enumerate the collection: "Failed to connect to server ..".

    At C:\test.ps1:46 char:23

    +  Foreach($Database in $SQLServer.Databases)

    +                       ~~~~~~~~~~~~~~~~~~~~

        + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException

        + FullyQualifiedErrorId : ExceptionInGetEnumerator

    Thanks.

    Tuesday, February 12, 2019 8:06 PM
  • I don't have a SQL server to test with so I'm sorry that I can't directly answer your question.

    See https://stackoverflow.com/questions/7516337/powershell-list-all-sql-instances-on-my-system

    Try this and see what it produces.

    # Let's first list off all installed instances. 
    "Here are the instances we found."
    (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
    ""
    # Now lets get a count of databases.
    (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances |ForEach-Object {
    $SqlInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $_
    "Instance {0} has {1} databases defined." -f $_, $SqlInstance.Databases.count
    }
    

    Tuesday, February 12, 2019 9:14 PM