Azure SQL Database Elastic Pools - fun monitoring the resize.

Turns out the Cloud is, as previously surmised, is other people's computers. And they want their two dollars. 

So let's resize! And then get a notification when it's done!

The annoying part, though, is that for the FIRST change, it doesn't tell you ANYthing until you're done. Run the command, get back an empty prompt. Seriously it was a "WTF". 

AFTER it's done, the SECOND change you can monitor. 

This code will do both. Warning that it will Just Keep Notifying you because I am lazy and just wanted SOMETHING to work. 

And the part with writehost? here's what it looks like in my job history:

Oh, and percent done appears to START at 50%. Like I started a resize on a >1tb SQL DB, and under 2 minutes it says 50%. Sure, sure. *pat DB on head* who's a good little monitor?

```

Connect-AzAccount 

$activity = @()

$activity = Get-AzSqlElasticPoolActivity -ResourceGroupName "yourgroupnamehere" -servername "yourservernamehere" -elasticpoolname "yourelasticpoolnamehere"

$latest_activity = $activity|sort -Property StartTime -Descending |select -first 1

write-host "$($latest_activity.state) is the state of the operation started at $($latest_activity.starttime) and pct is $($latest_activity.PercentComplete)"

#that is so that when I run it with sql server (VM), I can get back the results of the powershell script. 

if ($latest_activity.state -ne "IN_PROGRESS" -and $latest_activity.StartTime -gt [datetime]"2025/02/04 21:13:00")

{

notificationcodehere

}

```

ODBC OLEDB - returning the version of the actual driver using powershell.

 I have no idea why this is that difficult. The get-odbcdriver cmdlet is overly complicated to read, and doesn't return the actual DRIVER VERSION. The "DriverODBCVer", I believe, is the version of ODBC that's supported.

odbc driver file versions (github.com)

<script src="https://gist.github.com/mbourgon/05c75cda141745e4244bbe876cba5c10.js"></script>

[rant] Freaking Microsoft.Graph cmdlets for 1.27 - I swear, y'all.

 Never, for the love of god, run update-module -all. 

I went from 1.23 to 1.27 and my scripts broke.

First problem: incompatibility with Microsoft.Graph.Authentication. I had multiple copies of it, and uninstall older versions didn't work - had to uninstall ALL of microsoft.graph (not easy, whhhhyyyy), then reinstall WITH "-requiredversion 1.27" (or was it 1.27.00?).  Otherwise it still grabbed the wrong versions somehow.

Second problem: invalid filter clause

get-mgchat : Invalid filter clause

At line:1 char:1

+ get-mgchat -all -PageSize 50 -filter "lastUpdatedDateTime gt '2023-05...

why? Because between 1.23 and 1.27, they stopped accepting the datestring being in quotes. Now, if it is, it doesn't work.  What the everliving...

Patching Visual Studio via Powershell

 

Basic script to update all your Visual Studio instances. On some servers I have/need 3.

The VSSetup is the magic, courtesy of Microsoft (and hence the prereqs), the rest is just running their installer. 

#this didn't find the 2022 install. Run as ISE admin

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -force

set-psrepository psgallery -InstallationPolicy Trusted

Install-Module VSSetup 

get-vssetupinstance|%{

$installpath = $_.installationpath

"$installpath"

Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "update --passive --norestart --installpath ""$installpath"""

}

#this should work, but doesn't. Just kicks back instantly.

#Start-Process -Wait -FilePath "C:\Program Files (x86)\Microsoft Visual Studio\Installer\vs_installer.exe" -ArgumentList "updateall --passive --norestart"

Making about:config a trusted sites

 I need this because IE tries to load about:config twice, which throws errors in the secure mode of IE/Edge, and I needed to script a way around it. 

Thanks to Joe for the tip on quotes.

if (-not (Test-Path -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'))

{

    $null = New-Item -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank'

}

Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\blank' -Name "about" -Value 2 -Type DWord

Azure SQL Database - something like sp_send_dbmail

 I needed the ability to send emails from Azure SQL DB, which doesn't support it. The long-term goal is to use LogicApps or SendGrid or something like that, but while I wait for permissions and the like, let me share this instead. It acts like sp_send_dbmail, and will even run queries in the database (or other databases on that server!) and send them from your on-prem server.

This is based on others. They had the awesome parts, my parts are the crappy glue making it work. : )

The emailqueue table looks like this:

CREATE TABLE [dbo].[EmailQueue](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Recipients] [VARCHAR](250) NOT NULL,
[Cc_recipients] [VARCHAR](250) NULL,
[Email_Subject] [VARCHAR](250) NOT NULL,
[Email_body] [VARCHAR](MAX) NULL,
[Email_body_format] [VARCHAR](10) NULL,
[Query] [NVARCHAR](MAX) NULL,
[profile_name] [VARCHAR](250) NULL,
[QueueTime] [DATETIME2](7) NOT NULL,
[SentTime] [DATETIME2](7) NULL,
[Importance] [VARCHAR](10) NULL,
[attach_query_result_as_file] [BIT] NULL,
[query_attachment_filename] [VARCHAR](200) NULL,
[query_result_header] [BIT] NULL,
[query_result_width] [INT] NULL,
[query_result_separator] [VARCHAR](2) NULL,
[query_result_no_padding] [BIT] NULL,
[bcc_recipients] [VARCHAR](255) NULL,
[execute_query_database] [VARCHAR](255) NULL,
[from_address] [VARCHAR](MAX) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  CONSTRAINT [Co_Importance]  DEFAULT ('Normal') FOR [Importance]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [attach_query_result_as_file]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((1)) FOR [query_result_header]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [query_result_no_padding]
GO

And the SP: 

CREATE PROCEDURE [dbo].[log_dbmail] 

   @profile_name               sysname    = NULL,        

   @recipients                 VARCHAR(MAX)  = NULL, 

   @copy_recipients            VARCHAR(MAX)  = NULL,

   @blind_copy_recipients      VARCHAR(MAX)  = NULL,

   @subject                    NVARCHAR(255) = NULL,

   @body                       NVARCHAR(MAX) = NULL, 

   @body_format                VARCHAR(20)   = NULL, 

   @importance                 VARCHAR(6)    = 'NORMAL',

   @sensitivity                VARCHAR(12)   = 'NORMAL',

   @file_attachments           NVARCHAR(MAX) = NULL,  

   @query                      NVARCHAR(MAX) = NULL,

   @execute_query_database     sysname       = NULL,  

   @attach_query_result_as_file BIT          = 0,

   @query_attachment_filename  NVARCHAR(260) = NULL,  

   @query_result_header        BIT           = 1,

   @query_result_width         INT           = 256,            

   @query_result_separator     VARCHAR(2)       = ' ',

   @exclude_query_output       BIT           = 0,

   @append_query_error         BIT           = 0,

   @query_no_truncate          BIT           = 0,

   @query_result_no_padding    BIT           = 0,

   @mailitem_id               INT            = NULL OUTPUT,

   @from_address               VARCHAR(MAX)  = NULL,

   @reply_to                   VARCHAR(MAX)  = NULL

AS 

INSERT INTO dbo.EmailQueue

(

    Recipients,

    Cc_recipients,

Bcc_recipients,

    Email_Subject,

    Email_body,

    Email_body_format,

Query,

Execute_Query_Database,

attach_query_result_as_file,

query_attachment_filename,

query_result_header,

query_result_width,

query_result_separator,

query_result_no_padding,

    profile_name,

    QueueTime,

    SentTime,

Importance

)

VALUES

(   @recipients,            -- Recipients - varchar(250)

    @copy_recipients,          -- Cc_recipients - varchar(250)

@blind_copy_recipients,

    @subject,            -- Email_Subject - varchar(250)

    @body,          -- Email_body - varchar(max)

    @body_format,          -- Email_body_format - varchar(10)

@query,

@execute_query_database,

@attach_query_result_as_file,

@query_attachment_filename,

@query_result_header,

@query_result_width,

@query_result_separator,

@query_result_no_padding,

@profile_name,          -- profile_name - varchar(250)

    CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME), -- QueueTime - datetime2

    NULL,           -- SentTime - datetime2

@importance

    )

The powershell that pulls it all together: 

Read your Microsoft Teams messages using Microsoft Graph and Powershell

 I suck at time cards. Part of it is that I'm jumping between 30 tasks at once - help this team, help that team, training, documentation, and my sprint work. At the end of the day I have a bare-ass notebook with a few cryptic lines about what I did today - if I'm lucky.  

But a lot of it is available elsewhere. We're all virtual now, so all my interactions are either voice calls, meetings, or chats.  I want to list out what I did in a given day so I can reconstruct it - how much time I spent helping whom.

Enter Microsoft Graph. It's an API that selects from it. There's a set of cmdlets that let you do quite a bit.  I really do wish the get-mgchatmessagedelta properly handled "-filter" to make this faster, but I'll cope.

This will rip through ALL your chats, then for each one get the most recent 200 messages, filter it down to the past day or two, then toss it out on a format-table. 

My pain, your gain.  It's always fun when there's 2 google results for something. 

Install-Module Microsoft.Graph
Import-Module Microsoft.Graph.Teams
$RequiredScopes = @("Chat.ReadBasic", "Chat.ReadWrite")
Connect-MgGraph -Scopes $RequiredScopes
#at this point a browser window should appear - allow it.
#I had to find my user id in graph explorer UI by running GET   V1.0    https://graph.microsoft.com/v1.0/me
#unsure how to get it otherwise - but you don't need it with get-mgchat
get-mgchat
#take one of those IDs
#let's look at this chat:
get-mgchat  -ChatId 19:deadbeefb2d949d88d4455f5279e5d8b@thread.v2
get-mgchatmessage -ChatId 19:deadbeefb2d949d88d4455f5279e5d8b@thread.v2 

#this nests and walks through properly, strips HTML, but lord this will be slow.  And shows more than one line per message, even when I try now to.
#By default you can get all your chats by running get-mgchat.  -all and -pagesize 50 is required for the module to paginate the request and get you everything. But in my case it grabbed all 2000 chats. The -first 5 is for testing.
$tzone = Get-TimeZone  # conversion from GMT to local time. https://jdhitsolutions.com/blog/powershell/7962/convert-to-local-time-with-powershell/
$mychats = get-mgchat -all -PageSize 50  |select -first 5
$all_chat_info = @() #force-setting to an array
$all_chat_info = foreach ($chat in $mychats) { 
    $chatinfo = get-mgchat -ChatId $chat.id #get base details about the CHAT itself
    #set some details about the chat itself for the later query
    $chatname = $chat.Topic
    $members = $chat.Members
    $chattype = $chat.chattype
    #now get every message from that chat since midnight yesterday.  Note LastModifiedDateTime is GMT.    The jdhit page says -($tzone...), but all I had to do was .tolocaltime() ... I think.
    #the -top 200 -pagesize 50 is to get the most recent 200 messages, and again you have to paginate.   
    $recentchatmessages = get-mgchatmessage -ChatId $chat.id -top 200 -pagesize 50 |where {$_.LastModifiedDateTime.tolocaltime() -gt (get-date).date.AddDays(-1)} # all from after midnight yesterday |select -first 5
    #and now use select expression to add the above fields and parse the below fields, stripping out HTML (but I can't seem to only get the first line in OGV)
    $recentchatmessages | select @{Label='LastModified';Expression={($_.LastModifiedDateTime.tolocaltime())}}, @{Label='ChatName';Expression={($chatname)}}, @{Label='members';Expression={($members)}}, @{Label='ChatType';Expression={($chattype)}}, 
    @{Label='From';Expression={($_.from.user.displayname)}}, @{Label='Body';Expression={ ($_.Body.content -split '\n')[0] -replace '<[^>]+>',''}}
    #@{Label='From';Expression={($_.from.user.displayname)}}, @{Label='Body';Expression={( ($_.Body.content -replace '<[^>]+>','').split([Environment]::NewLine)|select -first 1)}}
}
$all_chat_info|format-table 
#and now close/disconnect
Disconnect-MgGraph

Powershell - using ODBC and the Simba Driver to query AWS Athena

 I was running into issues with the Linked Server lopping off long JSON that I'm having to pull out from the raw files.  I can't explain it - doesn't appear to be SSMS.  See previous post

But I needed to automate this, rather than use SQL Workbench, save to "Excel" (it was XML), then opening it again and saving it so that instead of 250mb, it's 30mb.  Runs against the previous month, one day at a time (walking the partitions), and then saves to a file.  You got your Athena, your ODBC, your Export-Excel...

#This has two requirements:

# 1 - simba installed as an ODBC driver, called "your_simba_athena_dsn_name_here".  Free from Amazon for this use.

# 2 - ImportExcel.  Requires Powershell V4 (V5?), but for sure v3 won't work.

#Get-ODBC-Data2 is just a slight modification of Anders' code that includes the UID/PWD. Thanks!

#https://www.andersrodland.com/working-with-odbc-connections-in-powershell/

#modified because we have to pass in user/password to Athena.

function Get-ODBC-Data2{

    param(

    [string]$dsn,

    [string]$param,

    [string]$query=$(throw 'query is required.')

    )

    $conn = New-Object System.Data.Odbc.OdbcConnection

    $conn.ConnectionString = "DSN=$dsn;$param"

    $query

    $conn.open()

    $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)

    $ds = New-Object system.Data.DataSet

    (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null

    $conn.close()

    $ds.Tables[0]

 }

 #Set start and end.  Those can be whatever, but I made it one month for simplicity

 [datetime]$start_date_yyyymmdd = (get-date).addmonths(-1).tostring("yyyy-MM-01")

 [datetime]$end_date_yyyymmdd = $start_date_yyyymmdd.addmonths(1).tostring("yyyy-MM-01")

 #[datetime]$end_date_yyyymmdd = (get-date).addmonths(0).tostring("yyyy-MM-01")

 $filename = "Myfile_" + $start_date_yyyymmdd.tostring("yyyyMM")

 #set a variable so that as we walk through, we have an array to save everything to.

 $formattedresults=@()

 

 #loop through the days

 while ($start_date_yyyymmdd -lt $end_date_yyyymmdd) {

 $startdate = $start_date_yyyymmdd.tostring('yyyy-MM-dd')

 "starting $startdate"

 $query = @"

 select json_extract_scalar(myfield, '$.fieldjsonparsed') as fielda, dt,

 fieldb, fieldc

  from myschema.mytablename

  where dt='$startdate'

"@

 

 $results = get-odbc-data2 -query $query -dsn "your_simba_athena_dsn_name_here" -param "uid=youraccesskeyhere;PWD=yoursecretkeyhere" 

 #save non-systemdatarow version to our main array

 $formattedresults +=$results|select-object fielda,fieldb,fieldc 

  $start_date_yyyymmdd = $start_date_yyyymmdd.adddays(1)

 }

 

 #export using ImportExcel module, which unfortunately needs at least higher than V3

 $formattedresults| export-excel -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path c:\temp\$filename.xlsx

[Powershell] Getting the full text out of a field in SQL when SSMS won't work

We have some code that is basically table-driven SSIS. The advantage is: no digging through SSIS! The disadvantage is that if someone decides to put in code instead of a stored procedure, it's hard to get it out of SSMS.

I wish I knew a better way to handle this, but this works fine.

#This uses the wondrous invoke-sqlcmd
. c:\powershell_scripts\invoke-sqlcmd2.ps1

$query = @"
SELECT source_sql FROM etl_me.dbo.data_flow_me_config where id = 1
"@

$fullquery = invoke-sqlcmd2 -ServerInstance mem-pr-cde-01 -Query $query


First, to get the data out, I tried:
$fullquery.source_sql

Then I realized I could do this, if I only had one column:
$fullquery[0]

But that can fall down.  This one is slightly better.  Add more columns if you need them - if they're nonexistent, it still works.

$fullquery|%{$_[0] #, $_[1], $_[2]
"-------------------"}

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.

[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?)

[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.