Sunday, March 31, 2019

Run a SQL Server query from PowerShell


We can call SQL Query from PowerShell by just creating below function from .net tool and then call the function through PowerShell (no additional SQL tools required),
function Invoke-SQL {
    param(
        [string] $dataSource = "testsqlx\sqldbx",
        [string] $database = "master",
        [string] $sqlCommand = $(throw "Please specify a query.")
            #[string] $sqlCommand = "SELECT * FROM [LINK_NAME]..[SCHEMA].[VIEW_NAME]"
      )
    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
   $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
   $connection.Open()
   $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
   $dataset = New-Object System.Data.DataSet
   $adapter.Fill($dataSet) | Out-Null
   $connection.Close()
   $dataSet.Tables
}
Now from power shell tool you can invoke the above function and pass the SQL statement that you want to run,
Syntax:
#Invoke-SQL -sqlCommand "SELECT * FROM [DB_LINK_NAME]..[SCHEMA].[VIEW_NAME]"
Example:
Invoke-SQL -sqlCommand "SELECT * FROM [EBSLINK]..[FIN].[MYVIEW] where [col_name]='VALUE'"
Note: There isn't a built-in "PowerShell" way of running a SQL query. If you have the SQL Server tools installed, you'll get an Invoke-SqlCmd cmdlet.
Because PowerShell is built on .NET, you can use the ADO.NET API to run your queries.
#Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3

Grant User Permission to SELECT on a Linked Server
Below command to grant a user permissions to SELECT on a Linked Server.
I had to make them a member of Public on Master and GRANT Execute to an Extended Procedure.

SQLCMD> GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO "ORACLE.COM\SAMI.MALIK";