Sample Creating PowerShell SQL Agent Jobs

 

With the introduction of SQL Server 2008, Microsoft SQL Server Team introduce SQLPS.exe.   SQLPS.EXE mini-shell is based on PowerShell v 1.0 and includes the SQL Server provider and snapins.  Also, SQL Server 2008 setup will install the SMO (SQLServer Management objects) for you.

What is SQLPS?

The sqlps utility starts the PowerShell environment (PowerShell.exe) with the SQL Server PowerShell snap-ins loaded and registered. The SQL Server PowerShell snap-ins are as follows:

  • Microsoft.SqlServer.Management.PSProvider.dll
    Implements the SQL Server PowerShell provider and associated cmdlets such as Encode-SqlName and Decode-SqlName.
  • Microsoft.SqlServer.Management.PSSnapin.dll
    Implements the Invoke-Sqlcmd cmdlet.

You can use sqlps to do the following:

  • Interactively run PowerShell commands.
  • Run PowerShell script files.
  • Run SQL Server cmdlets. Only three:
    • Encode-SqlName
    • Decode-SqlName
    • Invoke-SqlCmd

Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.

Back to PowerShell SQL jobs:

There’s at least two ways to create a PowerShell job in SQL Server 2008 when creating a Job step:

1. Create new jobstep as a job type “Operating System (CMDExec)” then type the DOS Commands or Cmd batch file.

2. Or, create the jobstep as a job type “PowerShell” then type the PowerShell Cmdlets or PS

Script: (sample)

Both methods will result in generating a simple text report:

Here’s a list of PowerShell.exe parameters you can use at the DOS command line:

Microsoft Windows [Version 6.1.7600]

Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Max>PowerShell /?

PowerShell[.exe] [-PSConsoleFile <file> | -Version <version>]

[-NoLogo] [-NoExit] [-Sta] [-NoProfile] [-NonInteractive]

[-InputFormat {Text | XML}] [-OutputFormat {Text | XML}]

[-WindowStyle <style>] [-EncodedCommand <Base64EncodedCommand>]

[-File <filePath> <args>] [-ExecutionPolicy <ExecutionPolicy>]

[-Command { – | <script-block> [-args <arg-array>]

| <string> [<CommandParameters>] } ]

PowerShell[.exe] -Help | -? | /?

-PSConsoleFile

Loads the specified Windows PowerShell console file. To create a console

file, use Export-Console in Windows PowerShell.

-Version

Starts the specified version of Windows PowerShell.

-NoLogo

Hides the copyright banner at startup.

-NoExit

Does not exit after running startup commands.

-Sta

Start the shell using a single-threaded apartment.

-NoProfile

Does not use the user profile.

-NonInteractive

Does not present an interactive prompt to the user.

-InputFormat

Describes the format of data sent to Windows PowerShell. Valid values are

"Text" (text strings) or "XML" (serialized CLIXML format).

-OutputFormat

Determines how output from Windows PowerShell is formatted. Valid values

are "Text" (text strings) or "XML" (serialized CLIXML format).

-WindowStyle

Sets the window style to Normal, Minimized, Maximized or Hidden.

-EncodedCommand

Accepts a base-64-encoded string version of a command. Use this parameter

to submit commands to Windows PowerShell that require complex quotation

marks or curly braces.

-File

Execute a script file.

-ExecutionPolicy

Sets the default execution policy for the session.

-Command

Executes the specified commands (and any parameters) as though they were

typed at the Windows PowerShell command prompt, and then exits, unless

NoExit is specified. The value of Command can be "-", a string. or a

script block.

If the value of Command is "-", the command text is read from standard

input.

If the value of Command is a script block, the script block must be enclosed

in braces ({}). You can specify a script block only when running PowerShell.

exe

in Windows PowerShell. The results of the script block are returned to the

parent shell as deserialized XML objects, not live objects.

If the value of Command is a string, Command must be the last parameter

in the command , because any characters typed after the command are

interpreted as the command arguments.

To write a string that runs a Windows PowerShell command, use the format:

"& {<command>}"

where the quotation marks indicate a string and the invoke operator (&)

causes the command to be executed.

-Help, -?, /?

Shows this message. If you are typing a PowerShell.exe command in Windows

PowerShell, prepend the command parameters with a hyphen (-), not a forward

slash (/). You can use either a hyphen or forward slash in Cmd.exe.

EXAMPLES

PowerShell -PSConsoleFile SqlSnapIn.Psc1

PowerShell -version 1.0 -NoLogo -InputFormat text -OutputFormat XML

PowerShell -Command {Get-EventLog -LogName security}

PowerShell -Command "& {Get-EventLog -LogName security}"

# To use the -EncodedCommand parameter:

$command = ‘dir "c:\program files" ‘

$bytes = [System.Text.Encoding]::Unicode.GetBytes($command)

$encodedCommand = [Convert]::ToBase64String($bytes)

powershell.exe -encodedCommand $encodedCommand

C:\Users\Max>

 

The Benefit and the Drawback:

1. The Benefit: Using the “Operating System (CmdExec)”, you can extend your PowerShell v2 by adding the SQL Provider and Snapin into your PowerShell user profile. By Default, the user profile is loaded automatically when executing PowerShell from the DOS command prompt.

2. The Drawback: When, you add your code in a PowerShell job type, SQL Job Agent uses the SQLPS.exe subsystem which is based on PowerShell v1. So, you won’t be able to use PowerShell new cmdlets, such as “Send-MailMessage”. But, you can build your own .NET code to build your own email solution in PowerShell.  Or, just look for the code on the internet…  Well, here’s an example of building an email .NET solution for PowerShell v1:

[System.Reflection.Assembly]::LoadWithPartialName("System.Net.MAIL")
$mailmess = new-object system.net.mail.mailmessage
$mailmess.From = "PowerShell@YourServer.com"
$mailmess.To.Add("Somebody@YourServer.com")
$mailmess.Cc.Add("maxt@putittogether.net")
$mailmess.Subject = "Test send message .NET mail."
$mailmess.Body = "Should work!!!!!!!!!";
$mailclient = New-Object System.Net.Mail.SmtpClient
$mailclient.host = "PostOffice.YourServer.com"
$mailclient.send($mailmess)

Finally, after testing the PowerShell scripts, now we are ready to scheduling the job or mix with other jobs.

Go ahead and keep working with PowerShell!!

It Really ROCKS!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Sample Creating PowerShell SQL Agent Jobs

  1. Thanks for posting this wicked article!

  2. shruthi says:

    thank you.i now know how to execute ps on sql server

  3. szumigalski says:

    VMware Certified Advanced Professional 6 (Desktop and Mobility Deployment) – The industry-recognized VCAP6-DTM Deploy certification validates that you know how to deploy and optimize VMware Horizon 6 (with View) environments. It proves that you have the knowledge and skills vital to leverage best practices to provide a scalable and reliable Business Mobility platform for your business. Some of the subjects involve: Configuring and managing Horizon View components, configuring cloud pod archituecture, configuring Group Policy settings related to Horizon View, Configuring and optimizing desktop images for Horizon View & Mirage, Configuring and managing App Volumes AppStacks, Configuring desktop pools, Configuring and deploying ThinApp packaged applications, Configuring VMWare Identity Manager, etc.Szumigalski.com was created by Sebastian to spread his interest for PowerShell & VMWare. Sebastian is an IT expert working in Singapore for longer than fifteen years who’s often searching for new technologies to improve his technical competencies & knowledge. Since then, Sebastian has joined PowerShell User Group & VMWare VMug group, and has been engaged in most of the functions held in Singapore. This blog will highlight exactly how Sebastian manage to automate his everyday assignments with the help of PowerShell. You will find study guides created for the VCAP6-DTM exam, which were professionally created by Sebastian. Sebastian is certified with VCAP6-DTM, and is strong with virtualization & server maintenance from 4 years experience of automation. The interest in VMWare knowledgable admins and engineers are ever-increasing in today’s tech market place. Learn much more about PowerShell & VMWare at Szumigalski.com!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s