How to run sql script using powershell?

Question

I wanted to run a sql script using powershell but getting an error "The term 'Invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check t he spelling of the name, or if a path was included, verify that the path is correct and try again."

I have found the below snippet from some website.but this is only for one sql command..But i wanted to run a sql script.

Could someone please help in modifying the below for sql script or any better suggestion ?

   SQLServer = "ABCD\ABC" 
    $SQLDBName = "ABC_1223"
    $SqlQuery = "select * from table" 

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database  =$SQLDBName;uid=$SQLDBName;pwd= $pwd; Integrated Security = True" 

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) 
    $SqlConnection.Close()
    clear 
    $DataSet.Tables[0]

To use invoke-sqlcmd you have to import the sqlps module. blog.smu.edu/wis/2012/11/26/sql-server-powershell-module-sqlps — David Brabant
@David Brabant Could you please help me in getting that done please ?Any steps you have in hand ? Also is there any modifications that you can see that we can make to above script? — user2075017

nimizen nimizen · Accepted Answer · 2014-08-18T12:59:20

You can use your code as it is, just put your SQL script in a Here-String (string literal). In addition I would wrap the call to the SQL database in a Try/Catch/Finally construct to ensure your connections are closed and disposed. Finally, consider using Application Name in your connection string to give your DBA's a clue as to what your connection is related to. Putting that together:

Try{

  $SQLServer = "ABCD\ABC" 
  $SQLDBName = "ABC_1223"

#This is the here-string
  $SqlQuery = @"
    select * from table
    where we can select stuff
    and filter it
    and join
    etc
"@

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server = $SQLServer; Database  =$SQLDBName;Application Name = 'user2075017_db_call';uid=$SQLDBName;pwd= $pwd; Integrated Security = True" 

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = $SqlQuery
  $SqlCmd.Connection = $SqlConnection 
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd 
  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet) 
  $DataSet.Tables[0]
}
Catch{
}
Finally{
  $SqlConnection.Close()
  $SqlConnection.Dispose()
}

How to run sql script using powershell?

1 Answers