How do you run a SQL Server query from PowerShell

Question

Is there a way to execute an arbitrary query on a SQL Server using Powershell on my local machine

User · Answer

You can use the Invoke-Sqlcmd cmdlet  Invoke-Sqlcmd -Query  SELECT GETDATE   AS TimeOfQuery   -ServerInstance  MyComputer MyInstance    http   technet microsoft com en-us library cc281720 aspx

User · Answer

Here s an example I found on this blog    cn2   new-object system data SqlClient SQLConnection  Data Source machine1 Integrated Security SSPI Initial Catalog master     cmd   new-object system data sqlclient sqlcommand  dbcc freeproccache    cn2    cn2 Open    if   cmd ExecuteNonQuery   -ne -1        echo  Failed      cn2 Close      Presumably you could substitute a different TSQL statement where it says dbcc freeproccache

User · Answer

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

User · Answer

This function will return the results of a query as an array of powershell objects so you can use them in filters and access columns easily   function sql  sqlText   database    master    server               connection   new-object System Data SqlClient SQLConnection  Data Source  server Integrated Security SSPI Initial Catalog  database         cmd   new-object System Data SqlClient SqlCommand  sqlText   connection         connection Open         reader    cmd ExecuteReader         results           while   reader Read                   row               for   i   0   i -lt  reader FieldCount   i                           row  reader GetName  i      reader GetValue  i                     results    new-object psobject -property  row                        connection Close          results

User · Answer

Invoke-Sqlcmd -Query  sp who  -ServerInstance   -QueryTimeout 3

User · Answer

To avoid SQL Injection with varchar parameters you could use function sqlExecuteRead  connectionString   sqlCommand   pars          connection   new-object system data SqlClient SQLConnection  connectionString       connection Open        command   new-object system data sqlclient sqlcommand  sqlCommand   connection       if   pars -and  pars Keys            foreach  key in  pars keys                  avoid injection in varchar parameters              par    command Parameters Add  quot   key quot    system data SqlDbType   VarChar  512                par Value    pars  key                         adapter   New-Object System Data sqlclient sqlDataAdapter  command      dataset   New-Object System Data DataSet      adapter Fill  dataset    Out-Null      connection Close       return  dataset tables 0  rows      connectionString    quot connectionstringHere quot   sql    quot select top 10 Message  TimeStamp  Level from dbo log  quot         quot where Message    MSG and Level like  LEVEL quot   pars          MSG    this is a test from powershell      LEVEL    aaa      sqlExecuteRead  connectionString  sql  pars

User · Answer

If you want to do it on your local machine instead of in the context of SQL server then I would use the following  It is what we use at my company    ServerName     ServerName    DatabaseName     DatabaseName    Query    SELECT   FROM Table WHERE Column         Timeout parameters  QueryTimeout   120  ConnectionTimeout   30   Action of connecting to the Database and executing the query and returning results if there were any   conn New-Object System Data SqlClient SQLConnection  ConnectionString    Server  0  Database  1  Integrated Security True Connect Timeout  2   -f  ServerName  DatabaseName  ConnectionTimeout  conn ConnectionString  ConnectionString  conn Open    cmd New-Object system Data SqlClient SqlCommand  Query  conn   cmd CommandTimeout  QueryTimeout  ds New-Object system Data DataSet  da New-Object system Data SqlClient SqlDataAdapter  cmd   void  da fill  ds   conn Close    ds Tables   Just fill in the  ServerName   DatabaseName and the  Query variables and you should be good to go   I am not sure how we originally found this out  but there is something very similar here

User · Answer

For others who need to do this with just stock  NET and PowerShell  no additional SQL tools installed  here is the function that I use  function Invoke-SQL       param           string   dataSource    quot   SQLEXPRESS quot            string   database    quot MasterData quot            string   sqlCommand     throw  quot Please specify a query  quot                 connectionString    quot Data Source  dataSource   quot                 quot Integrated Security SSPI   quot                 quot Initial Catalog  database quot        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     I have been using this so long I don t know who wrote which parts  This was distilled from others  examples  but simplified to be clear and just what is needed without extra dependencies or features  I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https   github com ChrisMagnuson InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it  assuming your using PowerShell v3 or later

[powershell] How do you run a SQL Server query from PowerShell?

Examples related to powershell

Examples related to sql