Remote Source/Validation SQL Query Best Practice

Published: 2017-04-13

When querying SQL data sources in remote source and remote validation scenarios its recommended not to use the SQL PowerShell cmdlet. The cmdlet performance is quite poor and loading the cmdlet takes considerable time that can potentially cause prompts queries to collide causing validation and source queries to fail. Instead we recommend using .NET calls to SQL sources, please see sample code below.

function SqlQuery ($Server, $DATABASE, $SQLQuery)
{ 
     $Datatable = New-Object System.Data.DataTable
     $Connection = New-Object System.Data.SQLClient.SQLConnection 
     $Connection.ConnectionString = "server='$Server';database='$DATABASE';trusted_connection=true;"
     $Connection.Open() 
     $Command = New-Object System.Data.SQLClient.SQLCommand
     $Command.Connection = $Connection
     $Command.CommandText = $SQLQuery
     $Reader = $Command.ExecuteReader()
     $Datatable.Load($Reader)
     $Connection.Close()
      return $Datatable
}
#END OF SQLQUERY FUNCTION

$QUERY = "QUERY HERE"
$DATA = New-Object System.Data.DataTable
$DATA = SqlQuery $DATABASESERVER $DATABASE $QUERY