none
Create Computed Column in PowerShell

    Question

  • STIG (security tech inst guide), wants hash values on all the libraries and executables.  

    There's code here but I ran into a problem with the syntax for creating a computed column (see column 8), and hoping for help with syntax.   Computed column should be "case when baselnhash=comparehash then 0 else 1 end"

    # MyServer\MyInstance

    param([string] $server,[string] $database,[string] $schema,[string]

    $table,[string] $output_file) $server="MyServer\MyInstance" $database="ds2k" $schema="dbo" $table="hasher" $output_file="readme.txt" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") $tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table") $scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server) # Get the database and table objects $db = $srv.Databases[$database] $tableExists=$db.Tables.Item("Hasher","Dbo"); IF(!($tableExists)) { $tb = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -argumentlist $db, "Hasher" $Type = [Microsoft.SqlServer.Management.SMO.DataType]::Int $col1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"ID", $Type $col1.Identity = $true $col1.IdentitySeed = 1 $col1.IdentityIncrement = 1 $tb.Columns.Add($col1) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::nvarChar(500) $col2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"path", $Type $col2.Collation = "Latin1_General_CI_AS" $col2.Nullable = $false $tb.Columns.Add($col2) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::nvarChar(150) $col3 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"filenm", $Type $col3.Collation = "Latin1_General_CI_AS" $col3.Nullable = $false $tb.Columns.Add($col3) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::varbinary(16) $col4 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"baselnhash", $Type $col4.Nullable = $true $tb.Columns.Add($col4) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime $col5 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"BaseLnDate", $Type $col5.Nullable = $false $tb.Columns.Add($col5) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::varbinary(16) $col6 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"comparehash", $Type $col6.Nullable = $true $tb.Columns.Add($col6) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime $col7 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"lasthashdate", $Type $col7.Nullable = $true $tb.Columns.Add($col7) $Type = [Microsoft.SqlServer.Management.SMO.DataType]::Boolean $col8 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"isChanged", $Type

    $col8.Computed = $true 

    $col8.ComputedText = "case when baselnhash=comparehash then 0 else 1 end"

    $col8.Nullable = $true $tb.Columns.Add($col8) $tb.Create() }

    There's more since it needs to be populated with the libraries but for now, I'm looking for the basics.  This is running from a SQL Agent Powershell command - first pass is baseline, latter passes are comparisons and this is the table it saves hash values in  -   there is a path, a file name, base line hash and so on.

    Reference: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/creating-altering-and-removing-tables?view=sql-server-2017

    Error: The property "Computed" cannot be found on this object, The property "ComputedText" cannot be found on this object.


    R, J

    • Edited by Crakdkorn Saturday, July 7, 2018 4:06 PM
    Saturday, July 7, 2018 3:35 PM

Answers

  • No need for the SqlDataAdapter since you are only executing DDL. You could simplify as:

    [string] $sqlCommand = $("alter table hasher add isChanged as case when baselnhash=comparehash then 0 else 1 end;") $connectionString = "Data Source=$server; " + "Integrated Security=SSPI; " + "Initial Catalog=$database" $connection = new-object system.data.SqlClient.SQLConnection($connectionString) $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection) $connection.Open() $command.ExecuteNonQuery() | Out-Null

    $connection.Close()



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP Monday, July 9, 2018 7:25 AM
    • Marked as answer by Crakdkorn Wednesday, July 11, 2018 4:14 PM
    Saturday, July 7, 2018 4:46 PM
    Moderator

All replies

  • I found a solution I am not happy with but it works:

     [string] $sqlCommand = $("alter table hasher add isChanged as case when baselnhash=comparehash then 0 else 1 end")
         
     $connectionString = "Data Source=$server; " +
                "Integrated Security=SSPI; " +
                "Initial Catalog=$database"
    
        $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
        $connection.Open()
    
        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataSet) | Out-Null
    
        $connection.Close()
        $dataSet.Tables
    


    R, J

    Saturday, July 7, 2018 4:26 PM
  • No need for the SqlDataAdapter since you are only executing DDL. You could simplify as:

    [string] $sqlCommand = $("alter table hasher add isChanged as case when baselnhash=comparehash then 0 else 1 end;") $connectionString = "Data Source=$server; " + "Integrated Security=SSPI; " + "Initial Catalog=$database" $connection = new-object system.data.SqlClient.SQLConnection($connectionString) $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection) $connection.Open() $command.ExecuteNonQuery() | Out-Null

    $connection.Close()



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP Monday, July 9, 2018 7:25 AM
    • Marked as answer by Crakdkorn Wednesday, July 11, 2018 4:14 PM
    Saturday, July 7, 2018 4:46 PM
    Moderator
  • Thanks Dan, 

    I'm more interested in knowing why that property for computedtext and computed is failing in SMO column, not so much in what the alternative is.  But I appreciate the refresher on the nonquery method.  It's been a while since I did any SMO queries.


    R, J

    Monday, July 9, 2018 12:34 PM