How do I run a sql script using powershell?
I wanted to run sql script using powershell but get error. "The term Invoke-sqlcmd" is not recognized as the name of a cmdlet, function, script file, or operating program. it typed the name or if the path was included, check the path is correct and try again. "
I found the below snippet from some site. but this is only for one sql command. But I wanted to run sql script.
Did anyone help with modifying 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]
source to share
You can use your code as is, just put your SQL script in Here-String (string literal). Also, I would wrap the SQL database call in a Try / Catch / finally construct to make sure your connections are closed and dropped. Finally, consider using the application name in your connection string to give your DBA a clue as to what your connection is associated with. Putting this 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()
}
source to share