Tuesday, June 17, 2014

Power Shell did not recognize invoke-sqlcmd on SQL Server 2008 R2 box for Power Shell/SQL scripts

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




No comments:

Post a Comment