Azure MI - Failed email -2147467259

A job step on an Azure Managed Instance that calls msdb.dbo.sp_send_dbmail (or possibly calling a linked server) can fail with error -2147467259.

The fix is to put "EXECUTE AS LOGIN = 'SQLAGENTUSER' " at the front of the code inside the job step. What's happening is that the Login that's used by the agent (ContainerAdministrator) is lacking perms (MSDB for email, but also linked server).

Applying Query Store hints to fix cardinality estimation issues where you can't change the query.

This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today.  It was SLOW. Like, 10 minutes slow. With the added bonus that it's done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn't call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick - query store hints (which used to be Plan Guides)

In theory you can probably get enough info from the query store/plan cache, so you can find the exact query  

What I did: 

• ran a profiler trace for that user/database. You could do this in Azure Data Studio for Azure SQL Database - I got "lazy", and since it was on a Managed Instance, used Profiler. Don't judge me. :)
• found the exact statement that their code ran. Easiest way to get this (it needs to be EXACT down to the white space) is to right-click on the cell in profiler, extract events, save to a file, then copy/paste out.
• found the AUDIT LOGIN from when it ran
• launched SSMS
• in one window, posted all of the SET QUOTED_IDENTIFIER (etc) statements from the AUDIT LOGIN, newline, "go", newline, then copy/paste from that second step. NO trailing anything. Ran it. Waited the 10 minutes for it to finish. You need to do it this way because the application default settings are not the same as the SSMS defaults (for more info, read Erland's seminal work "Slow in the Application, fast in SSMS?" https://www.sommarskog.se/query-plan-mysteries.html#defaultsettings), specifically 2.5.
• Expand that database in SSMS.
• Query Store->Top Resource Consuming Queries
• Mouse over and find the query in the left-side. Get the query_id (not the plan id). For me, it was 9605.
• run this in that database: EXEC sys.sp_query_store_set_hints @query_id= 9605, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
• have people test it and make sure it works.
• later, go into Query Store->Tracked Queries, put in the number above (for me, 9605), and see if it's getting called, and how long it takes.  Oddly, it doesn't show up in "Queries with forced plans".  : ( Unsure how to find queries that we've tuned this way, maybe Managing Query Store Hints - Simple Talk (red-gate.com)  ?).

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>

Azure SQL Database - ports you didn't realize you needed for your firewall request.

 Azure is fun. FUN, I say!

Ports: 

1433 - standard SQL port

11000-11999 - ports if you're using Proxy instead of Redirect

14000-14999 (!) - ports for the DAC (Dedicated Admin Connection)

Mermaid (the language) is life!

Just found out about this the past month. 

I like diagrams for my documentation, and I detest making it. I also would like to build it via script, since that's more useful.

Sample:

graph TD;

     A-->B;

     A-->C;

     B-->D;

     C-->D;

Which produces: 

Pretty cool right? 

VSCode supports it (load the extension "Markdown Preview Enhanced"), but Jira and Confluence don't support it natively.... 

But there's a workaround!

In Chrome, load the extension Mermaid Previewer (https://chrome.google.com/webstore/detail/mermaid-previewer/oidjnlhbegipkcklbdfnbkikplpghfdl)

There are a couple of tweaks. For one, you need to enable sandbox mode in the extension. 

For Confluence, I put the site as my\.confluence\.site and a selector of pre > code. To add it, I create a markdown block, but within it do this:

```mermaid

graph LR

A --> B

```

(update 2023/10/27) another way that seems to work for confluence is to make a regular code block ( for me, type ``` by hand, then do it with just the "graph LR / A --> B"), and in Mermaid Previewer as div.container, but I don't know how that affects other "containers". the nice thing about doing it the other way is that (for some reason) you HAVE to have the ```mermaid, which means it can't accidentally go crazy with it.

For Jira, my selector is pre.code-java, since by default our code blocks are Java. You can look at the element in Chrome developer mode (right-click, choose "inspect").

Then, within the Jira ticket, just do:

graph LR

A --> B 

(NOTE THE SPACES!) No idea why it doesn't act the same between them, but for me it's fine.

SSIS and Parameters and where to set them

 SSIS has a LOT of ways to set configs. Parameters & variables, and then those can be overridden at different levels.

in order (from lowest to highest) - bottom of this list supercedes the top. You can see WHERE it was overridden because at that level, the parameter name is in bold. 

1. SSIS Package level
2. SSIS Project level
3. XML Configs? (I don't remember where these can be set)
4. SSISDB - right-click on the project within integration catalog and choose "Config"
5. SSIS Job Step configuration

Azure Data Factory - how to ACTUALLY use storage event triggers to copy data from Blob Storage to Azure SQL Database (Azure SQL DB)

 This post is in response to an incredibly frustrating interaction with Azure.

I figured I would do something incredibly simple - import a parquet file from blob storage into Azure SQL DB. When the file is written to Blob Storage (using CETAS, fwiw), it should activate the storage trigger, which should kick off the pipeline. 

The problem is that it won't work as the tutorial (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal) shows. It says it can't find the file. Why not? Well, it wasn't firewalls or anything else. It's that a linked service dataset, in "Sink" requires a container name - and the trigger sends the container name also. So I kept getting messages that it couldn't find \mycontainer\mycontainer\myfolder\myfile.parquet . I found a single message on Stack Overflow (https://stackoverflow.com/questions/67294521/azure-data-factory-how-to-get-new-file-location-from-triggerbody-folderpath/74738786) on how to solve it. So, here we go: 

package parameters:

Source:

@pipeline().parameters.trigger_folder_path

@pipeline().parameters.trigger_file_name

My Source Dataset:

Sink:

And the last part in the trigger that makes it all work:

Here's what to put for trigger_folder_path, as per Steve Johnson on SO. No, you don't have to change the YAML, you can modify the values above: 

@substring(triggerBody().folderPath,add(indexof(triggerBody().folderPath,'/'),1),sub(length(triggerBody().folderPath),add(indexof(triggerBody().folderPath,'/'),1)))

Why it's that hard to find/use, I have no idea. 

All Hail Steve Johnson!

[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