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, Commented Aug 18, 2014 at 10:31
@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
– user2075017, Commented Aug 18, 2014 at 10:33

Martin Smith · Accepted Answer · 2017-05-20 16:40:10Z

5

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()
}

edited May 20, 2017 at 16:40

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

answered Aug 18, 2014 at 12:59

nimizen

3,4292 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2075017 Over a year ago

HI @nimizen , Thanks alot for your help in this.My requirement is to run an sql script abcd.sql using powershell without invoke and the above code almost resembles the one which i wrote for a sql query..any further suggestions for script instead of query ?

Collectives™ on Stack Overflow

How to run sql script using powershell?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related