PowerShell / Microsoft.SqlServer.Smo – Is Server up?

Background

Stole a code for my Bro A.M.

Hit him up on Linked In to give him on credit.

Relating to these Industry luminaries on a different level now, no more bootlegging.

Legit Stealing.

Wish the same on the next ones behind me on the Street; swear on my Mama.

Code

Outline

  1. param
    • Accept the following parameters
      • $sqlInstance
  2. Strict Mode
    • Version 2
  3. Declare Variables
    • $bSQLInstanceConnected ( Boolean )
  4. Load Modules
    • Microsoft.SqlServer.Smo
      • Partial Name
      • Using Partial Name as it is safe in this case
  5. Instantiate Object
    • Type Microsoft.SqlServer.Management.SMO.Server
    • Parameters
      • $sqlInstance
    • Result is saved in variable $srv
  6. Capture $srv.Information.Version
  7. If $srv.Information.Version is null, then tag connection as failed
  8. Else display $srvInformation.Version

PowerShell

param (
    [Parameter(Mandatory=$true)][string]$sqlInstance
)
#Strict Mode
Set-StrictMode -Version 2.0

[Boolean]$bSQLInstanceConnected = $false;

#Load
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

#Get a server object which corresponds to the passed in sql server instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $sqlInstance

#Server Information Version
$srvInfoVersion = $srv.Information.Version

if ( ($srvInfoVersion -eq $null) )
{

	$bSQLInstanceConnected = $false;

}
else
{

	$bSQLInstanceConnected = $true;

}

if ($bSQLInstanceConnected -eq $true )
{

	$log = "Connected to SQL Instance {0}" -f $sqlInstance;

	Write-Host ($log);

	$srvInfoVersionMessage = "`tSQL Information Version :- {0}" -f $srv.Information.Version

	Write-Host $srvInfoVersionMessage

}
else
{

	$log = "SQL Instance {0} is not available" -f $sqlInstance;

	Write-Host ($log);

}

Output

localhost.connected.20190719.0926PM

Source Code

GitHub

DanielAdeniji/SQLServerPowershellSMO
Link

David Heinemeier Hansson – Good Programmers

David Heinemeier Hansson

Medium

Writing for Medium, Christophe Limpalair ( from Linux Academy ) interviews David Heinemeier Hansson.

The post is here.

Points

Here are some good points from the post.

  1. Code Presentation
  2. Code Quality
    • Bad Looks
      • Having methods that are 15 lines long and do 5 different things
      • Tons of global variables
      • Poorly named variables
      • Poor commenting
  3. Follow Standards
    • Look to see available standards and used them
    • Rather, than develop everything from scratch
  4. Read Books
    • Kent Beck
      • Smalltalk best practice patterns
    • Robert Cecil Martin
      • Clean Code by Robert C. Martin
    • Martin Fowler
      • Refactoring: Improving the Design of Existing Code
  5. Open Source Contributions
    • While David emphasized that you do not need to have contributed to open source projects, it can be very beneficial.
    • Not only does it give you extra practice at making your code better, but you also make connections.
  6. Write Code
    • For the last test of their hiring process, Basecamp makes you write code for them. They pay you to work on a side project so they can judge you on your skillset.
    • Once you go through that process with a number of candidates, it’s very clear who you need to hire.
    • You get to see how they work, think, and solve problems.

 

Artifacts

Presentation

Presentation – Worrisome

presentation.bad

 

Presentation – Better

presentation.better.PNG

My Take

Commented Out Code

  1. Remove commented out code if you have not re-added them back in while
    • I suppose place a date annotation indicating the date you originally commented it out
    • And, may be the premise
    • I suppose it will help to place commented out code in its own function
      • Original Code can be more easily identified
      • And, compared to revised side by side one

Owed

We owe a lot to guys such as David Heinemeier Hansson and Christophe Limpalair.

Inclusive :-

  1. Writing great software
  2. Sharing stringent principles

There is so much I happily lived with and now I can start to correct just because someone took the time to point it out.

Transact SQL – Error – “Msg 14607 – Procedure msdb.dbo.sysmail_verify_principal_sp – principal name is not valid”

Background

Needing to deploy email notification across a couple of SQL Instances.

I am not into clicking through screen shots and so I am going to prepare a Transact SQL Script.

Error

Ran into an error

Image

error.Msg.14607.20190719.0133PM

Textual


Msg 14607, Level 16, State 1, Procedure msdb.dbo.sysmail_verify_principal_sp, Line 49 [Batch Start Line 8]
principal name is not valid

Trouble Shooting

The error message states the principal is no good.

Command

The command that failed is :-


EXEC msdb.dbo.sysmail_add_principalprofile_sp 

      profile_name=@mailProfile

    , @principal_name=@principal

    , @is_default=0

Principal

Instance

Outline

Is Principal registered on the SQL Instance

SQL


declare @principal    sysname;
declare @principalSID varbinary(85);
declare @principalID  int;

set @principalSID = SUSER_SID(@principal)

set @principalID = SUSER_ID(@principal)

Database

Outline

Is Principal registered in msdb

SQL

use msdb;
declare @principal    sysname;
declare @userID       int;

set @userID = USER_ID(@principal)

Remediation

In our case, the principal did not have an account in msdb.

To satisfy that requirement, issued “CREATE USER [principal] from LOGIN [principal]“.

 

Reference

  1. Microsoft
    • sysmail_add_principalprofile_sp
      • Docs / SQL / Reference / System stored procedures / Database Mail / sysmail_add_principalprofile_sp
        Link

Ministry of Love & Care

Background

Public Transportation is the only means I get to see and experience a bit more these days.

City Square

As I waited on the L Train yesterday evening, heard a young lady playing her music loud enough to be heard.

YNW Melly

Asked who she listening to, she said YNW Melly.

What song ? Slang That Iron.

Out for a while ? Don’t really matter, he on “Death Row“.

Songs

  1. Quando Rondo
    • Bacc To The Basics
      • Quando Rondo – Bacc To The Basics (Live Performance) 03/08/19 | PT 2
        Channel :- gSP1k
        Published On :- 2019-March-10th
        Link
      • Quando Rondo -Bacc to the Basics (Official Music Video)
        Channel :- Quando Rondo
        Published On :- 2018-Sept-24th
        Link
    • I remember
      • Quando Rondo – I Remember (feat. Lil Baby) [Official Music Video]
        Channel :- Quando Rondo
        Published On :- 2018-January-26th
        Link
  2. YNW Melly
    • Slang That Iron
      • Videos
        • YNW Melly “Slang That Iron” (WSHH Exclusive – Official Music Video)
          Channel :- WORLDSTARHIPHOP
          Published On :- 2018-July-30th
          Link

Quotes

YNW Melly

  1. Slang That Iron
    • And I really don’t feel the way I once felt about school
      Ma it’s not you, it’s me, I’m
      Just so fucked ’bout this street

Quando Rondo

  1. Bacc To The Basics
    • Straight off the head but they gon’ think that I wrote this
    • This a real life story so I gotta keep it real
      Stack-stack that money for a lawyer I been told that since a lil’ nigga
    • Shawty so one-hunnit, swear to God she keep it G though
      But if I gotta beat you then baby I don’t need you
    • More money, more problems, they be feelin’ on the problem solvers
    • know they hate I came up I see the look on all they faces
      My last bitch changed up I left her in a free agent
      Too many missed calls, why the fuck they keep on callin’ me?
      Shawty on the block list why the fuck she keep on stalkin’ me?
    • I let that hoe meet mother so now she thinkin’ she a Bowman
      I’ma fuck with no rubber see I don’t really need no Trojan
      I was sittin’ in a cell when I made that “I Remember”
      Now I’m fuckin’ all these bitches and they name I don’t remember

      • Bowman” is his Government Name
      • I Remember” is his break out track

 

In Closing

I couldn’t walk from shortie; seeing her dream locked up in/with YNW Melly.

I swear EPMD hit me me same way; when he plead and bled on “Please listen to my demo“.

Wifey always reminds me you can’t help everybody.

Help me, as I help you.

Keep me, as I keep you.

Every body has a story, ours just happen to be soul.

In time, we all get committed out, aged out.

So if you are doing it, if you are committed, please do it, continue doing it, as hard as you can, as well as you can, for as long as you can.

 

Dedicated

Dedicated to so many.

So many diamond, peachy pieces.

Quickly got beyond how she look.

When she turn and how she walk away, only thing I took and kept is her heart cry and say I wish I could do for mine; do for my people.

Don’t ask me about no f..ed up n..

I just opened up to you, the ache in my heart, and how am I mend the break in it, but you gonna down and drown me.

I thought we could relate on something that is real.

But, you starting to remind me of the same fools; we just got through talking about.

I swear on my mama back side, next time I see you, am I overlook your AZZ.

 

Jennifer Gates & Nayel Nassar

Background

In a world far too noisy, how does Heaven get a word in…

Jennifer Gates & Nayel Nassar

In Pictures

Equestrian Athletes

NayelNassar.JenniferGates.01.jpg

Egypt – 01

NayelNassar.JenniferGates.Egypt.01

Egypt – 02

NayelNassar.JenniferGates.Egypt.02.jpg

In Love

NayelNassar.JenniferGates.02.jpg

Quotes

Gates

  1. She is now enjoying the responsibility of being a team manager but is just happy to be successful in her childhood passion.
    • “I always loved horses. I convinced my parents to let me start taking lessons at a local barn, and things progressed from there,” she said.
    • “I never knew that I would be competing at this level today. I always thought it would just be a childhood hobby, but I’m so grateful that it still has a place in my life.”
  2. Gates graduated from Stanford University in 2018 and took a year out to concentrate on her sporting ambitions, but will soon be returning to her studies.
    • “Med. school will, of course, be a new challenge, but I love staying mentally active in whatever I’m doing,” she added.
    • “I found that when I was at Stanford, I was able to compete, and I’ll just have to play things by ear. But whether I can compete at this level or not, I know that horses will be a huge part of my life”
  3. Giving Back
    • She revealed that her father loves the thrill of the event and can also be seen riding in his spare time.
    • “I couldn’t do this without their support, both emotionally and with the horses. They cheer me on at every event,” she added.
    • Much has been made of Bill and Melinda Gates’ charity work — the couple have donated more than $28 billion to different causes — and their philanthropy has rubbed off on their daughter who wants to explore her own projects in future.
    • “They’ve inspired me to be a lifetime learner but also inspired me to think about ways to give back,” she said, admitting she wasn’t sure what her future profession would be.
      “I’m incredibly fortunate to have the resources that I do, so whether that’s in the equestrian world or in other pursuits, just thinking of ways to help others is a message that I will carry with me forever.”
  4. Special Relationship
    • As well as relying on her parents for support, Gates can also lean on Egyptian boyfriend Nayel Nassar who competes professionally for the Paris Panthers.
    • Together they tour a host of cities, sharing their love for the equine sport.
      “It’s really special,” she smiled. “Horses are just one part of our life, but we love the sport.
    • He’s a professional, and I do this as an amateur. So, to be able to share our love and passion for horses with each other is just incredible.”

Jeff Bezos

  1. I spend my billions on space because we’re destroying Earth.

 

Summary

I smile when I see grown men try to discourage others.

As my favorite philosopher says, “All the problems you have, you still want problems though“.

My favorite prof goes by Cole.

Its funny I barely told nobody I started rapping
‘Cause see some niggas was haters
I gotta make a move
If they don’t know your dreams, then they can’t shoot em down

Listening

Drake
Do Not Disturb

Link

Referenced Work

  1. Bill Gates
    • The Giving Pledge :– +Bill & Melinda Gates +Warren Buffet
      Link
    • WALTER ISAACSON
      • Time Magazine
        • IN SEARCH OF THE REAL BILL GATES
          Sunday, June 24, 2001
          Link

Jesus & Paul – Turning over to Satan

Jesus

Verses

Matthew 5:37 ( Link )

All you need to say is simply ‘Yes’ or ‘No’; anything beyond this comes from the evil one.

 

Paul

Verses

1 Timothy 1:20 ( Link )

…holding on to faith and a good conscience, which some have rejected and thereby shipwrecked their faith.

Among them are Hymenaeus and Alexander, whom I have handed over to Satan to be taught not to blaspheme.

 

1 Corinthians 5:5 ( Link )

…When you are gathered in the name of our Lord Jesus…., hand this man over to Satan for the destruction of the flesh, so that his spirit may be saved on the day of the Lord.

Your boasting is not good

XPath – Axis

Definition

Definition matters.

W3C – XPath

W3C defines Axis this way :-

Link

  1. An axis is either a forward axis or a reverse axis.
    • Forward Axis
      1. An axis that only ever contains the context node or nodes that are after the context node in document order is a forward axis.
    • Reverse Axis
      1. An axis that only ever contains the context node or nodes that are before the context node in document order is a reverse axis.
      2. Thus, the ancestor, ancestor-or-self, preceding, and preceding-sibling axes are reverse axes; all other axes are forward axes.
  2. Self Axis
    • The self axis always contains at most one node, it makes no difference whether it is a forward or reverse axis

Lab

As one plays with XPath one will get into the weeds in terms of how fully each environment supports the various XPath functions.

XML

Here is how original XML.

Image

foo.xml

XPath Query

Goal Xpath Query
Descendants
Find elements matching foo, filter on attribute id=2, get descendants //foo[@id=2]/descendant::*
Find elements matching foo, filter on attribute id=2, get first descendant node //foo[@id=2]/descendant::*[position()=1]
Find elements matching foo, filter on attribute id=2, get last descendant node //foo[@id=2]/descendant::*[last()]
Ancestor
Find elements matching foo/tar/bar, filter on attribute atrr=samuel, get ancestor //foo/tar/bar[@attr=’samuel’]/ancestor::*
Find elements matching foo/tar/bar, filter on attribute atrr=samuel, get ancestor with element matching tar //foo/tar/bar[@attr=’samuel’]/ancestor::tar
Find elements matching foo/tar/bar, filter on attribute atrr=samuel, get ancestor with element matching foo with attribute matching id is 3 //foo/tar/bar[@attr=’samuel’]/ancestor::foo[@id=3]
Find elements matching bar, filter on attribute atrr=samuel, get ancestor with element matching foo an attribute id is 3 //bar[@attr=’samuel’]/ancestor::foo[@id=3]

Tool

Here are tools you can try it out with :-

  1. XPather

XPather

Sample

XPather.XML.XQuery.Ancestor.element.foo.attribute.id.is.3