The Heap™ – Consultancy ©®

2:28 AM

Test...

Well, chat works. Getting the error message over on SO but maybe just a sign I should leave the poor ssis questions alone

McNets

6:24 AM

Morning

hot2use

6:50 AM

@TomV Yes, me (Ok, only half Polish) ;-)

Morning all

Andriy M

Morning

gbn

7:11 AM

@hot2use But still 100% Yorkshire?

Morning

@sp_BlitzErik In your 'hood kickaxe.com . Ever been there to throw axes at end user and web monkey effigies?

hot2use

Oo aye mate.

Tom V

@gbn we did something like that for a team building

gbn

@TomV Did you throw at each other then?

I work with them.. but socialising with then...?

Paul White

7:54 AM

Thank you for the information and repro - no need to file a feedback item, I've submitted this internally. I'll update you with any information I receive. — Sean Gallardy - Microsoft 8 hours ago

Answer in comment with three up votes earns no rep.

2

hot2use

:-|

136

Q: How does comment voting and flagging work?

Comments can now be upvoted and flagged. How do those features work? Do users get rep for their upvoted comments? Do upvoted comments have any effect on comment sorting? And can users see their own comment upvote number somewhere? Related: How do comments work? The Stack Overflow blog po...

@SeanGallardy-Microsoft
     .
   .:;:.
 .:;;;;;:.
   ;;;;;
   ;;;;;
   ;;;;;
   ;;;;;
   ;:;;;
   : ;;;
     ;:;
   . :.;
     . :
   .   .

      .

Paul White

I see why people do it, I just kinda wish they didn't sometimes.

But we live in a world with no blue feet so

hot2use

I know. And thanks for teaching me that even a small "answer answer" is better than a "comment answer".

Paul White

Well it is, to my way of thinking anyway. It can always be deleted, after all, in the worst case.

Andy K

9:13 AM

morning and evening

hot2use

9:35 AM

@PaulWhite Product Lifecycle SQL Server 2008 R2 which means it was the 8th July 2014.

Tom V

@gbn To be fair, the technical folks in the company I work with are mostly enjoyable

I don't like company events, but when it's the technical folks together it's usually doing something actually fun and then go for dinner and beers

hot2use

...and beers is mostly fun :-)

James L

10:28 AM

Who is at SQLBits?

hot2use

11:50 AM

The irony:

dba.stackexchange.com/review/low-quality-posts/107072

ypercubeᵀᴹ

@hot2use heh ;)

I find ironic that the question:

> Can anyone tell me what is wrong with this mysql trigger?

was answered with the missing END. There are probably 10+ things wrong with that trigger.

Sean Gallardy - Microsoft

12:12 PM

@PaulWhite so I should make that an answer?

@hot2use Love the ascii art! :)

Paul White

@SeanGallardy-Microsoft I would say so, then update it as you get more information about how columnstore delete bitmaps are mishandled :)

hot2use

@SeanGallardy-Microsoft Didn't want you to miss it ;-)

Sean Gallardy - Microsoft

@PaulWhite LOL - Gotcha

I didn't think it was a good enough answer (or an answer for that matter) so I left the comment. I'll change it to an answer shortly

@hot2use you're the best, I don't care what the EU says about you!

Paul White

@SeanGallardy-Microsoft Maybe think of it as an answer in progress, if that helps.

Sean Gallardy - Microsoft

Sure - that makes sense. You deleted yours which is a good workaround!

Paul White

12:18 PM

@SeanGallardy-Microsoft Well it's not because the question says it doesn't work, and I hadn't tested it at the time. I completely missed that aspect in the question - a column store table on a read only file group prevents anything being DBCC'd in the whole database!

As far as I can tell, it's because the DBCC code doesn't check the file group for the parent for the columnstore delete bitmap rowset correctly.

Sean Gallardy - Microsoft

oh, gotcha, I missed that too!

hot2use

@SeanGallardy-Microsoft blushing

Paul White

@SeanGallardy-Microsoft Makes it much worse eh

Sean Gallardy - Microsoft

@PaulWhite LOL, I'm old.. so...

alright, converted, now to go get my neck and back cracked...

Paul White

@SeanGallardy-Microsoft Younger than I am, I think

Sean Gallardy - Microsoft

12:20 PM

@PaulWhite In "full rotations of the earth around the sun", yes. In how many concussions, broken bones, and times hit by a truck? Probably not ;)

Paul White

Fair point, well presented.

grumble even when people convert comments to answers, they don't delete the comment

It's a tough life

2

Sean Gallardy - Microsoft

I don't have the power!

oh, ok, I do!

Paul White

smh

:-)

Sean Gallardy - Microsoft

I deleted it <looks around nervously for Paul's haunting>

Paul White

wooooooooooooooo

Tom V

12:45 PM

@SeanGallardy-Microsoft You should try to leave as many comments behind as possible between now and December, There usually is a hat for deleting your own old comments

Paul loves hats

Sean Gallardy - Microsoft

1:23 PM

@TomV This. 100%. @PaulWhite will love it!

It may up my hauntings from him, though...

Paul White

Ha! There is never a shortage of comments

Sean Gallardy - Microsoft

I could of that for Easter for him... leave little comment eggs

Andriy M

1:57 PM

Which would then hatch to become full-fledged comments

ConcernedOfTunbridgeWells

@PaulWhite But we still have boobies.

Paul White

true

Paul White

2:49 PM

@SeanGallardy-Microsoft I rescued my workaround.

Joe Obbish

well someone had fun this morning...

Paul White

3:06 PM

For certain values of "fun" yes.

I hope there's a fix for that in CU5.

Paul White

3:26 PM

@ypercubeᵀᴹ Congrats on winning hat bash 2017 btw if I didn't say it before.

Sean Gallardy - Microsoft

@AndriyM lol :)

@PaulWhite I'm not as good with the debugger as you, but mine doesn't throw there.

Paul White

3:42 PM

@SeanGallardy-Microsoft 2017 CU3? Fully repeatable here.

Send me the source code.

Sean Gallardy - Microsoft

Yep, CU3... hrm, I probably doing something wrong. I also didn't get the same stack Tejas did

@PaulWhite I'll gladly redacted skype you lol

Paul White

Ha. I'll just ask Evan :)

He must have it pretty much close to complete by now.

@SeanGallardy-Microsoft Tejas Shah?

Sean Gallardy - Microsoft

@PaulWhite yes

Paul White

Well I'm happy that people are on it. I'm not going to look any more, I've done what I reasonably can. Picked up a few interesting things I didn't know before, so it was worth the time, I think.

Sean Gallardy - Microsoft

@PaulWhite That's awesome :) The community is always glad for the help, I'm sure. I just need to get better with windbg... blah.

I'm sure this is not "working as intended" so I'd wager a guess that there might be a "fix"

Paul White

3:49 PM

Yes I think that's pretty safe. Remains to be seen what level of detail any fix might go into of course.

Depends who writes the KB I guess.

Sean Gallardy - Microsoft

/touches nose

Paul White

You're a good man, Gallardy.

In shocking physical condition, but a good man.

Sean Gallardy - Microsoft

@PaulWhite touching my nose means I don't want to do it. Not sure if it has a different meaning on the other side of the world. I'm kind of like humpty dumpty, except the doctors keep trying to put the pieces back.

I need a shirt that says, "I'm in my 30's and have most of my original parts"

Paul White

Oh right. I took it to mean, it's a secret but leave it with me.

You're still a good man.

Sean Gallardy - Microsoft

@PaulWhite Awww, shucks. I think that's the first time anyone accused me of such things. I'll take it though, keep that one in the back pocket for later.

ypercubeᵀᴹ

3:59 PM

@PaulWhite thnx! When did the stats come out?

Paul White

@ypercubeᵀᴹ Not too sure. Tom mentioned hats so I went looking.

Sean Gallardy - Microsoft

@PaulWhite I looked at the function in your screenshot, I don't see how it would throw there

Paul White

@SeanGallardy-Microsoft Code at 00007ffa368e794c` (sqlmin!CheckOfflineOrReadOnlyFileGroupsLong+0x2bc)

Sean Gallardy - Microsoft

CheckReadOnlyFileGroupsLong

wait, I might be a liar

Paul White

00007ffa`368e792d ba34000000      mov     edx,34h
00007ffa`368e7932 48897c2438      mov     qword ptr [rsp+38h],rdi
00007ffa`368e7937 4489542430      mov     dword ptr [rsp+30h],r10d
00007ffa`368e793c 4889442428      mov     qword ptr [rsp+28h],rax
00007ffa`368e7941 8d4ad2          lea     ecx,[rdx-2Eh]
00007ffa`368e7944 895c2420        mov     dword ptr [rsp+20h],ebx
00007ffa`368e7948 448d42dc        lea     r8d,[rdx-24h]
00007ffa`368e794c ff155eb0cd00    call    qword ptr [sqlmin!_imp_?ex_raiseYAHHHHHZZ (00007ffa`375c29b0)] ds:00007ffa`375c29b0={sqldk!ex_raise (00007ffa`34b6c7c0)}

Sean Gallardy - Microsoft

4:05 PM

CheckOfflineOrReadOnlyFileGroupsLong - yep

Paul White

Stop looking at the 2008 R2 code base :)

Sean Gallardy - Microsoft

Well, @JoeObbish was so fond of it...

Paul White

Not that anyone knows where that is

It only seems like yesterday that SQL Server 2000 was old code

Sean Gallardy - Microsoft

It still is ;)

Paul White

LOL!

Sean Gallardy - Microsoft

4:07 PM

See in mine, I get the stack of

sqlmin!`UtilDbccCheckDatabase'

Paul White

drum roll

Sean Gallardy - Microsoft

doh

Paul White

@SeanGallardy-Microsoft Ah so that's a long way down

Sean Gallardy - Microsoft

yeah

Paul White

Also this time around I was running CHECKTABLE for varety.

Sean Gallardy - Microsoft

4:10 PM

I just did the checkdb...

per repro

Paul White

I mainly did CHECKFILEGROUP

Sean Gallardy - Microsoft

but I do see what you're talking about

Paul White

Sweet

I had fun with primary/secondary columnstore and many other combinations.

Sean Gallardy - Microsoft

Yeah I see what's happening

ok I understand now

Paul White

Also spent some time with sys.syssingleobjrefs and friends.

That schema is security by obfuscation if I ever saw it.

Sean Gallardy - Microsoft

4:12 PM

Pretty sure it's attempting to write to a readonly filegroup

Paul White

Also found a new trace flag (2559)

Joe Obbish

GO ON

Paul White

@SeanGallardy-Microsoft Well it's certainly acting like it intends to, but for all I know it's simply an oversight in terms of what access is being checked.

@JoeObbish Oh it just dumps some dbcc facts gathering debug.

Sean Gallardy - Microsoft

@PaulWhite That's an interesting one, can't say I'd ever actually use it

Joe Obbish

oh

Sean Gallardy - Microsoft

4:14 PM

@PaulWhite Yeah, it hits an assert from what I can tell (obviously) since it dumps

Paul White

Found a bunch of trace flags for DBCC (don't spend time there).

Almost resorted to extended events but luckily there was no need.

I didn't need to be underwhelmed today.

There's already way more interesting stuff to delve into than I have time for.

Sean Gallardy - Microsoft

@PaulWhite Preach. Sometimes I wish I was a robot and didn't need food, exercise, or sleep.

Then I could spend my time on more interesting stuff... than say.. showering.

Paul White

It's shaving that gets me. Pointless bloody exercise.

Joe Obbish

there are people you can hire for that

I'm sure that there are many choices in Florida

Sean Gallardy - Microsoft

Just wax

Paul White

4:18 PM

Hm

Joe Obbish

@PaulWhite why bother?

Sean Gallardy - Microsoft

+1 Joe, I go cave man when I don't have to be seen by people

Paul White

@JoeObbish My wife has strong views on facial hair

She feeds me half the week

Sean Gallardy - Microsoft

@PaulWhite Hold on, let me make popcorn

Joe Obbish

I have a strong winter beard at the moment

Paul White

4:19 PM

But it's summer!

Sean Gallardy - Microsoft

@JoeObbish Pics or GTFO

It's always summer in Florida

Joe Obbish

I don't do selfies

Paul White

And Phoenix I discovered yesterday

19 hours ago, by Taryn

@PaulWhite https://www.currentresults.com/Weather/Arizona/Places/phoenix-temperatures-by-mo‌nth-average.php

Sean Gallardy - Microsoft

usclimatedata.com/climate/tampa/florida/united-states/usfl0481

currentresults.com/Weather/Florida/Places/…

Paul White

That's much more acceptable

Sean Gallardy - Microsoft

4:22 PM

You should have your winter home over here

then you could shoulder surf with me

sp_BlitzErik

@gbn I haven't, hahaha, but I might when I get back. That looks fun and I can drink.

Sean Gallardy - Microsoft

@sp_BlitzErik tsk tsk tsk

Paul White

@SeanGallardy-Microsoft Sounds awesome. Problem is, again, the wife. She likes four distinct seasons.

I should get a divorce eh.

Sean Gallardy - Microsoft

You could just take a short "hiatus" from her for a week?

Paul White

@sp_BlitzErik How's things going?

People constantly trying to get me on a bloody plane.

Joe Obbish

4:24 PM

I'm surprised your phone is plugged in

Paul White

On silent most of the day :)

Sean Gallardy - Microsoft

Take a boat... but make sure it has satellite wifi...

@JoeObbish It better be, his phone runs half of the production SQL Servers.

Paul White

Ooo cruisy

Sean Gallardy - Microsoft

I only ask that you bring me something from NZ

Paul White

Of course

Sean Gallardy - Microsoft

4:27 PM

You all have some great emeralds and sapphires down there...

just sayin

sp_BlitzErik

@PaulWhite be nice!

Paul White

I'll bring you a en.wikipedia.org/wiki/Mere_(weapon)

Should come in handy around the office

Sean Gallardy - Microsoft

That's awesome!

Paul White

@sp_BlitzErik I have a glass of wine!

Sean Gallardy - Microsoft

I'd use it to knock over Buck Woody's chess game

Joe Obbish

4:29 PM

ask for lemonade apples

Paul White

Perfect!

Sean Gallardy - Microsoft

I'll give you something Floridian, your choices are... A pet Gator, A pet Boa, A pet Ibis, or a new white trash wife.

sp_BlitzErik

@PaulWhite sounds like a good time to make life decisions then

Sean Gallardy - Microsoft

Red/White/Rose?

sp_BlitzErik

@SeanGallardy-Microsoft today I learned Conor's favorite kind of cookie is chocolate chip

Sean Gallardy - Microsoft

4:32 PM

@sp_BlitzErik Hey, if you get a chance... tell Bob I say "hi" and also "Woo baby Eagles won"

Paul White

@SeanGallardy-Microsoft Rose

ngaitahupounamu.com/shop/catalogue/mere

Sean Gallardy - Microsoft

He's sending me his copy of SI because it has the Eagles on the front.. and... "Nothing Eagles is allowed in my house"

Paul White

@sp_BlitzErik Isn't that a little ... pedestrian?

Sean Gallardy - Microsoft

@PaulWhite Holy bagesus that's expensive

Paul White

Cheap imitations are available but that's the real deal

Joe Obbish

4:33 PM

@PaulWhite what is that supposed to be?

is that a club?

Paul White

> Mere are a significant symbol representing the Mana of the owner. Mere are weapons which are handed down through the generations. They are often seen as beings imbued with a soul and part of the whanau or family. Traditionally, mere were reserved for warriors of distinguished mana and powers. Today, mere are gifts or taonga of high meaning and regard.

See also previous wikipedia linky

Andy K

I was Black Panther yesterday

Paul White

My wedding ring is pounamu

Turns out I'm allergic to silver, gold, titanium etc.

Sean Gallardy - Microsoft

That's some awesome history

Paul White

Yep I think so

Sean Gallardy - Microsoft

4:38 PM

@PaulWhite We implant gold in people because it literally doesn't react to anything.

You're super special, more special than I

Paul White

It's not pure gold in rings of course

Sean Gallardy - Microsoft

ah, it's probably cut with nickel

tons of people are allergic to nickel

Paul White

For sure. Major allergy to Ni.

Discovered that as a teen when I had my ear pierced.

Sean Gallardy - Microsoft

OTOH it helps you figure out if metal is pure or not ;)

TIL: Paul is a rebel

Paul White

I'm basically a scientific instrument at this point

sp_BlitzErik

4:40 PM

@SeanGallardy-Microsoft I'll wait until he's unreasonably drunk

Sean Gallardy - Microsoft

HAHAHA

sp_BlitzErik

@PaulWhite I'll let him know you think so 😗

Paul White

@sp_BlitzErik You realize this whole chat room is NDA right

Joe Obbish

only if you delete all of the messages

Paul White

@JoeObbish How's the latency/dropped packets today?

sp_BlitzErik

4:43 PM

@PaulWhite it's NDA only because everyone is too embarrassed to admit they're in here.

Joe Obbish

@PaulWhite 0%

I was on a party line, apparently

Paul White

@sp_BlitzErik Harsh!

Joe Obbish

half of my packets went to someone else

Taryn

@PaulWhite what happens in the heap stays in the heap

Paul White

One can only hope!

Taryn's an unusual name. Haven't encountered one of those before.

tbf bluefeet was as well

:-/

Taryn

4:45 PM

It's a rarity

Paul White

A unique identifier

sp_BlitzErik

I once made friends with a stripper going to veterinary school named Taryn.

Paul White

@SeanGallardy-Microsoft I suppose asking for all four would be too greedy?

Sean Gallardy - Microsoft

@PaulWhite That's the American way, not at all!

Taryn

@sp_BlitzErik cool story, bro

Paul White

4:51 PM

I wonder why vets would need to be able to remove wallpaper efficiently

Sean Gallardy - Microsoft

@PaulWhite the Toki are pretty neat too, and I'm not even a jewelry person

Some beautiful Jade down there

Paul White

Yes toki are very popular

@SeanGallardy-Microsoft The other end of the spectrum thewarehouse.co.nz/c/clothing-jewellery/fine-jewellery/…

But you don't want those

Zane

5:22 PM

Gah Windows team has shadowcopy taking up all my drive space.

Dang hidden files. Drive me nuts.

Sean Gallardy - Microsoft

> Drive me nuts.

I see what you did there..

ypercubeᵀᴹ

5:54 PM

Sure, @RDFozz - I was just being sloppy. I will add it to the 2nd index as well. Under some circumstances SQL Server will not use an index with a WHERE clause, if the columns referenced in the where clause are not part of the indexed columns. Probably not a big deal for this since we are really only using each index as a unique constraint. Although, if there were a lot of rows and you were looking for company owners, those indexes might help. — Max Vernon 3 mins ago

@MaxVernon is (was) there a connect item for this? Sounds like something they should fix.

Max Vernon

@ypercubeᵀᴹ I think there was. My memory is not serving me very well in regards to connect items :-)

ypercubeᵀᴹ

@MaxVernon I'm a bit confused by your answer, by the way.

Max Vernon

@sp_BlitzErik wrote about it here

@ypercubeᵀᴹ I must have written it wrong then.

ypercubeᵀᴹ

How do you enforce that a User belongs to a single company?

Max Vernon

@ypercubeᵀᴹ oh, was that part of the question? I thought there were saying a user can only "own" a single company.

although that could pretty easily be added as a unique constraint, too.

ypercubeᵀᴹ

6:03 PM

> I have 2 Tables in sql database, User and Company. User belongs to one company So user table has UserID & CompanyID(foreign key)

Max Vernon

lol, it's the 2nd sentence.

I'll update

Evan Carroll

wtf

does bitor bind higher than bitshift?

who the hell thought that was a good idea

Max Vernon

you're speaking in tongues again, Evan.

Evan Carroll

SELECT 1::bit(32); -- 00000000000000000000000000000001

SELECT 1::bit(32)<<8; -- 00000000000000000000000100000000

SELECT 1::bit(32)<<16; -- 00000000000000010000000000000000

SELECT 1::bit(32)<<8 | 0::bit(32)<<16; -- 00000001000000000000000000000000

Because PostgreSQL is the devil.

2

I want SELECT (1::bit(32)<<8) | (0::bit(32)<<16);

Max Vernon

ohhh, "bitwise or" versus "bitwise shift". gotcha. I got nothin'.

@ypercubeᵀᴹ I added a bit to the bottom of my answer, although that requirement makes the requirement that a person can only own one company a moot point.

unless they can own a company they aren't a member of.

Evan Carroll

6:33 PM

2

A: PostgreSQL: ERROR row is too big

Raster Data The table is to store data collected from a device. The device produces a PNG image as part of an analysis. The PNG consists of 1024 pixels. Each pixel has an associated numeric value. Along with the pixel data are various other fields related to the analysis. Breaking the table i...

Max Vernon

@EvanCarroll twss

Evan Carroll

twss?

Max Vernon

google, much?

:-)

Evan Carroll

6:48 PM

Never heard of it.

You know what's really awesome. Posting "Finally a principled candidate that believes in cutting Medicare" on popular posts on a local politician's Facebook page.

ypercubeᵀᴹ

@MaxVernon yeah, that is a complication that he hasn't clarified.

McNets

7:12 PM

@MaxVernon use my fiddle dbfiddle.uk/…

Zane

Hmmm even with looking at windows reserved files I only see 50 of 122 gigs taken up yet the drive is showing as full.

No shadowcopy and the page file is pretty small. I don't get it.

McNets

@MaxVernon you can add [Id] and order it by [Id], [Group]

Maybe a bit more clear

Evan Carroll

@McNets I fixed up the question and answer here: dba.stackexchange.com/a/198592/2639 Hope it's all good with you.

That question was goofy. 0x92 isn't valid ascii the higher order bit is set.

McNets

@EvanCarroll yes, it's perfect. Thank you Evan

Max Vernon

@McNets I appreciate it, but I find working in a browser to be too mind-numbing when I can just use SSMS instead. If I don't have SSMS, or the question is about some other DBMS, then that works great.

also, sorry if I pulled the "fastest gun in the west" on you.

McNets

7:23 PM

@MaxVernon Please, no worries.

hot2use

Andriy M

Auto-antonym

An auto-antonym or autantonym, also called a contronym or contranym, is a word with multiple meanings (senses) of which one is the reverse of another. For example, the word cleave can mean "to cut apart" or "to bind together". This phenomenon is called enantiosemy, enantionymy or antilogy (enantio- means "opposite"). An enantiosemic term is necessarily polysemic. == Nomenclature == The terms "autantonym" and "contronym" were coined by Joseph T. Shipley in 1960 and Jack Herring in 1962, respectively. An auto-antonym is alternatively called an antagonym, Janus word (after the Roman god with two faces...

McNets

@MaxVernon I have SSMS at home, but none DB installed, I usually connect via VPN to my SQL Servers. I should install an express edition.

hot2use

@PaulWhite any chance you‘ll be in N.Z. from 22/12/2018 till 18/01/2019?

@AndriyM Cool

Andriy M

@hot2use Probably not something specific to just English and German either. I'm sure there are such words in Russian, just can't remember them right now

Tom V

7:42 PM

Corpulent Brony

11 1

sql-server sql-server-2016 computed-column query-performance

Dat nickname tho

Max Vernon

@McNets Developer Edition. It's the "free" version of Enterprise Edition for use in development work (i.e. no production data). It has all the bells-and-whistles of Enterprise Edition

go.microsoft.com/fwlink/?linkid=853016

McNets

@MaxVernon yes, I know, but my current (home) laptop is not good, good ones usually end up in the hands of my children, I don't know why...

Max Vernon

@McNets fair enough! Just wanted to "share the love" ;-)

McNets

@MaxVernon oh sure ha ha

ypercubeᵀᴹ

8:22 PM

> Vampire coffee: coffee, sugar, milk and a drop of blood

ypercubeᵀᴹ

10:16 PM

You know, some things aren't meant for a DB to do. — tombom 7 hours ago

some comments need to be starred

Andriy M

10:30 PM

Two upvotes?!

Such a lazy question

This question shows research effort; it is useful and clear. Not

ypercubeᵀᴹ

I'm surprised no one answered in the 7 hours it had been open.

Comment-answers don't count ;)

There's no magic in MySQL that would automatically split a column into multiple ones one character per column. However, if you are not afraid of getting your hands dirty, you could try and stick together ten expressions that would do the job using MySQL's collection of string functions. — Andriy M 9 mins ago

Andriy M

10:51 PM

I really don't want it to be answered in this state, if I'm entirely honest.

ypercubeᵀᴹ

it's on hold now

Andriy M

It is, and I'm relieved, even though it's such a trivial matter. And yet thank you, @ypercubeᵀᴹ.

Sean Gallardy - Microsoft

11:16 PM

@EvanCarroll That would depend, are you talking about the original ascii of 127 (which then , it wouldn't fit) or the "extended" set, which it would fit?

@ypercubeᵀᴹ Yes-huh they do! Just ask me!

Transcript for

The Heap™ – Consultancy ©®