I have a .sql file. I am trying to pass connection string details through a Powershell script and invoke an .sql file.
I was searching and came up with a cmdlet related to Invoke-sqlcmd. While I was trying to find a module corresponding to SQL, I did not find any one in my machine.
Should I install anything in my machine (the machine already has SQL Server Management Studio 2008 R2) to get the modules or is there any easy way to execute .sql files using Powershell?
Try to see if SQL snap-ins are present:
get-pssnapin -Registered
Name : SqlServerCmdletSnapin100
PSVersion : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name : SqlServerProviderSnapin100
PSVersion : 2.0
Description : SQL Server Provider
If so
Add-PSSnapin SqlServerCmdletSnapin100 # here lives Invoke-SqlCmd
Add-PSSnapin SqlServerProviderSnapin100
then you can do something like this:
invoke-sqlcmd -inputfile "c:\mysqlfile.sql" -serverinstance "servername\serverinstance" -database "mydatabase" # the parameter -database can be omitted based on what your sql script does.
Quoting from Import the SQLPS Module on MSDN,
The recommended way to manage SQL Server from PowerShell is to import the sqlps module into a Windows PowerShell 2.0 environment.
So, yes, you could use the Add-PSSnapin approach detailed by Christian, but it is also useful to appreciate the recommended sqlps module approach.
The simplest case assumes you have SQL Server 2012: sqlps is included in the installation so you simply load the module like any other (typically in your profile) via Import-Module sqlps. You can check if the module is available on your system with Get-Module -ListAvailable.
If you do not have SQL Server 2012, then all you need do is download the sqlps module into your modules directory so Get-Module/Import-Module will find it. Curiously, Microsoft does not make this module available for download! However, Chad Miller has kindly packaged up the requisite pieces and provided this module download. Unzip it under your ...Documents\WindowsPowerShell\Modules directory and proceed with the import.
It is interesting to note that the module approach and the snapin approach are not identical. If you load the snapins then run Get-PSSnapin (without the -Registered parameter, to show only what you have loaded) you will see the SQL snapins. If, on the other hand, you load the sqlps module Get-PSSnapin will not show the snapins loaded, so the various blog entries that test for the Invoke-Sqlcmd cmdlet by only examining snapins could be giving a false negative result.
2012.10.06 Update
For the complete story on the sqlps module vs. the sqlps mini-shell vs. SQL Server snap-ins, take a look at my two-part mini-series Practical PowerShell for SQL Server Developers and DBAs recently published on Simple-Talk.com where I have, according to one reader's comment, successfully "de-confused" the issue. :-)
if(Test-Path "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS") { #Sql Server 2012
Import-Module SqlPs -DisableNameChecking
C: # Switch back from SqlServer
} else { #Sql Server 2008
Add-PSSnapin SqlServerCmdletSnapin100 # here live Invoke-SqlCmd
}
Invoke-Sqlcmd -InputFile "MySqlScript.sql" -ServerInstance "Database name" -ErrorAction 'Stop' -Verbose -QueryTimeout 1800 # 30min
Here is a function that I have in my PowerShell profile for loading SQL snapins:
function Load-SQL-Server-Snap-Ins
{
try
{
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (!(Test-Path $sqlpsreg -ErrorAction "SilentlyContinue"))
{
throw "SQL Server Powershell is not installed yet (part of SQLServer installation)."
}
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
$assemblyList = @(
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.SmoExtended",
"Microsoft.SqlServer.Dmf",
"Microsoft.SqlServer.WmiEnum",
"Microsoft.SqlServer.SqlWmiManagement",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.Management.RegisteredServers",
"Microsoft.SqlServer.Management.Sdk.Sfc",
"Microsoft.SqlServer.SqlEnum",
"Microsoft.SqlServer.RegSvrEnum",
"Microsoft.SqlServer.ServiceBrokerEnum",
"Microsoft.SqlServer.ConnectionInfoExtended",
"Microsoft.SqlServer.Management.Collector",
"Microsoft.SqlServer.Management.CollectorEnum"
)
foreach ($assembly in $assemblyList)
{
$assembly = [System.Reflection.Assembly]::LoadWithPartialName($assembly)
if ($assembly -eq $null)
{ Write-Host "`t`t($MyInvocation.InvocationName): Could not load $assembly" }
}
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
Push-Location
if ((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{
cd $sqlpsPath
Add-PsSnapin SqlServerProviderSnapin100 -ErrorAction Stop
Add-PsSnapin SqlServerCmdletSnapin100 -ErrorAction Stop
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
Update-FormatData -PrependPath SQLProvider.Format.ps1xml
}
}
catch
{
Write-Host "`t`t$($MyInvocation.InvocationName): $_"
}
finally
{
Pop-Location
}
}
Here's a light weight approach for simple scripts that requires no additional tools / setup / PowerShell add-ons.
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connectionStringGoesHere
$conn.Open()
$content = Get-Content $scriptFileNameGoesHere
$cmds = New-Object System.Collections.ArrayList
$cmd = ""
$content | foreach {
if ($_.Trim() -eq "GO") { $cmds.Add($cmd); $cmd = "" }
else { $cmd = $cmd + $_ +"`r`n" }
}
$cmds | foreach {
$sc = New-Object System.Data.SqlClient.SqlCommand
$sc.CommandText = $_
$sc.Connection = $conn
$sc.ExecuteNonQuery()
}
