OR
-How to load SQL Server Snapins into PowerShell Session. for SQL Server 2008 R2 box?
-It not required if it is SQL Server 2012 and above.
PowerShell run/execution issue for SQL Server 2012 box ps/scripts on SQL Server 2008 R2 box.
Developed powershell/sql scripts in SQL Server 2012 env box and moved/rollout to SQL Server 2008 env box. The ps scripts got failed and got below error.
Error: "The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program..."
"The specified module 'sqlps' was not loaded because no valid module file was found in any module directory."
Background: The SQLPS module is Powershell module introduced in SQL Server 2012. Prior to the 2012 release Powershell support in SQL Server was provided by a mini-shell i.e. sqlps.exe introduced in SQL 2008 with some minor enhancements in SQL Server 2008 R2.
Execution options on different SQL Server version:
SQL Server 2012: If install sql server 2012, default sqlps module and comdlets will be installed. Can use below script and execute on SQL Server 2012 box.
TestPShell.ps1
Import-Module SQLPS -DisableNameChecking
invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"
SQL Server 2008 R2: If install sql server 2008 R2 on box, then "Import-Module SQLPS" can not work due to sqlps module not available directly. In-order to work sqlps on sql server 2008 R2 box, add below code inplace "Import-Module SQLPS" for above "TestPShell.ps1" PS script. Then invoke-sqlcmd will work.
TestPShell.ps1
#Import-Module SQLPS -DisableNameChecking ----->This is not requied if it is SQL Server 2008 box(powershell).
if ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin sqlserverprovidersnapin100
}
if ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin sqlservercmdletsnapin100
}
invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"
Note: Load the following 2 snapin's and execute ps/sql scripts using invoke-sqlcmd command.
SqlServerCmdletSnapin100
SqlServerProviderSnapin100
-How to load SQL Server Snapins into PowerShell Session. for SQL Server 2008 R2 box?
-It not required if it is SQL Server 2012 and above.
PowerShell run/execution issue for SQL Server 2012 box ps/scripts on SQL Server 2008 R2 box.
Developed powershell/sql scripts in SQL Server 2012 env box and moved/rollout to SQL Server 2008 env box. The ps scripts got failed and got below error.
Error: "The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program..."
"The specified module 'sqlps' was not loaded because no valid module file was found in any module directory."
Background: The SQLPS module is Powershell module introduced in SQL Server 2012. Prior to the 2012 release Powershell support in SQL Server was provided by a mini-shell i.e. sqlps.exe introduced in SQL 2008 with some minor enhancements in SQL Server 2008 R2.
Execution options on different SQL Server version:
SQL Server 2012: If install sql server 2012, default sqlps module and comdlets will be installed. Can use below script and execute on SQL Server 2012 box.
TestPShell.ps1
Import-Module SQLPS -DisableNameChecking
invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"
SQL Server 2008 R2: If install sql server 2008 R2 on box, then "Import-Module SQLPS" can not work due to sqlps module not available directly. In-order to work sqlps on sql server 2008 R2 box, add below code inplace "Import-Module SQLPS" for above "TestPShell.ps1" PS script. Then invoke-sqlcmd will work.
TestPShell.ps1
#Import-Module SQLPS -DisableNameChecking ----->This is not requied if it is SQL Server 2008 box(powershell).
if ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin sqlserverprovidersnapin100
}
if ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin sqlservercmdletsnapin100
}
invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"
Note: Load the following 2 snapin's and execute ps/sql scripts using invoke-sqlcmd command.
SqlServerCmdletSnapin100
SqlServerProviderSnapin100
