Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)]
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 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 '11 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 '11 at 17:17
    
I was able to find it after much searching. I needed a tick mark in the command. (Get-Item SQLSERVER:\SQLPolicy\VirtualServer`,5000\DEFAULT).EraseSystemHealthPhantomRecord‌​s() Again, I thank you for your help. –  Jeremy Dec 13 '11 at 17:21
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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