@SeanGallardy can confirm that worked. Crazy that they'd just foist unwanted CoPilot crap on their customers for more money without asking. That should be illegal.

If only that were the case with many things foisted upon us...

A New MERGE Bug Appears!

Only available on 𝕏 for the moment

> base table bythe same key

FFS

Fixed

It had a red squiggle and everything

Last minute edits FTW

Why does everything I try to do recently immediately reveal some long-standing issue

You’re looking in the right places

Had to happen eventually

Like a dandy little Poirot

> this means Instead of

Missed the random cap on first read

Good spot. Fixed

Have you changed your mind about join alias ordering?

> ON E.id = B.id;

No, that's the way I've always written them

Huh

I thought you had said you prefer the opposite in here somewhat recently

I have had it set that way for years in SQLPrompt

@ErikReasonableRatesDarling I think that's the conversation you're referencing

I came to the same conclusion yesterday.

I don’t know how much you care or it matters, but tue second merge is missing the serializable hint included in the first one

@ErikReasonableRatesDarling There's no need for serializable isolation in that example

I only use hints where they are needed, not as magic dust

@ErikReasonableRatesDarling Thanks for confirming

@PaulWhite Interestingly, WHEN MATCHED THEN DELETE correctly transforms it to an inner join dbfiddle.uk/kqxBsuRV

DELETE of a joined view/CTE is allowed on MERGE, even though it's not allowed on a normal DELETE, as I've noted elsewhere

Hmmm actually that fiddle is deleting from the wrong table, due to this bug. Flipping the join into a right-join gets the correct table, and doesn't fail, but adds a weird Segment-Sequence-Filter for some reason.

@PaulWhite you put this as a dupe, but none of the answers on the linked answers are correct for this scenario. Would it make sense to transfer my answer over and keep it as a dupe or undupify it? dba.stackexchange.com/questions/345403/…

Hmmm seems to depend which column you join on. If you explicitly join Extended.id table then it transforms to inner-join, if you join on Base.id then you still get the error even for the THEN DELETE because it keeps the right-join dbfiddle.uk/MijADflQ

Note in both cases it's attempting to write to Extended not Base.

@SeanGallardy What's your take on my bug report on the above feedback.azure.com/d365community/idea/…

@Charlieface It looks nice, love the font selection

wow, that price increase was really just for co-pilot

but the good news is that it is included in the price increase

Is that good news, though?

@SeanGallardy Aside from the styling... what about the actual bug?

i suppose it's better than paying for something and not getting it

like a vending machine on the fritz

just waiting for that payday to drop

@SeanGallardy Yes of course, imagine all those people having to actually add it on manually, why not have MS do it for you?

@Charlieface No idea, I try to stay far away from query stuff

I'm not a QO/QE fan

even ES not really a fan

sean only breaks hearts and availability groups

Used to be some utility companies would put T.O.L. (trying our luck) on your bill.

what.gif

may have been a joke idk

deadpan

Oh, I wouldn't put it past utility companies though - that's some straight up stuff they'd do

i'd send it back with T.O.T.O.T.

trying out tons of tannerite

Anything that gets special designations to constantly screw you while forcing you to use their crap product is already scammy, so it tracks

well electricity is a pretty good product i think

Forcing me to a provider and then allowing them to willy nilly charge for things is not. It's why I checked the codes for requirements on hooking up to local power, which isn't always a requirement.

I'll be my own electricity provider

starting your own coal plant?

Something like that

well at the very least i hope you get a decent bbq out of it

I use pellets these days, put it on a timer and walk away

oh dear

apparently one drive hadn't opened since november of last year

cpu fans on red alert

That and Teams are the first things I uninstall

i sometimes need to use onedrive as an alternative clipboard

🥴

Truly the end times are near

god i hope so

enough is enough

> -2 The row fetched is missing.

that seems bad

Is it missing missing or just misplaced? Do we need to call the police?

> INFO Completed parsing. Parsed 8008 build files in 117.26s.

maybe your nickname should be titters

@Charlieface That's expected. The whole basis of the issue is that the MATCH finds a row, then the UPDATE clause attempts to update it. Problem is, they're rows from different tables. The first row does exist, the second is the result of the outer join; it's not a real row. See my 𝕏 artcile.

paul 𝕏pects you to read and comprehend

@SeanGallardy Yeah the 'dupe' system is often not perfect. Your answer is the answer to his specific (arguably off-topic) question, thanks to debugging efforts in comments. The dupe target was suggested by another user. On balance, I think it is a useful signpost to that more general Q & A. I don't think it would make sense to merge the Q & As because they're not exact duplicates. Your answer would look strange on the other question. I'm open to changing things if people feel strongly about it.

Heaps of chatter about fans in here this morning, I notice

that question seems v. localized

@PaulWhite True even with an outer join it would still be correct, I was just saying it's interesting that it managed to work out that the outer join should be an inner join in the one case but not the other. In both of them we need an actual row to exist in Extended, but for slightly different reasons, in my case the join condition, in your case the merge update condition.

In the sense that merge is actually a full-join, and the optimizer usually knows that if you only have a THEN UPDATE then it can transform to inner-join, but that's not happened here.

@ErikReasonableRatesDarling That only happens with KEYSET cursors, right? Where the row has been deleted since the keyset was built?

@PaulWhite i don't know, i'm just docs grazing a bit

goddammit

🤣

@ErikReasonableRatesDarling That's my recollection, yes. I never test for -2 unless a keyset is a possible cursor outcome (not necessarily the requested type)

A keyset being a saved set of keys. It needs a lookup at runtime to fetch the rest of the row

A static cursor has the whole row saved in a worktable, so the issue cannot occur

A dynamic cursor only encounters row that exist as it moves around

there is not actually anything in the docs about which statusi are applicable to which cursor types

> -9 The cursor is not performing a fetch operation.

@PaulWhite Yeah I don't know the best way either, that's why I asked.

okie dokie

@SeanGallardy We're all as confused and bewildered as each other then. Normal, in other words. Carry on

@ErikReasonableRatesDarling You sound surprised

i am not

> We're all as confused and bewildered as each other then. Normal, in other words. Carry on

Xeon 6 R1S products announced, already looking at supermicro boards @HannahVernon

@ErikReasonableRatesDarling I have never encountered that value. Don't know if it is even possible (these days)

i would guess it would happen if you had a dangling cursor (no close/deallocate) but apparently not

I think it's possible it comes from the result of a non-fetch FETCH option on the cursor like refresh or info

But that's not supported from T-SQL FETCH

i was curious about those in the api cursors

One never knows where the doc writer got their numbers from

e.g. copied from some API reference the T-SQL keyword claims to mirror

I thought it might happen after a positioned update, but no

huh

I don't suppose it's important

i guess i understand why most people just check for fetch status 0 to keep going

Yeah

you could do some interesting things with @@cursor_rows i guess

it's just such a weird world

You'd think they might have invented a better and more concise construct by now, like a foreach loop. Maybe DECLARE @crsr CURSOR (id bigint, someData varchar(30)); FOREACH @crsr IN (SELECT id, someData FROM someTable) BEGIN DoStuffHere END;

Yes, given their high priority for anything that makes T-SQL better, that is very surprising

it would be neat if there were a way to declare a cursor directly for executing a stored procedure over the results of a query

like a backup procedure pointed at a query against sys.databases or something

What do those words mean?

> declare a cursor directly for executing a stored procedure over the results of a query

Oh, execute a stored procedure once for each row of the cursor?

With the cursor contents providing the procedure parameters?

yes

It sometimes takes me a moment to transpile e-core opcodes

OPcodes

@SeanGallardy looks amazing. 136 PCIe Gen5 lanes is... amazing.

servethehome.com/…

122.88 TB SSD is incredible, too.

i'm not quite sure on that one

maybe an irish goat

ha ha

> maybe an irish goat

wearing a wig

@HannahVernon It's why I grabbed the threadripper last year, for pcie lanes, but I really hate the TR platform after using it, so I'm looking to divest

The nice thing is I can reuse pretty much everything for xeon 6

what's not great about TR?

Not that I've ever used it

I prefer my threads unripped, I must say

There are only official drivers for Client (Win11, not WS), the base power draw (idle) is very high (100+ watts), odd behavior on cpu balancing in the chip

It's great as a workstation for Windows 11

it's terrible as a storage server

IC

do u.

You might say I have hardware envy. My current storage platform is just slightly more dated lol.

that's a telecom 2-post "rack" in case it wasn't obvious.

I need a basement so I can do that too

the two servers are Dell R720XD which makes them about 12 years old

I quite like the two storage arrays though. Each one has 15x 3TB SAS disks, even if they are only 7200k RPM. They were very very inexpensive.

@SeanGallardy I don't believe I ever saw a single basement while I was living in FLA.

I got a tank of an IBM rack from a SAN for $50 from govdeals. Took all the drive bays, PDUs and fiber controllers to electronics recycling. PDUs were 3 phase.

It's still sitting empty in my ground floor basement waiting for rest of workshop to be organized

@CadeRoux I'd never heard of that before; looks interesting.

There's good stuff on there

govdeals.com/asset/832/1419

So that rack weighed a good 1000 pounds easy. Each of the 6 empty drive bays was like 50 pounds. Getting it into the van was insane (we removed everything just to get it light enough to lift in). Getting it unloaded was insane, too. Lesson: never let wife loose on GovDeals.

Her plan was to make it a fake blinkenlights Narnia entrance to the attic. Needless to say, it was far to heavy to make it up any steps, let along three flights.

Instead there is a kind of false cabinet in her loft that hides a door into the attic spaces.

Let's also say that the project to turn an old microfiche cart into a tea cart in that space stalled after it was gutted, and I still have a SECOND microfiche cart on my front porch.

Yes, they were only $5, but that's NOT THE POINT.

Wait til she has the carousel delivered

I'm dreading it

To be fair, I would absolutely love having a few horses (or the zoo animals) to restore and have in the house.

@CadeRoux I can get behind that lol

One can only hope the endangered species find a suitable home

at $225K, one can only imagine the ticket price to ride an endangered animal

whats the ROI like on that.

and it comes in 3 shipping containers, so I imagine shipping is not free lol

All this stuff is you come pick it up

I doubt the average citizen will be bidding at that auction

Are you suggesting zoos & circuses don't make financial sense

So it helps if you are close to the storage facility, but stuff can be anywhere, like a police storage or a library or in this case, I think it's right there in the back of the zoo.

That would make sense

That carousel is only 22 years old, so it should have quite a few more years in it.

What you don't know is that they are putting in a new carousel, which presumably costs more than that.

Is no one going to make a merry-go-round scan pun

not it

@HannahVernon The capex is no doubt stiff, but I think the idea is that riders pay a fee and the incremental costs of spinning the thing and keeping it clean aren't that bad

I don't know how much of a modern market there is for sitting on a wooden zebra while moving in a slow circular motion

Huge, TBH

At least locally

Well, there you go

@HannahVernon The impression I get with TR is that it's basically two entire CPUs on a single die, but without all the rest of the enhancements a server CPU normally gets (PCI and memory lanes etc). So it has a higher draw because it's two full dies, and the two halves communicate as badly as a two-CPU server. So better to get a proper server CPU.

A zebra splinter could be a real thread ripper

@HannahVernon Big fat Dell servers on a two-post rack, 'elf and safety would have a fit. How do they not tip forwards or backwards?

@PaulWhite there's a sentence I never thought I'd read

FYI you can get Dell R730s (or any other #30 gen) for pretty cheap

@HannahVernon Indeed

@Charlieface they are physically connected to overhead 2x12 beams, and pretty much rock solid

@HannahVernon I meant the servers not the posts.

I'm pretty sure the ROI on carousels kicks ass compared to AI, even if it is also negative.

@Charlieface ahhh, they are on shelves screwed into the rack posts.

they've been that way for about 6 years

OK what are the shelves screwed into? Turtles all the way down? How does a shelf take the weight of those things, they're massive

Perhaps AI people will naturally diversify into carousels over time

@Charlieface they weigh about 75 lbs each, yeah, but the shelves have brackets on each side which are about 3U high, so good and solid

I got two R730s here, I put in a PCIe M.2 splitter with two Samsung PM9A3 SSDs each (it allows slot bifurcation so they go in one slot). Pretty fast. Paid about £600 each, plus the SSDs.

It would have been much funnier to say the whole thing fell over ages ago, but that's just not apparent in the picture. It's lying on the floor.

turn it sideways

Horizontal racks will be all the rage

Trillion dollar idea

@Charlieface sweet as.

I think they're even cheaper now, but firmware updates are going to be non-existent probably 1 or 2 years time.

I once had to put in a full 24 port network switch behind someone's desk. Took a 1U rack mount (they do exist) and screwed it flat to the back of the desk. Then they complained about the noise le sigh

I worked somewhere once where a properly critical server was hosted in the disabled toilet of an office

why was the toilet disabled?

it had a fucking great server in it

No, I see the confusion: The toilet was in fact fully functional; it was for disabled people.

On the plus side, it was always nice and warm in there

Spacious too

And then there's me trying to do fresh air cooling in our server room and not putting in air con, so we kept getting issues with overheating. After virtualising a bunch of old servers, and replacing some old switches, I ended up with low temperature alerts this winter. We barely hit freezing, but the servers just weren't warming it up!

Bring some e-cores in and you'll be chipping ice of them in winter

I used to h8 working in server rooms. Noisy, cold, and the air had a weird quality to it

Full of strange people too

Ah the joys of self-colocation, you get none of that....

You do get power outages, which ain't fun, we had one recently.

@HannahVernon That's why they'll never find me!

@PaulWhite you could just give them hugs to keep them warm

🤨