Azure - CETAS with OPENQUERY works!

 Just putting it here since I'm thrilled this weird way of doing it worked. 

I was trying to use a linked server from an MI to an Azure SQL DB, so that I could CETAS (Create External Table As Select) from my Azure SQL DB to blob storage (since the only way to do it from ASD is either ADF or some sort of preview thing). It didn't work. Closest I got was XML errors on distributed queries. Then I went to OPENQUERY Woot! And I even got the xml field copied over!

CREATE EXTERNAL TABLE mytablename

WITH (

    LOCATION = 'capture/20250226/01',

    DATA_SOURCE = [my_blob_data_source],

    FILE_FORMAT = CETAS_ParquetSN_FileFormat)

AS 

SELECT fielda, fieldb, fieldc FROM OPENQUERY(my_ro_linked_server,'SELECT fielda, fieldb,  convert(nvarchar(max),fieldc) as fieldc FROM capture.dbo.mytable')

WAT - @@Rowcount, SELECT, DISTINCT, CTEs, and unexpected results.

 I was looking at a piece of code that was slow. The slow part was due to a "where (a.fielda = @param or a.fieldb = @param)". So I took it from 

declare @field varchar(10) = (select distinct (field) from myview a where (a.fielda = @param or a.fieldb = @param) and a.fieldc = @param2)

if @@rowcount > 0

BEGIN

blahblahblah

END

to

declare @field varchar(10);

with cte_distincter as 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

)select distinct @field = fielda from cte_distincter

if @@rowcount >0

BEGIN

blahblahblah

END

Pretty good, right? 150-250x faster. Problem solved.

Except.. now the @@rowcount code doesn't fire right. Because the first code, event if it returns an empty set, has a @@rowcount of 1. The CTE code will have the @@rowcount = 0 if it's NULL. So, had to drop the CTE, and just went to using a derived table. Which works. But jeeze, talk about unexpected behavior. CTEs aren't always the solution!

declare @field varchar(10) = (select DISTINCT(fielda) from 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

) a)

Resizing operations - getting notifications on Managed Instance & regular SQL DB

How to monitor the progress of a resizing operation. (see previous post for Elastic Pool changes)

Managed Instance:

•  Get-AzSqlInstanceOperation -ResourceGroupName "myrgnamehere" -managedinstancename "myservernamehere"

Azure SQL Database, not elastic pool. 

•  get-azsqldatabaseactivity -ResourceGroupName "myrgnamehere" -servername "myservernamehere" -databasename "dbnamehere"

Note that instead of IN_PROGRESS, for both of these the state is "InProgress". Sloppy, Microsoft, sloppy.

Oh, but at least they start reporting info shortly after it starts!

$activity = (the above)

$latest_activity = $activity|sort -Property StartTime -Descending |select -first 1 #unsure if I need this, but what the heck, still works

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

{

#notification here

}

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

}

```