@HannahVernon I prefaced the question by calling it "sort-of-a-queue table" because it's not 100% like a queue. The goal is to try to keep the workflow self-contained, by design, so if process A puts a SerialId in the table, I only want process A to be allowed to manage that row. It's really just a locking table for SerialIds in a sense.

interesting

@PaulWhite Thanks! Yes, I saw that and a few other articles from them. Was curious the collective thoughts on their input. Good to see it's well received. Coincidentally, I am using the OUTPUT clause when both adding and removing, but not exactly for the same goals as a true queue.

sounds like the kind of thing that might work with sp_getapplock, avoiding a table completely

@HannahVernon I've seen that before but forget how it works exactly. I'll look into again, thanks!

Also looks like nbk strikes back again lol.

this is the general way app locks work (note I'm not specifying any existing object in the database:

    DECLARE @return_value int;
    EXEC @return_value = sys.sp_getapplock @Resource = N'SerialId:5104', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 100;
    IF @return_value > -1
    BEGIN
        PRINT N'Lock obtained';
    END;
    GO

    /*  do some work on SerialId 5104 here  */

    DECLARE @return_value int;
    EXEC @return_value = sys.sp_releaseapplock @Resource = N'SerialId:5104', @LockOwner = 'Session';
    IF @return_value = 0
    BEGIN
        PRINT N'Lock Released';

If some other session tries to get an applock on @Resource = N'SerialId:5104' after we get it in our session, and before we release it, they would not get the lock assigned to them.

i.e. the @ReturnValue would be -1 or lower.

Interesting. So I could just take out a bunch of individual locks, one for each SerialId being processed?

This does use SQL Server's internal locking mechanism which is pretty bulletproof. But obviously you wouldn't want to completely overwhelm it with a huge number of outstanding locks.

@J.D. yeah exactly

Gotcha. Yea that's cool. Realistically there'd probably be like 10 at most at a time. Though there are rare cases where it could be to 1,000.

I guess the nice thing about a table though is one can store additional meta-data in it, and it's more natural for querying, for debugging etc.

But will definitely take a look into anyway.

Paul wrote about it here - I didn't read the entire question just now, but on the surface it sounds applicable.

👍

I just read the question, and while tangentially similar, it uses a table and the sp_getapplock mechanisms.

interestingly, he quoted that same article from Remus' blog in his answer

A nice bonus with that is if the session dies for whatever reason, the applock is automatically released

so, if your client crashes while it has an app lock, the app lock is released automatically.

@J.D. that's such a useful comment, too. "please read all about locking".

you'd think they'd just answer if they know something is up

@HannahVernon lol yea. More of like a flex on "here's some things I know about that are tangentially related", without the risk of being downvoted.

right

@HannahVernon true, I'd have to think about it, but I could see how that would be helpful most times to have inherently.

Ultimately the goal is the process syncs data from one of our databases to a 3rd party system via their API. Their API is slow as a sloth for a single call, but parallelizing the calls seems to take just as long to get more data synchronized. So just trying to ensure multiple runs of the process concurrently don't double-down on trying to sync the same object of data twice.

I would guess if our process crashed and left the SerialId locked, that would be ideal, so we could investigate where in the process it crashed (e.g. in case some of the related data to that Serial was synchronized already), before just unlocking it and allowing it to be re-processed.

Ad lib: 3rd party vendors suck, and cloud sucks lol.

Of course none of this is transactional via their API either lol.

@HannahVernon no it isn't

you need a catch block

"new eclectic rock music" is perhaps the least enticing arrangement of words i've seen in my life

Ouch.

on the plus side, you'll be the star of my youtube video tomorrow

gist.github.com/erikdarlingdata/…

oh i need to go to bed

that was fast

A chairde - Morning all!

@ErikDarling What?

> Locks placed on a resource are associated with either the current transaction or the current session. Locks associated with the current transaction are released when the transaction commits or rolls back. Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.

docs.microsoft.com/en-us/sql/relational-databases/…

That said, it's much more common to use a transaction-scoped app lock than a session-scoped one @HannahVernon

With a session-scoped lock, you're at the mercy of SQL Server noticing the session has ended

CATCH blocks are nice but you can't always rely on them being hit, see Erland's tome

@PaulWhite see the gist I posted for an example

@ErikDarling I read that of course. You're throwing an error, not killing the session

That's the message you originally replied to

Just wondering - since Monica-gate, has there been any fall-off in usage here on dba.se and/or stackoverflow (or even the whole stackexchange network)? I'm also wondering about, say, average rep of responders? Has it gone done significantly since Monica-gate? I know that, for example, @ypercube seems to have completely abandoned ship... which is a shame because the quality of his contributions was high - just wondering if this phenomenon has spread more widely?

@ErikDarling Are you up early or late?

@PaulWhite yes, I figured a session dying included throwing an error 🤷‍♂️ [alt text: an unnaturally yellow man in a blue shirt shrugging]

@ErikDarling Most errors don't kill the session though. Certainly not a division by zero.

@Vérace I woke up early. I’m still in bed. May go back to sleep.

I would expect an error that terminates the connection to also free a session-scoped app lock

Like one of the really high severity errors or something else

@ErikDarling Anything with severity >= 20, I think

But really, I was thinking more of normal session ending - e.g. the client disconnecting

I was only trying to protect our wayward Jacuzzi Dolphin from an unfortunate situation

Indeed. And you're both right, just about slightly different things

It’s pornographic how right we are about slightly different things

Errors Unaffected by a TRY...CATCH Construct

For the benefit of the transcript

I fear there's no way to fully protect our endangered dolphin

@PaulWhite Thanks for your answer! It sounds like I'm in good shape (as far as what the database side is actually doing). 😁

Yes. Usually, when the database is used for such things it is given heaps of extra info like the unique ID of the app owning the row, the SPID that created it, and so on

Even the only potential race condition you mentioned with transactions is ok per our business rules, because the same SerialId being processed twice by two different instances of the application process is allowed, as long as the processing isn't actually concurrent.

@PaulWhite Yes, good points too!

Sounds like madness, but sure

I mean, many of the potential downsides are only not downsides because of 'business rules' or other external restrictions. If any of those were to change...

I don't think this quasi-queueing / locking system is actually protecting us from much, but management wants it

Right

Just make sure you choose secure passwords

Don't worry, we store them in plaintext in our database where they're nice and safe and backed up. 😉

Oh that's alright then

Jk, I don't work at the company that does that, anymore.

I doubt it's as uncommon as people might like to think

Yea, agreed.

i worked at one place that had an in-house app that did that

i was mostly surprised by how raunchy people's passwords were

made you look at people quite differently

"If you don't understand the APPLY operator, your skills are somewhere around the 50th percentile at best."

that adam quote still gives me a chuckle

@PaulWhite I once worked at a really small marketing company that's no longer in business. For some reason, they had a one-off process with one of their customers where that customer would call and place orders via credit card over the phone, which would then be entered in plain text in an MS Access database on a file share on the company network.

@ErikDarling Almost all developers in shambles.

😑

web.archive.org/web/20191126122327/https://twitter.com/…

there we go

@PaulWhite this

@PaulWhite and rotate them every 6 minutes

with a port scan every five

As recommended by Mean Foolhardy of MSFT

a man of your age requires port scans yearly

who wants to live forever

i'd give it a shot for a bit

Next up, 4 digit wordles

frogless

Wouldn't it be great if they used some super obscure word like crwth tomorrow and the entire wordle world revolted

I fully expect it to crash or restart at 000

Today was CMXCIX for those using Roman numerals

Well, yesterday for me

There's a special animation for the 1000th Wordle for those using the NYT app

So, I guess it doesn't crash

Nice, future man

Little party emoji too

And a comma

SSMS can't do that

SELECT
    Wordle =
        FORMAT
        (
            1000,
            'N0'
        ) +
        N' ' +
        N'🎉';

Sure, SSMS can print a string with a comma in it

You know what I mean

I wonder if T-SQL will get the underscore as a separator thing

1_000_000 etc.

that'd be awesome

i'd love random formatting added to things when pasted

or does that require spaces too

i suppose it depends on the destination

I suppose interns are hard at work on it even as we speak

wonder what's in their github repo

it's a bit annoying that the sp_getapplock docs don't really go over when you'd want to use session vs transaction. some may walk away from descriptions of their behavior a bit puzzled.

some

if you don't understand sys.sp_getapplock your skills are somewhere around the first percentile at best.

probably people in the 50th percentile at best

dammit

jinx

Mean Foolhardy would like his groups of three digits separated by tabs

@J.D. I look at the difference between using a real table and sp_getapplock as whether you want the lock to persist through a connection drop, SQL Server restart or app crash. In most cases yes, but if not then sp_getapplock can make sense. Make sure to use SET XACT_ABORT ON ensuring you get a clean rollback.

1 000 000

Renders no differently, humph

Chat has no tab stops, it seems

Decimals were a mistake

Ancient Babylonians used base 60, much more hairy

MM in Roman numerals

@Charlieface Handy if you need to divide things up though, I'm told

@PaulWhite how do you pronounce that

"yum"

@ErikDarling duo milia

according to google

> Established in 1944, Kumeu River Village is a benchmark estate in New Zealand. Their balanced and fresh Pinot Noir has all the NZ Pinot notes of raspberry, cherry, and plums on its round palate, along with subtle acidity and a hit of chalky minerality that keeps it dry and refreshing

@PaulWhite can you confirm or deny this description

@Charlieface i was hoping it'd be m&m

Google translate has "one million" = "deciens" in Latin

latin is way better than roman

@ErikDarling It's all marketing speak

Poetic nonsense for the most part

The real question is how would Animal from The Muppets pronounce it

paul looks to muppets for correctness

that's gonna be in my head for a month

thanks

mahna mahna

doo doo doo doo doo

that'd make a good halloween costume for a thruple

yes

are muppets furries

these are the Important Questions

probably needs a Venn diagram

why are venn diagrams always caught in the middle of these controversies

@Charlieface Noted, cheers! I'm leaning towards persisting the lock through unexpected crashes / drops. I think due to the non-transactional nature of the overall process and the limited control we have with it being a 3rd party system we're synchronizing data to, it's probably better for those error cases to be manually looked into before decided on what should happen in regards to (un)locking with that particular SerialId.

@PaulWhite But MM is 2k not 1m

en.wikifur.com/wiki/Muppets

what sick person starred that

damning peter to eternal star board shame

@ErikDarling Muricans only one thing on the brain

also i hope that was an incognito window

> Many furries attribute this claim to be Kermit being Henson’s fursona.

!

childhood's end

the-more-you-know.gif

So where does that leave Fraggle Rock?

best not to speculate

a picture of MVPs from the nordic territories was posted on twitter. does anyone want to guess what one of the first responses was?

bork bork?

i might go say that

Funny how mentioning furries made Peter show up

@Charlieface There wasn't a convenient way to render M with an overbar

@HannahVernon yea, usually you have to light the FurSignal on top of city hall

@PaulWhite you couldv'e just used an errant apostrophe, we'd all have known what you meant

One tries hard not to abuse the apostrophe

@PaulWhite ̅ M̅ M and thats 2m not 1m.

hm

better call sol

@Charlieface I thought the overbar multiplied the letter underneath by a thousand?

Why am I debating Roman numerals

So did I, so M is 1000 multiplied by 1000, add the same again you get 2m?

I don't know why either....

I meant a single M with an overbar

I only typed MM because that was one of the first search results

And I really didn't care to read the whole link

I'm not at all convinced the Romans used big numbers very much

Simpler times

I'm not reading all this chat

What happened to the good days when there was only half a screen and I didn't need to scroll

Ah, Gall Salady arrives

How'd you know my porn name?

How do you think

You're my 1 subscriber! I appreciate you <3

Forgotten me already, it seemed

Only Fan

The joke has come full circle, love it when that happens

this one client of mine is always either terribly late, or no-shows calls

i don't bother joining until i get an email saying they're in the call

"Here is your 1 hour invoice to a call you didn't show, sorry not sorry"

😂

@SeanGallardy they all pre-pay, i don't care

i'm trying to come up with a compile lock demo

do i need a developer for this

josh arrived in a hurry

You just need to start the instance and then DDoS it with, ahem, "microservices" all running the same SP which is 1000 lines long

remember when that was the hotness?

i'm not entirely sure why you used the past tense there

🤔

@ErikDarling 👋

<waving hand emoji>

gosh there's a lot of useless stuff in perfmon counters

SQLServer:Catalog Metadata Cache Hit Ratio Base

@PaulWhite I thought it was a shake weight

did they have those things in the southern hemisphere?

A what now

you asked

I delivered

sigh

"""culture"""

this feels denormalized

> LobSS Provider Destroy Count

this might be a better one

Why am I getting spam from Stack Overflow all of a sudden?

Ah, Teams

you opened teams

the most useless product ever imagined

better get a cooties shot

@ErikDarling SO Teams, not MS Teams

it's all teams to me baby

SO Teams is a weird thing. It's just a meta site without a main, really

I identify as a dog — I go to the bathroom outside, sleep in a crate and have handlers

i didn't know any australians went to the bathroom inside

most surprising thing in the article

Ok, I think that's enough chat for me for a few hours

time for your walk?

@PaulWhite You have to put in the unicode value, typing in the text isn't how it works :D

unicode has no value

it's too much string

It's the one code, though, the only one

Didn't take the blue pill

i've lost track of which pills i've taken

just not the spool pill

What we have here is a problem with spools. Some spools you just can't take. And then you end up with what we have here.

that movie should be required watching

yes it should

I watched it the other day and thought wow what an amazing movie. Hard to watch at some points, but so worth it. Paul Newman is the man lol.

you mean the egg eating contest?

I'd known the quote for a million years from Slash's lyrics

@ErikDarling yeah that was crazy

I know you're all dying to know this, but I love doing T-SQL work in Visual Studio 2022 so much. It's got all the bells and whistles SSMS is missing - of course I have to do a bunch of stuff in SSMS, too, I mean it's not a be-all-and-end-all thing, but the editor is soooo much better in Visual Studio.

And awesome side effect, I now know exactly how to do dacpac deployments that don't suck.

of course git is involved, but what can I say that is awesome too.

GIT-TRANSMOGRIFY(1)                          Git Manual                          GIT-TRANSMOGRIFY(1)

NAME
       git-transmogrify - Transform your repository into an alternate dimension

SYNOPSIS
       git transmogrify [--sprocket-mode] [-z <level>] [--quantum-flux] [--resonate] [-t <target>]

DESCRIPTION
       This command allows you to transcend the boundaries of traditional version control and enter a realm of infinite possibility. By harnessing the power of quantum flux and resonance, you can transmute your repository into a state of flux where the laws of version control no longer a…

Who said ChatGPT was useless.

does it have query plans

this is a good question - I usually default to using SSMS for that kind of thing.

I imagine there is a way to do it because you can run queries in it

blogs.lessthandot.com/index.php/uncategorized/…

not sure if that's still correct

yeah, that works just fine. Of note, though, the key combinations are different, because why would they be the same. i.e. CTRL-M does not get you actual execution plans

I mean you can manually make it the same, but there is no out-of-the-box configuration to match SSMS

I feel like Microsoft could go a long way by just making that stuff all work nicely in VS, and just dump SSMS entirely. I know, heresy.

i'd be fine with that, just stop trying to push ads on me

no kidding - although there are some killer features from ADS that would be nice to have in the first-tier SQL Server Database Tool. Such as runbooks, and the ability to save query results with the query itself, etc.

I know that's a bit problematic, but it would be so great.

Like being able to inline the results with your query, and have the next query appear after those results on the same editor tab would be pretty sweet, for showing how you troubleshoot a problem query and make it go from taking 20 seconds to 20 milliseconds

also, imagine if you could have a single query window attach to a couple of servers so that you could run the same query with an INSERT INTO clause inserting the rows into a local table, so you could compare results from two or more databases or SQL Servers.

I mean the INSERT INTO would gather rows from multiple sources into a single local table

I'm currently using Beyond Compare to do before and after comparisons of data in certain circumstances, but it would be great to be able to do that in SSMS/Visual Studio

I have a query that's been running in SSMS for over 22 hours. It's progressing, but boy does it have a lot of work to do.

msrc.microsoft.com/blog/2024/03/…

@HannahVernon I don’t follow this one

@HannahVernon don’t multi-server queries do this via registered servers?

maybe I'll do a quick demo in ADS if I have time in a bit

@ErikDarling yeah, to some degree they do, but registered servers is a terrible experience IMO

this is a crude example, and you'll have to imagine the queries and results do something interesting, but this is pretty much what I meant:

you wouldn't need to switch between the T-SQL editor window and the results window every time you run a query when you're doing a video

the results just show up inline with the queries you're writing

@HannahVernon Specifically VS 2022, or VS in general?

2019 is ok too. The older versions get less and less appealing

2022 is super quick to start up, which is nice

I see. I'll give it a try. Thanks for tip.

@HannahVernon I think I’m just missing something about how this is different, perhaps because I’ve never used it

@ErikDarling In ADS it's referred to as a Notebook

also, it wasn't a big enough feature to make me start using ADS suddenly

I hated that movie

I'm betting your wife didn't though

Aw man, speaking of things Erik loves.

I just got this notification in my email.

@ErikDarling that's not two screenshots, that's a single screenshot of a single screen, if that helps