current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

Full Question: Have Powershell Script using Invoke SQL command, using snappins, I need them to be included in a SQL job, the SQL Server version of Powershell is somewhat crippled, does anyone know a workaround?

From what I have gathered, SQL Management Studio's version of powershell is underpowered, not allowing for the use of snappins, as such it does not recognize the cmdlets that I used in the script. I have tried running it in the job as a command line prompt rather than a Powershell script, which causes the code to work somewhat, however I check the history on the job and it says that invoke-sql is still not a recognized cmdlet. I speculate that because I am running the code on a remote server, with different credentials than my standard my profile with the snappins preloaded isn't being loaded, though this is somewhat doubtful.

Also, as I am a powershell rookie, any advice on better coding practices/streamlining my code would be much appreciated!

Code is as follows:

# define parameters
param
(
$file = "\\server\folder\file.ps1"
)

"invoke-sqlcmd -query """ | out-file "\\server\folder\file.ps1"

# retrieve set of table objects
$path = invoke-sqlcmd -query "select TableName from table WITH (NoLock)" -database db -server server

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions

 $so.DriPrimaryKey = $false
 $so.Nocollation = $true
 $so.IncludeIfNotExists = $true
 $so.NoIdentities = $true
 $so.AnsiPadding = $false

# script each table
foreach ($table in $path)
{
#$holder = $table
$table =  get-item sqlserver:\sql\server\default\databases\database\tables\dbo.$($table.TableName)
$table.script($so) | out-file -append $file 
}


(get-content "\\server\folder\file.ps1") -notmatch "ANSI_NULLS"  | out-file "\\server\folder\file.ps1" 
(get-content "\\server\folder\file.ps1") -notmatch " AS "| out-file "\\server\folder\file.ps1" 
(get-content "\\server\folder\file.ps1") -notmatch "Quoted_" | out-file "\\server\folder\file.ps1"
(get-content "\\server\folder\file.ps1") -replace "\) ON \[PRIMARY\].*", ")" | out-file "\\server\folder\file.ps1"
(get-content "\\server\folder\file.ps1") -replace "\[text\]", "[nvarchar](max)" | out-file "\\server\folder\file.ps1"
(get-content "\\server\folder\file.ps1") -replace " SPARSE ", "" | out-file "\\server\folder\file.ps1"
(get-content "\\server\folder\file.ps1") -replace "COLUMN_SET FOR ALL_SPARSE_COLUMNS", "" | out-file "\\server\folder\file.ps1"


""" -database database -server server" | out-file "\\server\folder\file.ps1" -append
share|improve this question

3 Answers 3

up vote 1 down vote accepted

So I figured out the answer to my own question. Using this site: http://www.mssqltips.com/tip.asp?tip=1684 and http://www.mssqltips.com/tip.asp?tip=1199

I figured out that he was able to do so using a SQL Server Agent Proxy, so I followed the yellow brick road, and basically I set up a proxy to my account and was able to use the external powershell through a feature. A note, you need to create a credential under the securities tab in object explorer prior to being able to select one when creating the proxy. Basically I ended up creating a proxy named powershell, using the powershell subsystem, and use my login info to create a credential. VOILA!

share|improve this answer
up vote 0 down vote

You have to add the snapins each time. In your editor you likely already have them loaded from another script/tab/session. In SQL Server you will need to add something like this to the beginning of the script:

IF ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
        Add-PsSnapin sqlserverprovidersnapin100
    }
IF ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
        Add-PsSnapin sqlservercmdletsnapin100
    }
share|improve this answer
    
See the problem is that sql server doesn't allow for the use of snapins as such get-pssnapin and add-pssnapin aren't recognized as evidenced by the error: – Jerome Jul 7 '11 at 17:41
    
@user - you didn't post your error. Can you do that? – JNK Jul 7 '11 at 17:44
    
A job step received an error at line 1 in a PowerShell script. The corresponding line is 'IF ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null )'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Get-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. – Jerome Jul 7 '11 at 17:44
    
@user - is this SQL Server 2008 r2 or non-r2? – JNK Jul 7 '11 at 17:57
    
it is SQL server 2008 r2 for sure – Jerome Jul 7 '11 at 18:51
up vote 0 down vote

I'm not sure the error you are trying to workaround - can you post that?

Have you tried this from a PowerShell prompt?

Add-PSSnapin SqlServerCmdletSnapin100

share|improve this answer
    
Yea, I have, it works fine at a powershell command prompt, the problem is that it doesn't work within SQL Managements Studios version of powershell, which doesn't allow snapins. – Jerome Jul 7 '11 at 17:43
    
@user823694 what's the error you are getting? – Matt Jul 8 '11 at 7:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.