locked
alter multiple tables error on alter RRS feed

  • Question

  •  I'm trying to update multiple tables to add an extra column with a default value. Would also like to log all events.

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    
    #Connect to the specified instance
    $ns = 'Microsoft.SqlServer.Management.Smo'
    $svr = new-object ("$ns.Server") "localhost"
     
    # Get the specified database where the changes will be made
    $db = $svr.Databases["Copydb1"]
    
    #We also need to create reusable data types like we did when we created a table.
    #Create reusable datatype objects
    $dec18 = [Microsoft.SqlServer.Management.Smo.Datatype]::decimal(18,4)
    
    foreach ($tb in $db.Tables) {
    $tn = $tb.Name
    $col = $tb.Columns['QtyChg']
    if ($col.Count -eq 0) {
    #Create the QtyChg column
    $colmb = new-object ("$ns.Column") ($tb, "QtyChg", $dec18 )
    $colmb.Nullable = $true
    $colmb.AddDefaultConstraint("Def_$($tn)QtyChg") | out-null
    $colmb.DefaultConstraint.Text = 0
    $tb.Columns.Add($colmb)
    }
    # Now Alter the table to actually make the change
    $tb.ALter()
    }
     Error:
     Exception calling "Alter" with "0" argument(s): "Alter failed for Table 'dbo.t4'. "At 
    C:\powershell\add_fields.ps1:28 char:1
    + $tb.ALter()
    + ~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : FailedOperationException

     THanks.

    Sunday, February 17, 2019 12:58 PM

Answers

  • Here is a much simpler way if using WMF 5.1

    using assembly Microsoft.SqlServer.Smo using namespace Microsoft.SqlServer.Management.SMO $srv = [Server]::New('.')
    $srv.Refresh()

    $db = $srv.Databases['Copydb1'] $columnName = 'QtyChg' foreach ($tbl in $db.Tables) { Try { if ($tbl.Columns[$columnName]) { Write-Host "Column $columnName exists in $tb" } else { $col = [Column]::New($tbl, $columnName) $col.DataType = [DataType]::Decimal(4, 18) $tbl.Columns.Add($col) #$tbl.Alter() Write-Host "Column $columnName added to $tbl" } } Catch { Throw } }



    \_(ツ)_/


    • Edited by jrv Sunday, February 17, 2019 11:45 PM
    • Marked as answer by hart60 Monday, February 18, 2019 5:06 PM
    Sunday, February 17, 2019 11:43 PM

All replies

  • Shouldn't the alter statement only be called if the column was added? Ie; part of the IF statement. 

    foreach ($tb in $db.Tables) {
    	$tn = $tb.Name
    	$col = $tb.Columns['QtyChg']
    	if ($col.Count -eq 0) {
    		#Create the QtyChg column
    		$colmb = new-object ("$ns.Column") ($tb, "QtyChg", $dec18 )
    		$colmb.Nullable = $true
    		$colmb.AddDefaultConstraint("Def_$($tn)QtyChg") | out-null
    		$colmb.DefaultConstraint.Text = 0
    		$tb.Columns.Add($colmb)
    		# Now Alter the table to actually make the change
    		$tb.ALter()
    	}
    }

    Sunday, February 17, 2019 1:46 PM
  •  I changed logic but still same error message on the Alter.

      Thanks.

    Sunday, February 17, 2019 2:20 PM
  • Sorry I can't help you with the SQL part of your problem. One suggestion that I would have is to add more "I got here and this what I see" type diagnostic output.

    I borrowed some of this code from https://stackoverflow.com/questions/38419325/catching-full-exception-message

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    
    #Connect to the specified instance
    $ns = 'Microsoft.SqlServer.Management.Smo'
    $svr = new-object ("$ns.Server") "localhost"
     
    # Get the specified database where the changes will be made
    $db = $svr.Databases["Copydb1"]
    
    #We also need to create reusable data types like we did when we created a table.
    #Create reusable datatype objects
    $dec18 = [Microsoft.SqlServer.Management.Smo.Datatype]::decimal(18,4)
    
    foreach ($tb in $db.Tables) {
    	$tn = $tb.Name
    	"`nAnalyzing table: {0}" -f $tb.Name
    	$col = $tb.Columns['QtyChg']
    	if ($col.Count -eq 0) {
    		"QtyChg not found, adding.."
    		#Create the QtyChg column
    		$colmb = new-object ("$ns.Column") ($tb, "QtyChg", $dec18 )
    		$colmb.Nullable = $true
    		$colmb.AddDefaultConstraint("Def_$($tn)QtyChg") | out-null
    		$colmb.DefaultConstraint.Text = 0
    		$tb.Columns.Add($colmb)
    		"Reporting on columns"
    		foreach ($col2 in $tb.Columns) {
    			"----------------------------------------"
    			$col2.name                                    # remove the .name to show all properties.  
    		}
    		# Now Alter the table to actually make the change
    		"--------------------------`nAltering table."
    		try {
    			$tb.ALter()
    		}
    		catch {
    			"--------------------------`n!!!! Exception!!!!!."
    			$e = $_.Exception
    			$msg = $e.Message
    			while ($e.InnerException) {
    				$e = $e.InnerException
    				$msg += "`n" + $e.Message
    			}
    			$msg
    		}	
    	}
    }

     
    Sunday, February 17, 2019 5:01 PM
  • You cannot alter a table with data in it in most cases.  In this case you are trying to make changes that would be invalid because the table has data and the alterations would cause integrity violations.

    I recommend NOT using SMO to alter tables.  Use SQL "ALTER TABLE" statements on a direct SQL connection using  ADO.Net.  This will give you more complete errors.  In most cases a column needs to be added with no constraints and any data that would violate any new constraints would have to be removed.

    I also suggest that posting in a SQLServer forum would give you help with how to use SQLServer.


    \_(ツ)_/

    Sunday, February 17, 2019 7:17 PM
  • Here is the correct way to create a decimal column with SMO.  Note that the scale and precision settings are the opposite from the GUI or from the SQL.

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $srv = [Microsoft.SqlServer.Management.Smo.Server]::New('.')
    
    $db = $srv.Databases['TestDB']
    $tbl = $db.Tables['MyTable']
    
    $col = [Microsoft.SqlServer.Management.SMO.Column]::New($tbl, 'MyNewCol2')
    $col.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::Decimal(4, 18)
    $tbl.Columns.Add($col)
    $tbl.Alter()
    
    

    The code you have copied was incorrect and likely only works by accident.

    Using an  "ALTTER TABLE" statement is much easier and less prone to confusion.  SMO is for experienced database programmers and can be very challenging even for experienced programmers.


    \_(ツ)_/

    Sunday, February 17, 2019 8:30 PM
  • I was trying to find a simple article expaling precision and scale.  All found were very technical so here is a simple explanation.

    In T-SQL and in the GUI the datatype appears like this "decimal(<precision>,<scale>)" of "decimal(18,4)".

    In SMO the SMO datatype constructor is  "[Microsoft.SqlServer.Management.SMO.DataType]::Decimal(<scale>, <precision>)". TO verify this just type the following: "[Microsoft.SqlServer.Management.SMO.DataType]::Decimal"

    PS D:\scripts>  [Microsoft.SqlServer.Management.SMO.DataType]::Decimal
    
    OverloadDefinitions
    -------------------
    static Microsoft.SqlServer.Management.Smo.DataType Decimal(int scale, int precision)

    This is the opposite of other representative uses.  The error is simple in that scale cannot be greater than precision or:

    precision where precision >= scale

    or

    scale where scale <= precision

    This:

    $dec18 = [Microsoft.SqlServer.Management.Smo.Datatype]::decimal(18,4)

    is wrong because 18 > 4

    $dec18 = [Microsoft.SqlServer.Management.Smo.Datatype]::decimal(4,18)

    correct because 4 < 18

    To understand review the complete error returned:

    $error[0] | select *

    Review the error stack for detailed information on the cause of the error.


    \_(ツ)_/



    • Edited by jrv Sunday, February 17, 2019 10:19 PM
    Sunday, February 17, 2019 10:17 PM
  • This would be the best or almost best way to do this with SMO.  Notice we don't need all of that stuff about a namespace.  It just adds complexity and room for misunderstanding.

    [void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') $srv = [Microsoft.SqlServer.Management.Smo.Server]::New('.')
    $srv.Refresh()

    $db = $srv.Databases['testdb'] $columnName = 'QtyChg' foreach ($tbl in $db.Tables){ Try{ if($tbl.Columns[$columnName]){ Write-Host "Column $columnName exists in $tb" }else{ $col = [Microsoft.SqlServer.Management.SMO.Column]::New($tbl, $columnName) $col.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::Decimal(4, 18) $tbl.Columns.Add($col) $tbl.Alter() Write-Host "Column $columnName added to $tbl" } } Catch{ Throw } }



    \_(ツ)_/





    • Edited by jrv Sunday, February 17, 2019 11:46 PM
    Sunday, February 17, 2019 11:14 PM
  • Here is a much simpler way if using WMF 5.1

    using assembly Microsoft.SqlServer.Smo using namespace Microsoft.SqlServer.Management.SMO $srv = [Server]::New('.')
    $srv.Refresh()

    $db = $srv.Databases['Copydb1'] $columnName = 'QtyChg' foreach ($tbl in $db.Tables) { Try { if ($tbl.Columns[$columnName]) { Write-Host "Column $columnName exists in $tb" } else { $col = [Column]::New($tbl, $columnName) $col.DataType = [DataType]::Decimal(4, 18) $tbl.Columns.Add($col) #$tbl.Alter() Write-Host "Column $columnName added to $tbl" } } Catch { Throw } }



    \_(ツ)_/


    • Edited by jrv Sunday, February 17, 2019 11:45 PM
    • Marked as answer by hart60 Monday, February 18, 2019 5:06 PM
    Sunday, February 17, 2019 11:43 PM
  •  Thanks jrv for all of your inputs is $srv = [Microsoft.SqlServer.Management.Smo.Server]::New('.')
    a 5 version?

     Without having to define a specific table could your example "This would be the best or almost best way to do this with SMO"  find all tables like f-%  

     Thanks again for sharing different approaches.

    Monday, February 18, 2019 12:26 AM
  • WMF 5 and later.

    I posted an example of running against all tables.


    \_(ツ)_/

    Monday, February 18, 2019 12:34 AM
  •  Thanks again for all your insights...very helpful.

    Monday, February 18, 2019 5:06 PM