Powershell sending wrong exit code to SQL Agent

I went to the edge of the Internet, and could not find the answer to my Powershell question.  Say I returned exit code from Powershell. SQL Agent, running the command from CmdExec, would always receive an exit code of 0.   It didn’t matter what I tried from all of the suggestions.  The Powershell would fail, but Agent always thought that it succeeded.

This solution ended up working.  Use “[Environment]::Exit(1);” rather than “Exit 1;”  In SQL Agent, you can look for a box in the job step area for CmdShell, and tell it what exit code represents success.  You’d want to change this to 1, if that represents success for your script.    The StackOverflow question can be found here:  http://stackoverflow.com/questions/8902004/powershell-fails-to-return-proper-exit-code

Although the question shows a solution, it doesn’t state any reasons.   I found that it was because of [Parameter(Mandatory=$True)] in the parameter portion of the Powershell script.  My script had this, and so does the one in the StackOverflow question.

Once I ripped out [Parameter(Mandatory=$True)] from the script, it processed the exit codes normally.  Now, this is a little inconvenient, since I do want those parameters to be mandatory.   With this knowledge, I was able to find the StackOverflow question that pointed to the answer.  Use [Environment]::Exit(1);

Posted in Powershell | Tagged | Leave a comment

SMO detailed errors

I was starting to regret using SMO, because  of the vague error messages.

Here is an example:
Exception calling “SqlRestore” with “1” argument(s): “Restore failed for Server ‘SM001′. ”

But this was fairly easy to resolve with an extra piece of code in my catch block:

Here is the new error message:

There is insufficient free space on disk volume ‘E:\’ to create the database. The database requires 6346801152 additional free bytes, while only 5079326720 bytes are available.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Posted in Uncategorized | Leave a comment

Church attendance over time

Recently, I became interested in seeing church attendance numbers over time.  So I requested the numbers from the church office, and put them in a chart.

My church has two services.  The one at 8:45 is traditional, and the 11:00 is contemporary.

At the end of 2013, another church merged in with ours, and most of the new people chose to attend the 8:45 service.

This chart shows average monthly attendance (smoothing out the lowest and highest of the month):


Here is a chart showing average annual attendance:


As a long time attender of the 11:00, I could sense that we had fewer people over time, but wasn’t sure.  As you can see, the decline is small – about 8%.   The 8:45 service increased in number by 30-35% when the other church merged in.

*Easter and Christmas were omitted from the chart.

Posted in Uncategorized | Leave a comment

Search term after the WHERE

I got a request from someone to find a certain word, but it has to be after the last WHERE clause, not in the SELECT list.  This is not perfect – technically you could have a subselect with a where clause, and no actual WHERE clause in the main query.  But most of the time, there will be a final WHERE clause, and this will pick up the search term after that final clause.

;WITH ObjectDefinition AS(

SELECT views.name,
FROM sys.views
FROM ObjectDefinition
WHERE SUBSTRING(Defn, LEN(Defn) – PATINDEX(‘%EREHW%’, REVERSE(Defn)), 2000) like ‘%SearchTerm%’

Posted in Uncategorized | Leave a comment

Powershell SMO Job Object

I am trying to kick off a job using SMO objects. And encountered the following error:

Cannot find an overload for “Job” and the argument count: “1”. This simply means that the parameters weren’t used correctly. There should be two parameters, and should look something like this.  See the last line, below

#Load SMO Objects
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null

$SourceFullServerName = "DanServer";
$JobName = "Dan | Fail This Job";

#Set SMO Server object
$SourceServer = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SourceFullServerName;

$Job = $SourceServer.JobServer.Jobs[$JobName];

At first I was using this, but got another error when starting the job
$Job = New-Object Microsoft.SqlServer.Management.Smo.Agent.Job ($SourceServer.JobServer, $JobName);
Exception calling “Start” with “0” argument(s): “Start failed for Job ‘Dan | Fail This Job‘.”

If you use the following code, it will provide a more detailed error message (reference here):
$error[0]|format-list -force;

This provides the following additional information:
Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException: You cannot execute this operation since the object has not been created.

For more troubleshooting, add this line.
“Date Created: ” + $Job.DateCreated;
In my case, the job did not even return anything, so I did something wrong.

It turns out that I had a lot more luck with the following:

$Job = $SourceServer.JobServer.Jobs[$JobName];

Alternatively, if you want to use the JobID instead of the JobName, you can use the GetJobByID method:

$Job = $SourceServer.JobServer.GetJobByID($JobID);
Posted in Powershell | Tagged | Leave a comment

Running remote Powershell script in SQL job

A job was not able to complete when running a remote Powershell script. It would not really start either, but was just running indefinitely. I asked the question on StackExchange, and eventually found the answer.


This article was helpful in troubleshooting the question.


Posted in Powershell | Leave a comment

Move Hallengren Backups to instance folder

The Ola Hallengren backup solution creates folder names of the concatenation of SERVER + $ + INSTANCE.

I wanted to move these folders into a parent directory of INSTANCE. That is because I have several servers with the same instance name, and this is how I want it organized.

The dollar sign has to be handled in Powershell. This code uses the REPLACE function to replace the $ with `$. The backtick is the escape character for dollar sign.

The script finds the substring after the dollar sign, and creates a folder of that name. Then it moves the original folder into the new folder.

$dir = "\\FileServer\SQLBackups\Product";

$NameGroup = Get-ChildItem $dir | select name;

foreach($Name in $NameGroup) {

$NewDir = $Name.Name;
$SubDir = $NewDir.Substring( $NewDir.IndexOf("$") + 1, $NewDir.Length - $NewDir.IndexOf("$") - 1);

$OldFullFolder = $dir + "\" + $NewDir;
$OldFullFolder.Replace("$", "`$");
$NewFullFolder = $dir + "\" + $SubDir;

New-Item $NewFullFolder -type directory;
Move-Item $OldFullFolder $NewFullFolder;

Posted in Powershell | Leave a comment