Drop login Powershell SMO

For some reason, Powershell is what I’m blogging about these days.  Maybe it is rare to use it in this way, so there is not much information out there.  So I want to be a help to others who need the answers.

I developed a process to drop temporary logins, but discovered a problem recently.  It could not drop the login because of the following error:  Detailed error:Could not drop login ‘LoginName’ as the user is currently logged in.  The individual using the login apparently doesn’t close their SSMS.

There doesn’t seem to be a streamlined way in SMO to drop all connections for that specific login.  But this blog post showed me the EnumProcesses() function, which allowed me to kill the connections for that login.

Without automating anything, here are the simple commands.

Search for connections/processes with login ‘cat’
$ServerObject.EnumProcesses() | Where-Object {$_.Login -eq “cat”}

SMOProcesses

Kill connections manually from above picture
$ServerObject.KillProcess(54);
$ServerObject.KillProcess(68);

For the automated piece, a typical foreach loop is fine

$SqlLoginName = “cat”;
$ConnectionsToDrop = $ServerObject.EnumProcesses() | Where-Object {$_.Login -eq $SqlLoginName};
foreach($ConnectionToDrop in $ConnectionsToDrop)
{  $ServerObject.KillProcess($ConnectionToDrop.Spid); }

Posted in Uncategorized | Leave a comment

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:
$error[0].exception.GetBaseException().Message;

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.

There was another service at 11:00 in the gym from around 2006 to 2009.  It became problematic to run both of these at the same time, so it ended.  But in terms of attendance, it seems like the gym service was a good thing to have.

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):

Attendance1

Here is a chart showing average annual attendance:

Attendance2

*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,
OBJECT_DEFINITION(OBJECT_ID) as Defn
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) like ‘%WHERE%SearchTerm%’
)
SELECT name
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];
$Job.Start();

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.

http://dba.stackexchange.com/questions/84126/execute-remote-powershell-script-from-sql-agent

This article was helpful in troubleshooting the question.

http://stackoverflow.com/questions/728143/ignore-security-warning-running-script-from-command-line

Posted in Powershell | Leave a comment