none
How to update a value with two variables? RRS feed

  • שאלה

  • suppose i have the following statement

    Invoke-Sqlcmd -Query "UPDATE [$Table]
    SET [size] = '$newSize.Size $newSize.Unit'
    WHERE [cname] = '$cube'" -ConnectionString $CS

    this doesnt work and throws the following error:

    Invoke-Sqlcmd : String or binary data would be truncated. The statement has been terminated.

    in the table, i see this in the field:

    @{Size=919.8; Unit=MB}.Size

    this is because im setting two variables in one statement, but what is the proper syntax for that?

    i am trying to store the following value: 919.8 MB

    i tried this: '$estimatedSize.Size','$estimatedSize.Unit'

    but it doesnt work either

    more info related to this thread: https://stackoverflow.com/a/57531267/8397835

    שבת 17 אוגוסט 2019 00:53

תשובות

  • I don't have SQL to actually try it, but this might work:

    $([string]$newsize.size + " " + $newsize.unit)

    actually, i figured it out :)

    SET [size] = '$($newSize.Size) $($newSize.Unit)'

    • סומן כתשובה על-ידי cataster שבת 17 אוגוסט 2019 01:05
    שבת 17 אוגוסט 2019 01:04
  • An easier way to do this.  It is even better with bigger strings to load.

    $sql = @'
    UPDATE [{0}]
    SET [size] = '{2} {3}'
    WHERE [cname] = '{4}'
    '@
    $query = $sql -f $Table,$newSize.Size, $newSize.Unit,$cube
    
    Invoke-Sqlcmd -Query $query -ConnectionString $CS


    \_(ツ)_/

    • סומן כתשובה על-ידי cataster שבת 17 אוגוסט 2019 02:18
    שבת 17 אוגוסט 2019 02:02
    מנחה דיון

כל התגובות

  • I don't have SQL to actually try it, but this might work:

    $([string]$newsize.size + " " + $newsize.unit)

    שבת 17 אוגוסט 2019 01:04
  • I don't have SQL to actually try it, but this might work:

    $([string]$newsize.size + " " + $newsize.unit)

    actually, i figured it out :)

    SET [size] = '$($newSize.Size) $($newSize.Unit)'

    • סומן כתשובה על-ידי cataster שבת 17 אוגוסט 2019 01:05
    שבת 17 אוגוסט 2019 01:04
  • An easier way to do this.  It is even better with bigger strings to load.

    $sql = @'
    UPDATE [{0}]
    SET [size] = '{2} {3}'
    WHERE [cname] = '{4}'
    '@
    $query = $sql -f $Table,$newSize.Size, $newSize.Unit,$cube
    
    Invoke-Sqlcmd -Query $query -ConnectionString $CS


    \_(ツ)_/

    • סומן כתשובה על-ידי cataster שבת 17 אוגוסט 2019 02:18
    שבת 17 אוגוסט 2019 02:02
    מנחה דיון
  • An easier way to do this.  It is even better with bigger strings to load.

    $sql = @'
    UPDATE [{0}]
    SET [size] = '{2} {3}'
    WHERE [cname] = '{4}'
    '@
    $query = $sql -f $Table,$newSize.Size, $newSize.Unit,$cube
    
    Invoke-Sqlcmd -Query $query -ConnectionString $CS


    \_(ツ)_/

    oh yes, splatting is nice :)
    שבת 17 אוגוסט 2019 02:18
  • That has nothing to do with splatting.  Look gain.  Play with it and test it.  It is one of the oldest string manipulation methods in programming and is used in all languages.  It can be used in any string to create a new string with the variables inserted.

    $name = 'Mary'
    'This string belongs to {0} who likes strings' -f $name
    
    'The exact time now is "{0:hh\:MM\:ss}"!' -f [datetime]::Now

    Learn PowerShell now before it is too late.


    \_(ツ)_/

    שבת 17 אוגוסט 2019 02:25
    מנחה דיון