The Heap™ – Consultancy ©®

12:09 AM

@J.D. you may want to use ORIGINAL_LOGIN instead, to ensure you don't get caught by impersonation.

> Returns the name of the login that connected to the instance of SQL Server. You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches.

J.D.

2:28 AM

@HannahVernon Good point! Thank you!

Zikato

7:49 AM

Morning

John K. N.

9:28 AM

Morning

mailchi.mp/brentozar/…

Paul White

> Death toll from earthquake in Türkiye rises to 7108

John K. N.

11:53 AM

here we go again

Paul White

what

Sean Gallardy

12:21 PM

@kesadae11 If you want to answer the question, write an answer. We don't accept comment-answers here. — Paul White ♦ 47 mins ago

John K. N.

yes.

And previous question from OP

Am I exaggerating if I say, I don't think they have learnt much since they started posting? Quality-wise and so on....

Sean Gallardy

12:39 PM

yeah it's getting harder and harder for me to submit answers for their questions

Paul White

12:56 PM

it's not compulsory

but always nice when you do

Erik Darling

1:25 PM

“With an AI copilot, you'll be more productive and discover better information. The age of AI is here, and @Microsoft is powering it.”

J.D.

1:38 PM

@SeanGallardy AGs have been popular lately eh? lol

@ErikDarling Maybe it can help me find that old Feedback link that conveniently disappears whenever Microsoft changes sites again.

Sean Gallardy

2:03 PM

@J.D. Just one person asking the same questions over and over and never learning

Hannah Vernon

2:18 PM

@J.D. take a look at this example showing weird login names in the sys.dm_exec_sessions dmv: sqlserverscience.com/tools/…

J.D.

@SeanGallardy Fair. Though I think I saw a few one-offs from other new accounts recently too. But yea I know what ya mean about that user.

Paul White

Tolerance, empathy, and self-awareness exceptionally high in the room today I see

J.D.

@HannahVernon interesting thanks!

Sean Gallardy

If I'm known for anything, it's for tolerance and empathy

3

J.D.

lol

Paul White

2:23 PM

@SeanGallardy right

if anything

😀

J.D.

@HannahVernon I was just especially surprised yesterday that I haven't seen anyone mention the solution I came up with before. I can't be the first to thought to do that.

But I've been doing a lot of on and off research on all of the change tracking and auditing features for the last few months, and I have yet to see a good or even realistically viable way to associate the User who made the change to the actual change.

Sean Gallardy

:P

Paul White

It's a heavily-requested feature. Who did it and when as GENERATED ALWAYS columns.

J.D.

I'd imagine so. And I can't imagine it being that hard to implement.

Curious if you had any critiques on my current possible solution?

Paul White

Most little things aren't hard to implement. They're hard to test with every other feature in the engine(s)

Where hard = expensive, and Microsoft isn't made of money you know

2

@J.D. I didn't look at it closely, but I imagine it's what people usually do when they find there isn't a built-in capability.

I'm sure there are some quirks and gotchas, but temporal isn't my thing

So I don't pay it much attention

J.D.

2:32 PM

No doubt.

Paul White

Before it came along, people wrote triggers. Often badly.

Zikato

I had a hybrid approach before
https://straightforwardsql.com/posts/how-to-audit-data-modifications-with-surgical-precision/

Paul White

Yeah, cool

J.D.

@Zikato Nice, I'll check this out. Thanks!

Paul White

Extended events, MERGE, and PowerShell all in one convenient place

🤮

J.D.

2:40 PM

@PaulWhite True. It also just sounds like the kind of change small / specific enough that the number of things they'd realistically need to test would be quite a subset of all things in SQL Server.

Paul White

If only things were so simple

J.D.

True true.

Paul White

All the people that worked on the feature have probably been fired anyway

Microsoft are quite Ooo! Look! Squirrel! these days

Sean Gallardy

But Paul, Mongo is doing it... Postgres is doing it... Oracle is...dead?

Gotta keep up with couchbase

Also, ask yourself this, can we make this a cloud only feature?

Sounds like a nice way to lock you in!

Paul White

Put a business case together explaining how it will help sell more Azure Arc Hyperscale Synapse Cosmos

3

Sean Gallardy

2:47 PM

☝️👆☝️👆

Paul White

And bear in mind they can't even keep email running reliably when you set your expectations

Sean Gallardy

I lost it when I saw a "serverless auto-pause instance" supposedly "taking too long to unpause"

Paul White

🤷‍♂️

Sean Gallardy

Many of these really grind my gears

"S0 instance not updating 2 million rows quickly"

Yes, by design.

Here's your 1/4 of a fake core.

Paul White

ha ha

How about, "Hi! We're deprecating some cloud hardware, moving you on to the closest new equivalent oh and btw your bill might increase as a side-effect."

J.D.

2:50 PM

@Zikato I originally was thinking of going the route of Audit or XE but was immediately turned off when I learned there was no automatic way to store the results to a table. I plan to have pretty old historical data I'm logging and don't trust the reliability of the log files. Also just seems silly in general that XE still uses log files. I was very close to saying screw it and rolling my own triggers yesterday until I thought to try the computed column idea.

Leveraging Query Store is an interesting idea.

Sean Gallardy

@PaulWhite Watch the cancellations pour in

Paul White

It's easy to underestimate people's desire to remain on old, knackered hardware and software for decades

Or aversion to spending money, more realistically

I saw a new 2008 question pass by on main and giggled a little

There was a 2000 one not so long ago

Probably running on a Pentium II server

Anyway

J.D.

@PaulWhite so when I worked for a military & aerospace engineering company, we'd buy electronic components from a very large international company that ran the market share of that industry. They were still using an MS-DOS based inventory system.

Zikato

Probably keeping some consultants in business. How nice of them

J.D.

This was 4 years ago.

Sean Gallardy

2:58 PM

@J.D. Beat me to it. I've been at multiple gov't sites and everything is crazy old (20+ years).

Paul White

Is there a national tech debt ceiling, I wonder

That said, a few military things have good reason to use very old tech

J.D.

Yea it's crazy man. You usually hear that kind of thing for governments and some schpiel on reliability. I literally could not believe my eyes that the world's largest and international electronic component dealer, whose job is centralized around inventory, used a command line based inventory system internally.

In 2018.

The only reason I knew, it's kinda interesting, is they're so big they offer an option to literally store a mini-inventory warehouse at your manufacturing location, fully stocked, with one of their own employees 5 days a week, so you can have realtime delivery on components. I imagine they only do this for larger customers. But I'd end up troubleshooting our inventory software we made their employee use when they'd transfer inventory to us.

Paul White

As a rule of thumb, the more vital the application (medical, aviation, banking etc) the shonkier the tech

J.D.

And I got a peek at their inventory system during the process.

@PaulWhite p crazy times.

John K. N.

death toll currently at 11'0000

Lamak

3:13 PM

did anyone watch the google AI event today?

No

Nope

Was Fred introduced?

felt really underwhelming

Sean Gallardy

like most "AI"

Also like most "Cloud", "ML", "DevOps" items as well

Lamak

3:15 PM

yeah...but the MSFT event yesterday was far better

Sean Gallardy

Gotta learn to lower your standards

Lamak

@SeanGallardy I know, I've been on learn.microsoft.com

Sean Gallardy

:D

I'm excited for the next single name word it'll be called

We taking a deadpool on when the name will switch?

Erik Darling

3:31 PM

hello and good evening

Sean Gallardy

🙋‍♂️

Josh Darnell

Oh hi!

17 hours ago, by J.D.

So I've been looking for a good way to automatically correlate the User who made the change to the history in a Temporal Table's history table. I found adding a computed column to the source table using the SUSER_NAME() function does persist the correct User to the history table of whoever executed the DML statement.

@J.D. The only comment I have on this idea is that it's only as useful as the users / logins being used to modify the database.

If everything connects as sa, then...

Erik Darling

i do all my modifications with dbcc writepage to skirt the auditors

Josh Darnell

3:50 PM

Typical consultant.

Cade Roux

4:06 PM

It's not good for real temporal tables, but I use AutoAudit for a few tables in some of my DBs where I just want a good history that's easy to review: github.com/koenmd/AutoAudit - that was copied from codeplex before it was shut down. It uses DDL triggers to update itself if a table is changed. The code isn't huge and complex, and it works reliably for that simple use case.

I do not have it in place for actual transactional tables or anything like a real temporal table like slowly changing dimension.

It's not maintained or anything, but my local copy hasn't even needed any changes or customization in years.

Erik Darling

sounds deprecated

ypercubeᵀᴹ

@SeanGallardy I say 2023. we can bet on the month ;)

Sean Gallardy

@ypercubeᵀᴹ That'd be soon, even by MS's standards

Erik Darling

4:22 PM

quite an agile company these days

Sean Gallardy

"agile azure cloud for business 365" please

Erik Darling

agops

Sean Gallardy

@Lamak lol internal chat this morning, just found this, "I am seeing the [bing] integration this morning. It is underwhelming."

Erik Darling

bingtegration please and thanks

Sean Gallardy

of course, my apologies

I'll add it to my agops dashboard items

Erik Darling

4:29 PM

scrum it baby

0

Q: Varbinary pattern search

So I'm trying to make a query which goes through varbinary data. The issue is that I can't really finish what I'm trying to achieve. What you should know about the column is varbinary(50) and the patterns that occur have no specific order in writing, meaning every prefix could be anywhere as long...

oh boy

Sean Gallardy

5:30 PM

Would a mod clean up the comments on this?dba.stackexchange.com/questions/323004/…

Erik Darling

you're lucky that didn't get turned into a chat

Sean Gallardy

5:45 PM

I mean I guess it could be

because I'm going to post more comments

Erik Darling

D:

paul 💔 sean

Sean Gallardy

yeah then let's do a chat I guess

I don't know how it all works

Erik Darling

i bet @HannahVernon could make that happen

Sean Gallardy

6:40 PM

on a call, legit just heard, "Postgres already does this"

Feels like Nokia all over again

Erik Darling

i keep telling microsoft to buy postgres

instead they buy dumb shit like "activision"

Sean Gallardy

Gotta spend 69 billion dollars somehow

Erik Darling

i could fix sql server with that budget

Sean Gallardy

but could you fix the cloud with that budget?!

A little bit of Paul in me, I guess...

You have, in your post, that you restarted SQL - it was down for a period of time. Your questions turn into "and then" comment answers. If you have other questions, post them, please do not "and then" answered questions. — Sean Gallardy 44 secs ago

Hannah Vernon

@ErikDarling your wish is my command

Erik Darling

6:54 PM

🧞‍♂️

2 days ago, by Erik Darling

sometimes i avoid answering questions because i know op will ask a bunch of annoying question comments

Sean Gallardy

I guess I have a little bit of Erik too

Hannah Vernon

Especially if its that one special user

swasheck

weird question ... are constraints checked on delete?

>CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.

nvm

ypercubeᵀᴹ

@swasheck only FKs I think, when deleting rows of the referenced table

Zikato

8:20 PM

@Dan You seem to have accidentally typed your answer into the wrong box. Pls fix. — Paul White ♦ 5 hours ago

4

lol

J.D.

@JoshDarnell oh yea, absolutely. And equally true for any other solution. But yes, good point. Fortunately at my current gig I had a say to ensure all of our connections to the database are as the actual executing user. We only have a handful of exception cases that require a dedicated elevated account, for automated services.

Erik Darling

i got some orm fanboy to block me on twitter today

felt pretty good

Zikato

Keep up the good fight

J.D.

Btw I'm not cool enough to run a blog, so if anyone wants to, feel free to use my idea.

Erik Darling

your idea for what

J.D.

8:39 PM

On correlating the executing User / Login's changes to a Temporal Table, to the changes themselves as they get logged in the history table.

Erik Darling

eh then people might bother me about temporal table stuff

J.D.

Heh, I already did once.

Erik Darling

exactly

if that happens again i'm gonna quit

J.D.

Yes but at least you got to send an actual invoice that time 😁

Erik Darling

if you're not careful you might get another one

J.D.

8:46 PM

Fair enough.

ypercubeᵀᴹ

1

Q: Deletions getting omitted, data supposedly deleted returned in CQL queries

We are facing an issue on one of our production systems where after we delete the data the data doesn't seem to get deleted. We have a Get call just after the delete call. The data shows up. Versions cassandra : 3.11.6 gocqlx : v2 v2.1.0 Client Settings: LocalQuorum Number of Nodes : 3 All 3 no...

delete cassandra

welcome to nosql world, where delete doesn't always mean delete

Erik Darling

it's almost like you paid for eventual consistency and you got eventual consistency and now you're wondering why things are inconsistent

J.D.

I was gonna say, is it an eventual consistency issue? Because that'd be pretty funny. But I see them talking about 10 day grace periods, which sounds absurd.

Erik Darling

well there are answers that i can't be bothered to read

swasheck

9:45 PM

do we still have purity filters in here?

Erik Darling

yeah we kick virgins out

swasheck

man. i'd just written a poem to ORMs too

Erik Darling

orms are for virgins

once you know physical love you only write sql