PowerShell MSSQL Query for Windows Azure SMA

workflow db_query-database { param ( [Parameter(Mandatory=$true)] [string] $Query ) $con = Get-AutomationConnection -Name 'Connection' $securepassword = ConvertTo-SecureString -AsPlainText -String $con.Password -Force $cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $con.Username, $securepassword

$RetData = InlineScript{
    
    $DBServer = "DBSERVER\DATBASE"
    $databasename = "SCO_Logging"
    $Connection = new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
    $Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True" 
    ### Connect to Database and Run Query
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands
    $SqlQuery = "$Using:Query"
    $Connection.open()
    $SqlCmd.CommandText = $SqlQuery
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlCmd.Connection = $Connection
    $DataSet = New-Object System.Data.DataSet
    $rows = $SqlAdapter.Fill($DataSet)
    $Connection.Close()
    $DataSet.Tables[0]
    
} -PSComputerName $con.ComputerName -PSCredential $cred -PSConfigurationName Microsoft.PowerShell32

$RetData  

}

Jeremy Tirrell

Read more posts by this author.