none
Creating Complex Views using Powershell RRS feed

  • Question

  • I've been tasked to create a Powershell script that will create the following view which works currently within Microsoft SQL Studio.  I've made a copy of the working script and would like to to apply it to this Microsoft example.  My question is - can you take the known working query below and insert it in the TextBody property below?


    # Set the TextHeader and TextBody property to define the view.   
    $myview.TextHeader = "CREATE VIEW [Sales].[Test_View] AS"  
    $myview.TextBody ="SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID"  



    CREATE VIEW [dbo].[PROV] AS
    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)) = ''
    • Moved by Bill_Stewart Monday, April 30, 2018 9:26 PM This is not "teach me how to use SQL from PowerShell" forum
    Wednesday, February 21, 2018 7:46 PM

All replies

  • What "TextBody".  You have not provided good  information.  PowerShell does not have a Text Body".

    A view can be created in PowerShell using "Invoke-SqlCmd".


    \_(ツ)_/

    Wednesday, February 21, 2018 8:00 PM
  • Are you asking about SQLServer Management Studio (SSMS)?  If so the answer is no and you need to post SSMS questions in the SQLServer forum.


    \_(ツ)_/

    Wednesday, February 21, 2018 8:03 PM
  • I'm using this example - https://technet.microsoft.com/en-us/library/ms162235(v=sql.110).aspx

    with the idea I can add my SQL query from a view I copied from our SSMS interface.  My question is how complex of a query can be plugged in - using this example.  As you see the query is using CASE statements - just wondering if it's possible to insert my working view code into this Powershell method?

    Wednesday, February 21, 2018 9:57 PM
  • That is not PowerShell. It is VB/C# and requires the SMO assemblies to be loaded. 

    If you have the assemblies loaded and can create the view then the text can be as large as the SQL needed to create the view.

    Here is how to easily declare the view text:

    $viewtext = @'
    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)) = ''
    '@
    
    

    Then just assign it to the view.

    $myView.TextBody = $viewtext

    I find it easir to place large sql into a file and test it then just copy the file.

    $myView.TextBody = Get-Content view.sql | Out-String


    \_(ツ)_/

    Wednesday, February 21, 2018 10:10 PM
  • There is a section - Creating, Altering, and Removing a View in PowerShell of a Technet article titled:
      Creating, Altering, and Removing Views
    Thanks for detailing out the option you sent me that allows me to insert my code



    Wednesday, February 21, 2018 11:13 PM
  • Yes.  We do it all of the time with SMO which is not part of PowerShell.  SSMS opens PowerShell with SMO loaded.  Regular PowerShell does not.   The documents for C# will be close to usable for PowerShell SMO.

    \_(ツ)_/

    Thursday, February 22, 2018 12:05 AM
  • I first ran the SQL script via SSMS 2012 to verify it does create the view - positive result.  

    Then I attempted to encapsulate the same code using the Here-String via Powershell and received errors below.  I'll try this second method if you think this is a better method for incorporating the working SQL code  -

     find it easir to place large sql into a file and test it then just copy the file.

    $myView.TextBody = Get-Content view.sql | Out-String

    :

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #import SQL Server module t
    o create a new SMO Server object
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> Import-Module SQLPS -Disabl
    eNameChecking
    The term 'Import-Module' is not recognized as the name of a cmdlet, function, s
    cript file, or operable program. Check the spelling of the name, or if a path w
    as included, verify that the path is correct and try again.
    At line:1 char:14
    + Import-Module <<<<  SQLPS -DisableNameChecking
        + CategoryInfo          : ObjectNotFound: (Import-Module:String) [], Comma
       ndNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #your SQL Server Instance N
    ame
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $SQLInstanceName = "SQL2012
    "
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $Server = New-Object -TypeN
    ame Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #provide your database name
     where you want to create view
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $DatabaseName = "Echo"
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #create SMO handle to your
    database
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $DBObject = $Server.Databas
    es[$DatabaseName]
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #view name
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwName = "PROVTEST2"
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #create SMO handle for your
     view
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject = $DBObject.Views
    [$vwName]
    Cannot index into a null array.
    At line:1 char:29
    + $vwObject = $DBObject.Views[ <<<< $vwName]
        + CategoryInfo          : InvalidOperation: (PROVTEST2:String) [], Runtime
       Exception
        + FullyQualifiedErrorId : NullArray

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #check if view exists, if e
    xists then drop it
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> if ($vwObject)
    >> {
    >> $vwObject.Drop()
    >> }
    >>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #Create an SMO View handle,
     which requires three parameters (database handle, view name, and schema)
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject = New-Object -Typ
    eName Microsoft.SqlServer.Management.SMO.View -ArgumentList $DBObject, $vwName,
    "dbo"
    New-Object : Exception calling ".ctor" with "3" argument(s): "SetParent failed
    for View 'dbo.PROVTEST2'. "
    At line:1 char:23
    + $vwObject = New-Object <<<<  -TypeName Microsoft.SqlServer.Management.SMO.Vie
    w -ArgumentList $DBObject, $vwName, "dbo"
        + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvoca
       tionException
        + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.Power
       Shell.Commands.NewObjectCommand

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs>
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #TextMode = false  means no
     need to explicitly write the CREATE VIEW header
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject.TextMode = $true
    Property 'TextMode' cannot be found on this object; make sure it exists and is
    settable.
    At line:1 char:11
    + $vwObject. <<<< TextMode = $true
        + CategoryInfo          : InvalidOperation: (TextMode:String) [], RuntimeE
       xception
        + FullyQualifiedErrorId : PropertyNotFound

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject.TextHeader = "CRE
    ATE VIEW [dbo].[PROVTEST2] AS"
    Property 'TextHeader' cannot be found on this object; make sure it exists and i
    s settable.
    At line:1 char:11
    + $vwObject. <<<< TextHeader = "CREATE VIEW [dbo].[PROVTEST2] AS"
        + CategoryInfo          : InvalidOperation: (TextHeader:String) [], Runtim
       eException
        + FullyQualifiedErrorId : PropertyNotFound

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> #TextMode is false means we
     can provide only view body with Select Statement
    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject.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 an
    d 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_s
    pec.specorder = 1 THEN dr_spec.specialty ELSE '' END as dr_spec, CASE WHEN dr_sp
    ec.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 degr
    ee_txt, educate.tdate as educate_tdate, CASE WHEN dr_spec.specorder = 1 THEN spe
    c_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 'Specia
    list'
    >>                                            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.lin
    k = (SELECT TOP 1 link FROM address1 WHERE sch_type = 'G' AND address1.dr_id = d
    rname.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)) = ''
    >> "@
    >>
    Property 'TextBody' cannot be found on this object; make sure it exists and is
    settable.
    At line:1 char:11
    + $vwObject. <<<< TextBody = @"
        + CategoryInfo          : InvalidOperation: (TextBody:String) [], RuntimeE
       xception
        + FullyQualifiedErrorId : PropertyNotFound

    PS SQLSERVER:\SQL\DCQWDBS097\DEFAULT\JobServer\Jobs> $vwObject.Create()

    Thursday, February 22, 2018 6:30 PM
  • here is the code I ran from PS SQLSERVER>

    #import SQL Server module to create a new SMO Server object
    Import-Module SQLPS -DisableNameChecking
     
    #your SQL Server Instance Name
    $SQLInstanceName = "SQL2012"
    $Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName
     
    #provide your database name where you want to create view
    $DatabaseName = "Echo"
     
    #create SMO handle to your database
    $DBObject = $Server.Databases[$DatabaseName]
     
    #view name
    $vwName = "PROVTEST2"
     
    #create SMO handle for your view
    $vwObject = $DBObject.Views[$vwName]
     
    #check if view exists, if exists then drop it
    if ($vwObject)
    {
    $vwObject.Drop()
    }
     
    #Create an SMO View handle, which requires three parameters (database handle, view name, and schema)
    $vwObject = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View -ArgumentList $DBObject, $vwName, "dbo"
     
    #TextMode = false  means no need to explicitly write the CREATE VIEW header
    $vwObject.TextMode = $true
    $vwObject.TextHeader = "CREATE VIEW [dbo].[PROVTEST2] AS"
    #TextMode is false means we can provide only view body with Select Statement
    $vwObject.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)) = ''
    “@
     
    $vwObject.Create()

    Thursday, February 22, 2018 6:32 PM
  • Running PowerShell from the SSMS menu does not require loading any modules.  SMO is loaded. Just create the view object and set the properties.  Start with a simple view.

    Import-Module SqlServer
    $srv = [Microsoft.SqlServer.Management.Smo.Server]::new($env:COMPUTERNAME)
    $db = $srv.Databases['TestDB']
    $v = [Microsoft.SqlServer.Management.Smo.View]::New($db,'Test_view')
    $v.TextHeader = 'CREATE VIEW [Test_View] AS'
    $v.TextBody = 'select * from syscolumns;'
    $v.Create()


    \_(ツ)_/

    Thursday, February 22, 2018 7:10 PM
  • This is what "TextMode" is used for:

    A Boolean value that specifies whether the text header is editable as text.
    
    If true (default), the text header is editable as text.
    
    If false, the text header is not editable.
    
    
    Implements
    ITextObject.TextMode

    Notice it just establishes "TextHeader" edit ability and has nothing to do with "TextBody".


    \_(ツ)_/

    Thursday, February 22, 2018 7:18 PM
  • PS C:\Users\user> Get-Module -ListAvailable

    attempted this test using a simpleview above - an issue is 'The term 'Import-Module' is not recognized.

    Here's the return of listout of modules I have available:

    ModuleType Name                      ExportedCommands
    ---------- ----                      ----------------
    Manifest   ADRMS                     {}
    Manifest   AppLocker                 {}
    Manifest   BestPractices             {}
    Manifest   BitsTransfer              {}
    Manifest   PSDiagnostics             {}
    Manifest   ServerManager             {}
    Manifest   TroubleshootingPack       {}
    Binary     Microsoft.MonitoringAg... {}

    PS C:\Users\Echo_MTUser> sqlps
    Microsoft SQL Server PowerShell
    Version 10.50.1600.1
    Microsoft Corp. All rights reserved.
    Friday, February 23, 2018 6:51 PM
  • Import-Module is a core CmdLet of PowerShell.  It may not be available over remote connections if they have been retricted.

    From the looks of your module list you are running on a restricted remote connection or your admins have defined all PowerShell configurations as restricted.

    This is an issue you will have to fix if that is the case.

    If you are running from the SSMS menu "Start PowerShell" then you can skip the "Import-Module line since SQLServer should already be loaded.

    If you are running on Win7/WS2008r2 then you may only have PS2 available and many things may not work.  You would need to upgrade PS.

    SQLPS at a command line is a custom console which also loads the SQLServer support.

    The SMO assemblies are also already loaded and SQLServer support is not needed to be loaded as it is loaded by the console.

    Skip the Import-Module line and the remaining code will work assuming SQS is installed correctly and the assemblies and paths are all set correctly.


    \_(ツ)_/

    Friday, February 23, 2018 7:30 PM
  • This is the current version of SQLPS(13.0).  You are running a very old copy.

    PS D:\scripts> sqlps
    Microsoft (R) SQL Server (R) PowerShell
    Version 13.0.1601.5
    Copyright (c) 2015 Microsoft. All rights reserved.
    
    PS D:\scripts>
    

    When running a PowerShell job I am not sure if the server support is loaded.  If it is skip the import-module command.


    \_(ツ)_/

    Friday, February 23, 2018 7:37 PM
  • PowerShell agent jobs load the older SQLPS module.  That is because I have not installed the newer support:

    Here is what I get in a job:

    ModuleType Version    Name                                ExportedCommands                                              
    ---------- -------    ----                                ----------------                                              
    Script     0.0        Sqlps                                                                                             
    Manifest   1.0        SQLPS                               {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListe...
    
    
    
    

    This also loads SMO support.


    \_(ツ)_/

    Friday, February 23, 2018 7:44 PM
  • Also note that, by default, the SQLAgent account has no permissions on the server so all attempts to create a view will fail.

    Every part of the step succeeds except the "Create":

    A job step received an error at line 8 in a PowerShell script. The corresponding line is '$v.Create()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Create" with "0" argument(s): "Create failed for View 'dbo.Test_view'. "


    \_(ツ)_/


    • Edited by jrv Friday, February 23, 2018 7:49 PM
    Friday, February 23, 2018 7:48 PM
  • Ok.  I have run the following under an agent job step using the now correct, SQLAgent account settings an it ran with no issues and the view was created.

    $db = $srv.Databases['TestDB']
    $v = [Microsoft.SqlServer.Management.Smo.View]::New($db,'Test_view')
    $v.TextMode = $true
    $v.TextHeader = 'CREATE VIEW [Test_View] AS'
    $v.TextBody = 'select * from syscolumns;'
    $v.Create()


    \_(ツ)_/

    Friday, February 23, 2018 8:05 PM