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<c/> "TestView2"<c/> 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)) = ''
“@
