2

I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system. The server is using a non default port number and is in a cluster. Before it was put in a cluster, it was a stand alone box and the job ran with no issues with the non default port number. It is failing at step 3, which is

(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()

I have already addressed an issue of using the computer node name instead of the virtual server name as we have other servers in clusters.

The only difference with this setup is that it is not using the default port of 1433. The server is already set to receive remote connections.

Here is the error.

The job script encountered the following errors. 
These errors did not stop the script:  
    A job step received an error at line 1 in a PowerShell script. 
    The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()'. 
    Correct the script and reschedule the job. 
    The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Could not connect to 'VirtualServer\DEFAULT'. [Failed to connect to server VirtualServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
    The server was not found or was not accessible. 
    Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
Improve this question

1 Answer 1

Reset to default
2

You will need to put the port number in there. When using the default instance the SQL Browser is not contacted in order to get the port number to use to connect. Because of this you have to specify the port number when connecting otherwise the OLE/ODBC driver doesn't know what TCP port number to connect to.

Please clarify how you took a standalone server and made it a cluster. That isn't supported unless you uninstalled SQL and reinstalled it.

Improve this answer
6
  • Yes, we backed everything up and uninstalled SQL Server and reinstalled. Where exactly do you put the port number? I have tried this and it did not work. (Get-Item SQLSERVER:\SQLPolicy\VirtualServer,5000\DEFAULT).EraseSystemHealthPhantomRecords()
    – Jeremy
    Dec 13, 2011 at 13:49
  • I'm not sure what the syntax in powershell would be. I would think that (Get-Item SQLSERVER:\SQLPolicy\VirtualServer:5000\DEFAULT).EraseSystemHealthPhantomRecords(‌​) would work.
    – mrdenny
    Dec 13, 2011 at 16:54
  • VirtualServer:5000 did not work. I have pretty much no knowledge of powershell. I appreciate your help so far.
    – Jeremy
    Dec 13, 2011 at 17:03
  • The only thing I can think of would be to use an alias to force the driver to the correct port number. Run cliconfg and configure an alias for the port number.
    – mrdenny
    Dec 13, 2011 at 17:17
  • 1
    I was able to find it after much searching. I needed a tick mark in the command. (Get-Item SQLSERVER:\SQLPolicy\VirtualServer`,5000\DEFAULT).EraseSystemHealthPhantomRecords() Again, I thank you for your help.
    – Jeremy
    Dec 13, 2011 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.