SQL Server 2008 and PowerShell
in [Server Configuration]
|
Prev: Cannot restart SQL Server service
Next: Visual Studio 2008 from SQL 2008 installation media and templa
From: PatB on 29 Dec 2008 08:29 I'm new to SQL Server 2008 and having trouble with a job that was installed with SQL called syspolicy_purge_history. Step 3 uses a powershell command and is failing. The powershell command is: (Get-Item SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords() The error returned is: Executed as user: DOMAIN\SQLServerAgent. 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\DAVYJONESQL\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 'DAVYJONESQL\DEFAULT'. [Failed to connect to server DAVYJONESQL. --> 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)] Failed to connect to server DAVYJONESQL. 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) ' A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT' because it does not exist. ' A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'You cannot call a method on a null-valued expression. '. Process Exit Code -1. The step failed. I'm new to SQL 2008 and to PowerShell. Can anyone give me any direction as to where to begin debugging why this is happening? -- Pat B BCC Software, Inc A BÖWE BELL + HOWELL COMPANY
From: Geoff N. Hiten on 29 Dec 2008 09:41 This is not so much a PowerShell error as it is an ordinary connection error. PowerShell uses Windows Authentication to connect to the target. Either you system cannot resolve the target name or your login does not have permission to connect. Since this is SQL Agent, I suspect the owner of the job (which is where the default permissions come from) does not have permission. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "PatB" <PatB(a)community.nospam> wrote in message news:6D72C564-CED8-4D12-BF8C-440A604EF016(a)microsoft.com... > I'm new to SQL Server 2008 and having trouble with a job that was > installed > with SQL called syspolicy_purge_history. Step 3 uses a powershell command > and is failing. > > The powershell command is: (Get-Item > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords() > > The error returned is: > > Executed as user: DOMAIN\SQLServerAgent. 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\DAVYJONESQL\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 > 'DAVYJONESQL\DEFAULT'. [Failed to connect to server DAVYJONESQL. --> 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)] Failed to connect to server > DAVYJONESQL. 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) ' A job step > received an error at line 1 in a PowerShell script. The corresponding line > is > '(Get-Item > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. > Correct the script and reschedule the job. The error information returned > by > PowerShell is: 'Cannot find path > 'SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT' > because it does not exist. ' A job step received an error at line 1 in a > PowerShell script. The corresponding line is '(Get-Item > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. > Correct the script and reschedule the job. The error information returned > by > PowerShell is: 'You cannot call a method on a null-valued expression. '. > Process Exit Code -1. The step failed. > > I'm new to SQL 2008 and to PowerShell. Can anyone give me any direction > as > to where to begin debugging why this is happening? > > -- > Pat B > BCC Software, Inc > A BÖWE BELL + HOWELL COMPANY
From: PatB on 29 Dec 2008 15:25
Hmmm..... The owner is SA. I didn't create this job. It was part of the SQL Install. SA should have permissions to run it. However, just for fun I changed the owner to my domain login which has the sysadmin role and it still failed with the same error. Additionally, I tried to run the same command in PowerShell logged in as me (sysadmin) and got the same error. Any other thoughts? Thanks for the help. -- Pat B BCC Software, Inc A BÖWE BELL + HOWELL COMPANY "Geoff N. Hiten" wrote: > This is not so much a PowerShell error as it is an ordinary connection > error. PowerShell uses Windows Authentication to connect to the target. > Either you system cannot resolve the target name or your login does not have > permission to connect. Since this is SQL Agent, I suspect the owner of the > job (which is where the default permissions come from) does not have > permission. > > -- > Geoff N. Hiten > Principal SQL Infrastructure Consultant > Microsoft SQL Server MVP > > > > > > "PatB" <PatB(a)community.nospam> wrote in message > news:6D72C564-CED8-4D12-BF8C-440A604EF016(a)microsoft.com... > > I'm new to SQL Server 2008 and having trouble with a job that was > > installed > > with SQL called syspolicy_purge_history. Step 3 uses a powershell command > > and is failing. > > > > The powershell command is: (Get-Item > > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords() > > > > The error returned is: > > > > Executed as user: DOMAIN\SQLServerAgent. 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\DAVYJONESQL\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 > > 'DAVYJONESQL\DEFAULT'. [Failed to connect to server DAVYJONESQL. --> 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)] Failed to connect to server > > DAVYJONESQL. 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) ' A job step > > received an error at line 1 in a PowerShell script. The corresponding line > > is > > '(Get-Item > > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. > > Correct the script and reschedule the job. The error information returned > > by > > PowerShell is: 'Cannot find path > > 'SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT' > > because it does not exist. ' A job step received an error at line 1 in a > > PowerShell script. The corresponding line is '(Get-Item > > SQLSERVER:\SQLPolicy\DAVYJONESQL\DEFAULT).EraseSystemHealthPhantomRecords()'. > > Correct the script and reschedule the job. The error information returned > > by > > PowerShell is: 'You cannot call a method on a null-valued expression. '. > > Process Exit Code -1. The step failed. > > > > I'm new to SQL 2008 and to PowerShell. Can anyone give me any direction > > as > > to where to begin debugging why this is happening? > > > > -- > > Pat B > > BCC Software, Inc > > A BÖWE BELL + HOWELL COMPANY > > |