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

  • 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<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,

    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 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)) = ''

    • Edited by DanFig Friday, February 23, 2018 12:07 AM
    • Moved by Bill_Stewart Monday, April 30, 2018 9:22 PM Unanswerable drive-by question
    Friday, February 23, 2018 12:03 AM

All replies

  • If you would use the code I posted in your other thread you would not have this issue.

    The is no such file system path as this - "CD \sql\localhost\default\databases" on your system. Do NOT use the SQLPS snap-in. It is obsolete.  Use the view creation as I showed you.  It will work on any version of PS 3 or later.


    Friday, February 23, 2018 12:24 AM
  • last part of script is to run the -  
    Friday, February 23, 2018 12:25 AM
  • You have to explicitly get the server and then get the database as I posted.  Other methods will not work correctly in all situations.

    If you have not loaded the latest good version of SQLServer module load it this way:

    Find-Module SQLServer | Install-Module

    You must install from an elevated prompt.

    We are at SQLServer module version 21 and it can now only be installed from PowerSHellGet.

    Here is the latest but not the greatest documentation: https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module


    Friday, February 23, 2018 12:34 AM