Creating a PowerShell Job Step Fails To Execute - Works from SQLPS Prompt

Question

I'm following the Technet steps documented in the article Run Windows PowerShell Steps in SQL Server Agent. It works when I run it from the SQLPS command line.

When I take this same unchanged code and place it in the Job Step List for SQL Server Agent to run it - I receive these errors complaining about the first line. Why is this a problem when using SSMS versus no problems with the SQLPS prompt >

A job step received an error at line 1 in a PowerShell script.

The corresponding line is 'CD \sql\localhost\default\databases'. Correct the script and reschedule the job.

The error information returned by PowerShell is: 'Cannot find path 'C:\sql\localhost\default\databases' because it does not exist. '

A job step received an error at line 2 in a PowerShell script. The corresponding line is '$db = get-item Echo'.

The error information returned by PowerShell is: 'Cannot find path 'C:\Windows\system32\Echo' because it does not exist. ' A job step received an error at line 3 in a PowerShell script.

The corresponding line is '$myview = New-Object -Typename Microsoft.SqlServer.Management.SMO.View -argumentlist $db "TestView2" dbo'.

The error information returned by PowerShell is: 'Exception calling ".ctor" with "3" argument(s): "SetParent failed for View 'dbo.TestView2'. " SetParent failed for View 'dbo.TestView2'. Value cannot be null. Parameter name: newParent '. Process Exit Code -1. The step failed.,00:00:00,0,0,,,,0

MY VIEW CREATION SCRIPT:

CD \sql\localhost\default\databases
$db = get-item Echo
$myview = New-Object -Typename Microsoft.SqlServer.Management.SMO.View -
argumentlist $db, "TestView2", dbo
$myview.TextHeader = "CREATE VIEW [dbo].[TestView2] AS"
$myview.TextBody = @"
SELECT drname.dr_id, drname.create_dt, dr_fname, dr_iname, dr_lname, 
drtitle, drsuffix, gender, birthdate, marital, ethnic, drname.email, 
org_name, dr_ofname, dr_oiname, dr_olname, nationalid, drname.medicare, 
drname.medicaid,

CASE
               WHEN EXISTS(SELECT 1 FROM stfstatu act WHERE act.dr_id = 
drname.dr_id AND fac_cd  NOT IN('SMC','WAR','CCS') AND act.active123 IN 
('1','3','N','G','6','S','U','CA','TPRO')) THEN 'Y'
ELSE 'N'
END  as Active_Status, 
              a.sch_type as Addr_Type_sch_type, a.addr, a.addr2, a.city, 
a.state, a.zip, a.phone, a.fax, a2.tax_id,
              (select top 1 rtrim(d.dr_lname) from drname d join address aa 
on d.dr_id = aa.dr_id join address2 aa2 on aa.link = aa2.l_address where 
d.verfac = 1 and aa2.tax_id = a2.tax_id and drname.verfac = 0) as 
Addr_DrPGp,
              lan_tab.txt as lan_tab_txt, dr_lan.lan_cd as dr_lan_cd, CASE 
WHEN dr_spec.specorder = 1 THEN dr_spec.specialty ELSE '' END as dr_spec, 
CASE WHEN dr_spec.PrimSpec = 1 THEN 'P' ELSE '' END as PrimSpec, school.cd 
as school_cd, school.txt as school_txt, educate.fdate as educate_fdate, 
educate.sch_type, educ_arr.txt as school_type_txt,  educate.degree, 
educ_tab.txt 
as degree_txt, educate.tdate as educate_tdate, CASE WHEN dr_spec.specorder = 
1 
THEN spec_tab.txt ELSE '' END as spec_Tab_txt,  

                  CASE WHEN stfstatu.pcp_spec = 'B' then 'Yes' WHEN 
stfstatu.pcp_spec = 'P' then 'Yes' ELSE 'No' END as PCP, 

              case when stfstatu.pcp_spec = 'B' then 'PCP and Specialist'
      else case when stfstatu.pcp_spec = 'P' then 'PCP'
                      else case when stfstatu.pcp_spec = 'S' then 
'Specialist'
                                       else ''
                               END
                  END
                              END as PCPSpec, taxonomy.cd as tax_cd

FROM drname
JOIN stfstatu on drname.dr_id = stfstatu.dr_id
LEFT JOIN address1 a on drname.dr_id = a.dr_id AND a.sch_type = 'G' AND 
a.link = (SELECT TOP 1 link FROM address1 WHERE sch_type = 'G' AND 
address1.dr_id = drname.dr_id ORDER BY LINK DESC)
LEFT JOIN address2 a2 on a2.l_address = a.link
LEFT JOIN dr_lan on drname.dr_id = dr_lan.dr_id
LEFT JOIN lan_tab on lan_tab.cd = dr_lan.lan_cd
LEFT JOIN dr_spec on drname.dr_id = dr_spec.dr_id
LEFT JOIN spec_tab on dr_spec.specialty = spec_tab.cd
LEFT JOIN educate on educate.dr_id = drname.dr_id
LEFT JOIN school on educate.cd = school.cd
LEFT JOIN educ_arr on educ_arr.cd = educate.sch_type
LEFT JOIN facility on facility.fac_cd = educate.fac_cd
LEFT JOIN educ_tab on educate.degree = educ_tab.cd
LEFT JOIN taxonomy on taxonomy.dr_id = drname.dr_id
--Where LTRIM(RTRIM(dr_spec.Fac_Cd)) = ''
“@
$myview.Create()

Answer 1

I don't know if this is the best way to do it, but try putting this at the beginning to set the location to a local directory.

Set-Location C:\;

That helped my job execute correctly. Got that from this source, https://www.sqlhammer.com/sql-agent-2016-jobs-running-powershell-scripts/