Invoke-Sqlcmd inserting variables from array and not data values

Welcome Forums General PowerShell Q&A Invoke-Sqlcmd inserting variables from array and not data values

Viewing 6 reply threads
  • Author
    Posts
    • #203171
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      Hello,

      The script below pulls records from Azure billing.

      
      $sql = @’
      INSERT INTO [dbo].[AzureBilling](
      [UsageStart],
      [UsageEnd],
      [BillingPeriodName],
      [InstanceName],
      [UsageQuantity],
      [BillableQuantity],
      [PretaxCost],
      [MeterID]
      )
      VALUES(
      ‘{0}’,
      ‘{1}’,
      ‘{2}’,
      ‘{3}’,
      ‘{4}’,
      ‘{5}’,
      ‘{6}’,
      ‘{7}’
      )
      go
      ‘@
      
      Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1 |
      ForEach-Object{
      $sql -f $_.UsageStart,$_.UsageEnd,$_.BillingPeriodName,$_.InstanceName,$_.UsageQuantity,$_.BillableQuantity,$_.PretaxCost,$_.MeterId
      }
      
      

      I’m trying to create an insert statement and use the invoke-sqlcmd to load the data.  However, it seems to be inserting the index arrays instead of the data.

      
      
      INSERT INTO [dbo].[AzureBilling](
          [UsageStart],
          [UsageEnd],
          [BillingPeriodName],
          [InstanceName],
          [UsageQuantity],
          [BillableQuantity],
          [PretaxCost],
          [MeterID]
      )
      VALUES(
          '1/1/2020 12:00:00 AM',
          '1/1/2020 11:59:59 PM',
          '20200101',
          'STAGING',
          '22.6451612832258',
          '',
          '2.81829032873185',
          '1450c4f7-f8db-4fc'
      )
      go
      INSERT INTO [dbo].[AzureBilling](
          [UsageStart],
          [UsageEnd],
          [BillingPeriodName],
          [InstanceName],
          [UsageQuantity],
          [BillableQuantity],
          [PretaxCost],
          [MeterID]
      )
      VALUES(
          '{0}',
          '{1}',
          '{2}',
          '{3}',
          '{4}',
          '{5}',
          '{6}',
          '{7}'
      )

      I seen there is a ‘-variable’ option, but unsure how I would use it.

      Thanks,

      Frank

    • #203183
      Participant
      Topics: 12
      Replies: 1489
      Points: 1,987
      Helping Hand
      Rank: Community Hero

      In the code you are not showing how you are calling SQL, but you are not saving the commands you created to variable. Additionally, GO cannot be used, it’s only relevant in certain places. Use a semicolon to end each command:

      $sql = @'
      INSERT INTO [dbo].[AzureBilling](
      [UsageStart],
      [UsageEnd],
      [BillingPeriodName],
      [InstanceName],
      [UsageQuantity],
      [BillableQuantity],
      [PretaxCost],
      [MeterID]
      )
      VALUES(
      '{0}',
      '{1}',
      '{2}',
      '{3}',
      '{4}',
      '{5}',
      '{6}',
      '{7}'
      );
      
      '@
      
      $sqlCmd = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1 |
                ForEach-Object{
                    $sql -f $_.UsageStart,$_.UsageEnd,$_.BillingPeriodName,$_.InstanceName,$_.UsageQuantity,$_.BillableQuantity,$_.PretaxCost,$_.MeterId
                }
      
      Invoke-SQLCmd -Query $sqlCmd
      

      Also, it’s not a best practice to assume a GET returns something, it would be better to follow logic like this:

      $sql = @'
      INSERT INTO [dbo].[AzureBilling](
      [UsageStart],
      [UsageEnd],
      [BillingPeriodName],
      [InstanceName],
      [UsageQuantity],
      [BillableQuantity],
      [PretaxCost],
      [MeterID]
      )
      VALUES(
      '{0}',
      '{1}',
      '{2}',
      '{3}',
      '{4}',
      '{5}',
      '{6}',
      '{7}'
      );
      
      '@
      
      $usage = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING -Top 1
      
      if ($usage) {
          $sqlCmd = foreach ($item in $usage) {
      
              $sql -f $item.UsageStart,
                      $item.UsageEnd,
                      $item.BillingPeriodName,
                      $item.InstanceName,
                      $item.UsageQuantity,
                      $item.BillableQuantity,
                      $item.PretaxCost,
                      $item.MeterId
          }
      
          Invoke-SQLCmd -Query $sqlCmd
      }
      else {
          'No usage'
      }
      
      
    • #203210
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      Great points! It works for returning 1 record.  I’m receiving an error when I try to return all records:

      
      Invoke-Sqlcmd : Cannot convert ‘System.Object[]’ to the type ‘System.String’ required by parameter ‘Query’. Specified method is not supported.
      At line:39 char:88
      + … Instance “CQ\SERVER” -Database sqltest -Query $sqlCmd
      + ~~~~~~~
      + CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
      + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
      
      

      I’m assuming it has something to do with building the array.

    • #203216
      Participant
      Topics: 12
      Replies: 1489
      Points: 1,987
      Helping Hand
      Rank: Community Hero

      Yes, it’s most likely a string array, so you can just one of the following:

      $content = @"
      The color is {0}
      "@
      
      
      $result = foreach ($item in 'red','green','blue') {
          $content -f $item
      }
      
      #join with line break
      $result -join "`r`n"
      #pipe to Out-String
      $result | Out-String
      
    • #203222
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      That worked perfectly!

      I ran it for the entire month for one resource and it wrote 91 records into the database! At this point, it seems the easiest route to load the data instead of writing to file and then loading. I may need to change if we start to report outside of our group to make this method more efficient.

      Here’s the final code:

      
      $sql = @’
      INSERT INTO [dbo].[AzureBilling](
          [UsageStart],
          [UsageEnd],
          [BillingPeriodName],
          [InstanceName],
          [UsageQuantity],
          [BillableQuantity],
          [PretaxCost],
          [MeterID]
      )
      VALUES(
          ‘{0}’,
          ‘{1}’,
          ‘{2}’,
          ‘{3}’,
          ‘{4}’,
          ‘{5}’,
          ‘{6}’,
          ‘{7}’
      );
      ‘@
      
      $usage = Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING 
      
      if ($usage) {
          $sqlCmd = foreach ($item in $usage) {
      
          $sql -f $item.UsageStart,
                  $item.UsageEnd,
                  $item.BillingPeriodName,
                  $item.InstanceName,
                  $item.UsageQuantity,
                  $item.BillableQuantity,
                  $item.PretaxCost,
                  $item.MeterId 
          }
      
          # string array; join with line break
          $sqlCmd -join “`r`n”
          # pipe to Out-String
          $qry = $sqlCmd | Out-String
          Invoke-Sqlcmd -ServerInstance “CQ\SERVER” -Database sqltest -Query $qry 
      }
      else {
          ‘No usage’
      }
      
      

      Again, thanks for all of your help!

    • #203234
      Participant
      Topics: 12
      Replies: 1489
      Points: 1,987
      Helping Hand
      Rank: Community Hero

      If you are strictly inserting records, there are better methods:

      Write-SqlTableData

      Not messed with it personally, but the idea is you would be able to do:

      Get-AzConsumptionUsageDetail ... | Write-SqlTableData ..
      

      Normally, I do bulk inserts as I’m getting 10k=50k records and inserting them in SQL and have done inserts with 100k+ and it takes seconds. Only drawback is you have to convert from PSObject > DataTable > Map Columns > Insert. The Write-SqlDataTable looks like it takes some of the work out of the middle and would fit your needs rather than building a sql script to insert rows manually.

      6 methods to write PowerShell output to a SQL Server table

    • #203240
      Participant
      Topics: 16
      Replies: 29
      Points: 226
      Rank: Participant

      Seems much simpler to load data, and possibly quicker.  I didn’t time, but the query finished in a few seconds for 91 records.

      
      (Get-AzConsumptionUsageDetail -BillingPeriodName 202001 -InstanceName STAGING | Select-Object -Property UsageStart, UsageEnd, BillingPeriodName, InstanceName, UsageQuantity, BillableQuantity, PretaxCost, MeterId) |
      Write-SqlTableData -ServerInstance “” -DatabaseName “” -SchemaName “dbo” -TableName “” -Force
      
      

      Thanks!

Viewing 6 reply threads
  • You must be logged in to reply to this topic.