Tuesday, December 10, 2019

MS Teams - posting results of a SQL query as a table via Powershell.

(update 2019/12/10 13:33 - fixing the tables; the formatting I included got screwed up because Blogger decided to render it)

TL;DR - below is code that will post the results of a SQL query, as a table, in a channel within MS Teams.

Special thanks to http://mikeconjoice.com/2018/05/25/send-a-notification-to-microsoft-teams-via-powershell/, who had the original post/code I used.

First of all - MS, you should be embarrassed. All the documentation says to use Office 365 Connector Cards/Adaptive Cards, but it doesn't work for teams as of December 2019 ("Supported in Teams, not in Bot Framework."), and there's no sense that it'll ever happen, given there's a Connect/User Voice that's been open a year with no updates.

A) Here's some documentation that doesn't actually work for what we're doing.
https://docs.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/connectors-using
https://techcommunity.microsoft.com/t5/Microsoft-Teams/Microsoft-Teams-Incoming-Webhook-Message-Formatting/m-p/31984
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
https://messagecardplayground.azurewebsites.net/
https://stackoverflow.com/questions/58598592/microsoft-teams-escaping-underscores-in-text-posting-to-webhook

* Note there's a max of 20711 characters in the JSON, and 10 sections in the JSON. (Somewhere else on StackOverflow it says 28k for an image is the max size of that)
* Note there's also a limit how often you can post, and it's a "back-off" algorithm, IIRC.

So.... you want to post to a Teams channel automagically.  Should be simple, and it is!  Alas, it means you have to ignore most of the documentation.  Let's do this!

1) Here's how to hook it up with your channel.  Note that when I created a brand new "Team", it took about 5-10 minutes before I was able to add the webhook connector - prior to that, I got a "channel does not exist or has been deleted".
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
(scroll down)

2) Getting a basic post working - this works and will post text.  Note that you can split lines by using "/r/n" in the text, because it sort of supports formatting (see #3)

$webhook_url = "https://outlook.office.com/webhook/  ......" #URL when you added the connector

$json = @"
{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "text": "Hi Mom",
  "version": "1.0",
  "type": "AdaptiveCard"
}
"@

Invoke-RestMethod -Method post -ContentType 'Application/Json' -Body $json -Uri $webhook_url


3) Adding a table:
Basic HTML is supported.  And I mean BASIC. It supports less formatting than Outlook (I had a border and had to remove it).


So the JSON would look like:




4) Putting it all together, we can do something like this, which will query SQL Server using invoke-sqlcmd2, create the proper JSON, and post it to our channel (note the below SQL is incomplete):



5) And here's what the powershell script returns, if successful:

1

and in Teams...


If you don't do it right, you'll get errors like:
Invoke-RestMethod : Summary or Text is required.
or (the typical "you didn't do it perfectly") of:
Invoke-RestMethod : Bad payload received by generic incoming webhook.

Wednesday, November 13, 2019

[Powershell] loading a module if the SQL Server Agent can't do so automatically

I'm trying to use the SQL Server Agent to run a Powershell script that calls AWS.

But it failed.  

The term 'get-awscredentials' 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.  

Which is odd because if I start a brand-new ISE on the box, it works.  Grr.

Okay, I must've forgotten the module.  But again, I don't need it with the ISE...

import-module awspowershell

But I got a different error:

import-module : The specified module 'awspowershell' was not loaded because no   valid module file was found in any module directory.  

Okay, so let's add it.

Where the heck is the module, anyways?
From the ISE, after loading the module by hand, run:

(Get-Module -ListAvailable awspowershell*).path


Then, now that we know that path:

import-module -name "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\awspowershell"
 
(note awspowershell is in there twice - it's looking for the... PSD1, I think?)

Tuesday, May 7, 2019

[Table Partitioning] Does WAIT_AT_LOW_PRIORITY work with Standard Edition?

(TL;DR - yes.  Yes, it does.)

As you hopefully know, back with SQL Server 2016 SP1, Microsoft opened the floodgates of formerly-only-enterprise-features (https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1). 

But with caveats.

For instance, IMOLTP (in-memory online transactional processing, aka "in-memory") tables are limited in the amount of RAM used. 

But how about WAIT_AT_LOW_PRIORITY?  That was introduced in 2014 to make table partitioning deal better with That-Dude-From-Accounting-Who-Kicks-Off-A-Massive-Query-On-Friday-at-5pm, which causes partitioning to hang on Saturday when you're trying to add and remove partitions.

Specifically, the notes don't say anything about it.  The closest they get is:
"WAIT_AT_LOW_PRIORITY used with ONLINE=ON only."

And ONLINE is an Enterprise-only feature.

So, does it or doesn't it?

For a simple example I actually used Aaron Bertrand's answer to a StackOverflow question:
https://dba.stackexchange.com/questions/155495/table-partitioning-in-sp1-for-sql-server-2016



So, what do we get?
Well, as pointed out by Joe Sack back in 2013(!), that feature will log to the Errorlog when it works. 
https://sqlperformance.com/2013/09/sql-indexes/lock-priority-sql2014

Let's try it out.  In the first window create the objects.  Once created, start a second window and open the transaction.  Then back to the first and run the SWITCHes and see what happens!

...Yup! It works in Standard!

Here are the messages I got:

Date 5/7/2019 3:41:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
An ALTER TABLE SWITCH statement was executed on database 'eif_workspace', table 'partitionexample' by hostname 'CH004547', host process ID 30812 with target table 'staging_FactInternetSales' using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.

, followed a minute later by: 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 1182627256. All blocking user sessions will be killed.

and 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
Process ID 63 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 1182627256.


I was figuring/hoping it'd work, but that example using ONLINE = ON gave me pause.
Proof, and a blog post!


Thursday, May 2, 2019

[Linked Servers] Kerberos Double Hop - SPNs and Trusted for Delegation

Yesterday I ran into the dread Kerberos Double-Hop when trying to set up a linked server.  Thought it was the standard "Add an SPN using the Microsoft Kerberos Configuration tool".  Which didn't fix it.

What the....

Turns out there's another potential setting you have to fix. 

In active directory, the service account that SQL Server is running under may need this setting:


Tuesday, February 26, 2019

[Powershell] Tracking down that failing app on another server using WMI's win32_process and Profiler

(Yes, I'm still using profiler in some cases)

As we continue to move off SQL Server 2008, we've found instances where we change all the application configs, yet something on ServerA is still querying ServerB.  And since our internal application doesn't have the connection string set to give an ApplicationName, we have no idea which one is misconfigured - or if one was missed entirely!

1) Set up a profiler trace on one side, making sure to grab ClientProcessID



2) Run this WMI query on ServerA, using Powershell. 


$loopcount = 1
$futuretime = (get-date).addseconds(60)
$processeses = gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" # run locally on the server making the calls for speed)
do {
$processeses += gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" -ComputerName "mem-qa-trdb-17"
$loopcount++while ((get-date-lt $futuretime)
$processeses | ogv

(using $loopcount will tell you how many times it ran in that minute)

You could run it remotely if you had the proper WMI rights set up (which is probably the case on WS2016+), but my fear is that it'd be slow enough that it wouldn't "catch" the app as it's running, if it's spinning up on its own, failing to connect, then quitting. 

If the app is just running and not starting/stopping, then it'll keep the same PID (process_id) and you can just run the first $processeses and filter it. 


3) When the ogv (out-gridview) runs, it'll pop up an interactive window.  Type the actual number for ClientProcessID you got from the profiler trace, in the "filter" box, and it should filter down to just the Process (and location of the executable) that you need to fix.  


Thursday, February 14, 2019

[EPR] Making the Extensible Powershell Repository work on a box with SQL Server 2017 since add-sqltable fails with Microsoft.SqlServer.Dmf

We're migrating the EPR (http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html) to a new box.  After 6 years, and it's still one of my best tools.  (Just drop a SQL or PS1 script in a folder, and it'll create a table and run it against all your servers).

Alas, Chad Miller's excellent add-sqltable started failing.  To the internet!

Could not load file or assembly 'Microsoft.SqlServer.Dmf"

Looking around, it appears that it's a problem with SMO.  On 2017, they split out SMO and SSMS.  Which means that installing SSMS 2017 doesn't get you SMO.  Supposedly installing it (install-module sqlserver) will do that, but I'd already did that and it wasn't working.

So, after fighting with it for a little bit, I decided to "get it working" and revisit later.

1) Install SSMS 2016 (which is when they started separating out SSMS)
2) Change the version from 11 to 13 in the script, as below: 

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} 
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"}