The Heap™ – Consultancy ©®

1:09 AM

do we want this on Database Administrators ?

0

Q: How can I as a student practice using Vertica?

I want to learn how to use HP Vertica, but the community edition it offers required that I have access to a cluster. I don't own a server cluster, so is there any alternative or cheap hosting service I could use to try out Vertica? Also, if such a way to try it out exists, which files should I ...

vertica

Paul White

6:25 AM

@ypercube For one thing, the semantics are different w.r.t an empty input. Scalar max returns null, top returns empty set. Second, the optimizer doesn't simplify apply..top for backwards compatibility/common practice reasons.

ypercube

8:26 AM

thnx.

JNK

12:13 PM

good morning all

Gemtastic

Morning!

ConcernedOfTunbridgeWells

Morning

vijayp

12:31 PM

Morning

Kris Gruttemeyer

morning urryone

bluefeet

12:54 PM

morning

Lamak

1:06 PM

morning

John

1:39 PM

morning

mmarie

ooh ooh, me too! morning!

(sorry, I've already had 2 cups of coffee)

Aaron Bertrand

Gemtastic

I'm a morning person and a night owl, but midday is terrible ;P

Kris Gruttemeyer

@AaronBertrand No, but coffee helps :)

Aaron Bertrand

Clutching mine tightly today

mmarie

1:48 PM

I'm not a morning person. I hate mornings. I <3 coffee.

Gemtastic

I suppose I'm lucky then since I don't like coffee :P

mmarie

Neither does @billinkc. He does tea.

Gemtastic

Me too. When I get a job I'm gonna buy me one of those big coffee thermoses you have for conferences or parties, and have my brewed tea in it

When I code I keep forgetting about the tea-making process

So many reboiled kettles, so many cold cups of tea...

Kris Gruttemeyer

Love coffee, the blacker the better. Got hooked while in the Navy, nothing like some good old 3-hour percolated coffee when you're standing watch on a submarine

mmarie

Today I hit a milestone in my morning life. I have mastered the creation of a healthy microwave blueberry muffin. Quick, tasty, healthy breakfast FTW.

I took this blogilates.com/blog/2011/07/11/… and added a bit of agave nectar to sweeten it up a bit.

Gemtastic

1:55 PM

muffins are healthy?

Kris Gruttemeyer

7 wieight watchers points for one muffin, wow!

That's a good bit right there

Gemtastic

Still doesn't sound healthy to me... But on the other hand, I've detoxed on sugar

mmarie

Sorry, I don't speak weight watchers points.

Fine, healthy-ish. Healthier than a real muffin bought from the store.

Gemtastic

I tried having a chocolate muffin today. I had a hard time stomaching because of all the sugar

Kris Gruttemeyer

@Gemtastic Dia-beetus

Gemtastic

1:57 PM

@mmarie I'd say the one you linked is way more "real" than the artificial junk they make in the factories :P

@KrisGruttemeyer I should have it considering how much sugar I ate before I started detoxing

James Lupolt

2:14 PM

TIL that if you search for things like NOT IN vs NOT EXISTS + Bertrand, you get a mixture of Aaron Bertrand and Bertrand Russell in the search results.

2

Aaron Bertrand

I've never had that problem </sarcasm>

:-)

Were you looking for this post? sqlperformance.com/left-anti-semi-join

James Lupolt

@AaronBertrand Yes, reading that now. I was trying to remind myself of all the alternatives

mmarie

Wooohooo! Client opened their Impala dev environment to us, and it has already been upgraded to 2.1. This means I have my window functions back. :-)

billinkc

Close the window, it's chilly this morning

mmarie

Kind of want

bluefeet

2:27 PM

how's your new desk @mmarie?

billinkc

I have one of those, except it looks like

mmarie

@bluefeet I haven't put it together yet. I was going to last night, but decided to go to sleep instead.

bluefeet

@mmarie sleep always wins for me too

ypercube

@Gemtastic are you implying that sugar is toxic?

Gemtastic

@ypercube well, yes and no. It's not toxic, but it's addictive

Aaron Bertrand

2:33 PM

@mmarie that is a neat idea. I'm a scribbler, doodler, and furious note-taker.

billinkc

whoa, gnarly dude

0

Q: Running SSIS Package from WinForms app - Gnarly Error

I have a winforms application that makes use of Microsoft.SqlServer.ManagedDTS to load and execute an SSIS package. When running locally in debug and when installed the package runs fine, when installed on a dev server the package runs fine. When deployed onto a live server I get the following er...

c# winforms ssis

billinkc

3:07 PM

citylab.com/housing/2015/03/…

Twitter maps are not to be trusted

Kermit

3:17 PM

Morning

am I late?

Mark Sinkinson

3:45 PM

@Kermit It's practically pub o'clock

Kermit

@MarkSinkinson if you're across the pond

Zane

@Kermit oh... Does that mean I should not be drinking yet?

Kermit

@Zane i never said such a thing. drinking is for all hours of the day

Zane

Oh good I was worried.

billinkc

4:08 PM

Anyone have SQL Server 2008 (not r2) installed? If so, from a command line, dtexec /help. What is the version string on the second line?

Aaron Bertrand

Sorry, I don't have a machine with only 2008 installed.

Lamak

@billinkc nope

Zane

Is there any greater indicator that a question will be useless than reading this? "I will preface this by saying I am nowhere near being an expert in SQL."

Zane

4:30 PM

Tried to explain a bunch of things that were wrong with this dumb query.

0

A: Getting invalid column name in SQL/Excel

Alright so first off you need to stop using the old style joins for all of the reasons listed here. You are also going to run into an issue that you aren't joining on anything other than one of your tables. I'm pretty sure this will not give you the results that you are looking for. Then final...

ypercube

@Zane yes. a mysql tag ;)

3

Zane

Anyone want to pile on.

Bwahahaha.

ypercube

@Zane 4 tables, one condition. nice

Zane

yeah he's boned there.

I couldn't even bring myself to use his query as an example for a subquery.

Aaron Bertrand

I cry every time I see case statement :-)

Not as weepy as record / field, but close

But more than when I see "I'm using MSSQL"

Zane

4:34 PM

@AaronBertrand oh that's right. I've offended you. That one is stuck in my head still. Feel free to edit the change if it would make you happy :)

Aaron Bertrand

You have not offended me in the slightest. I just think it's an important semantic difference.

Zane

Changed.

Aaron Bertrand

I'd up-vote again if I could

Zane

Lol

Aaron Bertrand

This is a very thorough review of a few of the more popular standing desks

thewirecutter.com/reviews/best-standing-desk

Zane

4:37 PM

I have no clue what this guy is trying to accomplish with his joins.

Aaron Bertrand

I'm still really intrigued by that thing @mmarie posted earlier... is that called the dual toilet paper roll desk?

(I am serious though, I think that's a fantastic idea, except I'd have to move a ton of crap off the desk every time I needed to advance the roll.)

@Zane what about this one:

0

Q: Viewing execution plans for queries with parameters

I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.) I would like to display its execution plan and attempt to tune it by indexing or rewriting some parts of it. I cannot just copy/paste it i...

sql-server query-performance execution-plan

James Lupolt

@billinkc Your desk is a OneNote icon?

mmarie

@AaronBertrand design-milk.com/desk-lets-record-thoughts-ideas-moment

ypercube

@Zane I edited. removed 2 parentheses in the end, check please

Aaron Bertrand

@mmarie nice, thanks

Zane

4:41 PM

@ypercube Oh yeah good call.

@AaronBertrand I must have missed that.

@mmarie oh weird.

I would wreck that constantly. I'm a desk puncher.

@AaronBertrand great scott!

Aaron Bertrand

Obviously generated by some visual query designer. Which should be shot, pissed on, and buried.

Zane

Global temp table too. Nice.

ypercube

My eyes bleed. Allergic reaction to the redundant parentheses. — ypercube 41 secs ago

Aaron Bertrand

Quick, informal poll: Favorite trace flag? Most feared trace flag?

billinkc

3226 shrugs

Aaron Bertrand

4:58 PM

That's a lot of shrugging - hey get your exercise however you can

billinkc

I aspire to fight Grant and win

Oh look, doughnuts

Kris Gruttemeyer

5:18 PM

Our Sharepoint went down for about 3 minutes. Something with our web servers. Is it wrong that I secretly hoped it would just stay down?

billinkc

A non-ugly solution would come from Microsoft in the form of another option. I'm not sure there is a non-ugly work around. — Thursty 4 mins ago

Lamak

damn it, he wasted the chance of saying something like: "A non-ugly solution would come from another user"

JNK

@Lamak A non-ugly solution would come from someone besides your mom!

Kris Gruttemeyer

bazinga

Lamak

@JNK that's overkill

Aaron Bertrand

5:26 PM

Err, my suggestion, just stop using Access

I just meant that I need something in Access to link to right? So I need to have a table back in Access that links to all the "actual" tables in SQL Server (if I'm understanding this correctly). If that is the case, it seems odd to leave 1 table behind in Access as a "link" while all the rest are put in SQL Server — TBrown33 8 mins ago

My turn for 3226 shrugs

mmarie

In case it's helpful to anyone else, MS has a helpful set of icons to use in diagrams microsoft.com/en-gb/download/details.aspx?id=41937

(updated this week)

Lamak

thanks

Kris Gruttemeyer

@mmarie Very nice! I'll tuck those away for later.

billinkc

No matter how I try, I'll never be able to give you something. Something that I just haven't got. — billinkc 50 secs ago

Two Out of Three Ain't Bad

"Two Out of Three Ain't Bad" is a love song performed by the American musician Meat Loaf in his solo career, preceded by "You Took the Words Right Out of My Mouth" and followed by "Paradise by the Dashboard Light". It is a track off his 1977 album Bat Out of Hell, written by Jim Steinman. It reached #11 on the Billboard Hot 100 and earned a million-selling Gold single from the RIAA. It stands as one of his career signature tunes, still enjoying recurrent airplay. It was the final song written for the album. In a 2003 interview for the VH1 Ultimate Albums series, Steinman recalls: "I remember Mimi...

And I'm done

Lamak

@billinkc how deep

billinkc

5:36 PM

also, how derp

Much slashdot once advocated: if you can't be insightful, at least be funny

I fail at both but I'll keep tilting at the second windmill

billinkc

5:59 PM

Nothing to see here, move along... 19M rows to 22k (two times) it's fine. Really

Kris Gruttemeyer

Was this the UNION query that you were talking about yesterday?

billinkc

Yup. Remote call is better than I thought (they cleaned up data there) Local is worse

Kris Gruttemeyer

It's still a giant bag of 'ewwww'

billinkc

ypercube

UNION flag

Kris Gruttemeyer

6:14 PM

SSIS + Sharepoint Source adapter = fail

When SSIS fails, it's error output is as ambiguous as telling me 'it's in that place where I put that thing that time'

mmarie

@billinkc didn't you have good luck with the SharePoint adapter at [client we both worked on that shall not be named]?

billinkc

Francois is the one who ultimately did the SP integration. And last I recall, it never passed validation once deployed

mmarie

oh

ha

Kris Gruttemeyer

Well now, haha

mmarie

My memory of them is hazy, and I'm not that upset about it. :-P

billinkc

6:19 PM

It's not like they ever updated that SP list as it was. Well, except to completely change the layout and make it go from "unlikely to work" to "never going to work"

mmarie

Hey, I fixed it at least twice, so they must have updated it at least twice :-)

Kris Gruttemeyer

Or broke it at least twice. :)

billinkc

Rebuild vs reorg. It's EE so I can and must use an online operation for the above fragmented CI. Rebuild is what I want, yes?

Kris Gruttemeyer

With that level of fragmentation, I would think so, yes.

I don't always rebuild indexes, but when I do (and I paid $$$ for EE), I do them online

billinkc

ALTER INDEX CI ON dbo.foo
REBUILD WITH
(
    PAD_INDEX = OFF
,   STATISTICS_NORECOMPUTE = OFF
,   SORT_IN_TEMPDB = ON
,   ONLINE = ON
,   ALLOW_ROW_LOCKS = ON
,   ALLOW_PAGE_LOCKS = ON
,   FILLFACTOR = 95
);

That look approximately correct?

Took the existing index definition and slapped those things in there.

Kris Gruttemeyer

6:24 PM

Looks good to me, F5 that beast

billinkc

That only took 6 minutes. I am disappoint

mmarie

This is the most helpful Hue/Impala error message I've gotten all day:
Bad status for request 138: TGetOperationStatusResp(status=TStatus(errorCode=None,
errorMessage=None, sqlState=None, infoMessages=None, statusCode=0),
operationState=5, errorMessage=None, sqlState=None, errorCode=None)

That is to say, none of the error messages are helpful. This project does not make me want to get into hadoop.

Lamak

6:40 PM

@billinkc you are so useful

I totally understood the problem and I also created a chart that shows the organization chart but I can't post pictures since I am new to stackoverflow and I don't have reputations. Thank you for editing the list billinkc. — ddag 1 min ago

billinkc

stackoverflow.com/posts/29307676/revisions

Yes, very helpful

Kris Gruttemeyer

@mmarie That's very informational and verbose

Lamak

@billinkc I lol'ed at "spinning cursor"

Zane

aaaaand I'm back.

mmarie

@KrisGruttemeyer Yep. And operationState = 5 just means ERROR_STATE

Lamak

6:47 PM

@Zane welcome back

Aaron Bertrand

Lamak

why is this guy choosing to use the name instead of the id of the supervisor?

also, @billinkc, please go and edit the question again

-1

Q: Develop hierarchy query

I created the scenario shown on the List below so that you can understand my question more clearly. I want to develop a spinning cursor that will enable me to find the top leader of any agent in the bottom of the pyramid in the organization. Consider as if the only information you have is ea...

sql sql-server

Aaron Bertrand

I've had this argument at every company I've worked for.

"But I just want to use John as my handle / e-mail alias / URL."

Which works great until you have two Johns.

Even first name / last name is dangerous - imagine if we hired Dell's Jason Hall, or if ours went to work for Dell?

(I know that's not directly relevant to the supervisor query, but it's kind of related.)

Zane

Technically I'm only back for 30 Minutes. I'm leaving in 30 minutes.

Lamak

@AaronBertrand yeah, then start the variations for the new ones adding the first letter of the second name, etc

Aaron Bertrand

7:01 PM

@Lamak Microsoft had a real problem with that when they had their hiring explosion - lots of funky aliases because the e-mail system still had rules about 8 character user names.

Lamak

yeah, I also remember the nightmare of some table names when they couldn't had those restrictions

Martin Smith

@AaronBertrand - Yes sorry missed you at SQL Bits. You don't happen to be going to SQL Saturday portugal?

Lamak

well, that's good

That sounds better. I will also add that field. Thanks. — ddag 2 mins ago

Aaron Bertrand

@MartinSmith No, won't be there. That event kind of rubs me the wrong way - Niko puts very serious demands on speakers, like only submitting sessions you've never presented elsewhere (which seems counter-intuitive to me, he should want proven quality sessions, not exclusive sessions of questionable quality).

Plus we've been cutting back on trying to go to every SQL Saturday, especially when being there is expensive.

Today @jlupoltsql made me snort coffee out of my nose with something he said yesterday: "How can I make my stinky database more perfumant?"

Tweeted by AaronBertrand on March 27, 2015 at 7:10 PM

8

^ speak of the devil

James Lupolt

Hah

@AaronBertrand I'm surprised to hear that. I thought some of the sessions like Chris Adkin's had also been presented elsewhere.

Aaron Bertrand

7:13 PM

@JamesLupolt well I don't know how good Niko is at enforcement but he certainly made that requirement very clear on twitter about 400 times.

Martin Smith

@AaronBertrand That's totally understandable. I didn't realise that the sessions there had that requirement.

James Lupolt

@AaronBertrand I think I'm wrong anyhow. It looks like the session he's going to present at SQLSaturday Portugal is on the SQLbits site, but with a note that appears to say it was rejected from SQLbits: sqlbits.com/Sessions/Event14/…

Martin Smith

@JamesLupolt Looks like an interesting session actually.

James Lupolt

@MartinSmith Yes, I thought so too. I actually don't know anything about the batch mode stuff.

Other than that it exists in 2012+ and Conor C has talked about it.

Martin Smith

@JamesLupolt There's some good white papers written by Rusanu and others that discuss some elements of this.

Aaron Bertrand

7:18 PM

@MartinSmith yeah, I mean some of us have stuff to do besides trying to impress Niko. :-)

Martin Smith

@AaronBertrand Yes I imagine preparing a new session must be pretty time consuming getting all the content and visuals together.

@JamesLupolt This is the paper I was thinking of specifically but looking at it again it doesn't go into that much detail research.microsoft.com/pubs/193599/…

Aaron Bertrand

Sometimes I want to punch punch punch

Aaron - as mentioned its a wiki type system add where you see fit or add your own answer...its a simple thing. — JonH 41 secs ago

So is updating your own answer that recommends deprecated garbage.

Kris Gruttemeyer

Sweet jesus, I liked the 'look at my comment activity'. It's like he's just arguing for the sake of arguing

Again, the butt-hurtness is strong these days

mmarie

7:34 PM

@AaronBertrand DevConnections had this requirement, but it's not very strict.

Aaron Bertrand

It's not like my initial comment was offensive or overly critical, or like I would remember that I've commented to him specifically before about deprecated objects. If I had more time today I'd run a query against data explorer to see just how many comments I've posted that contain deprecated...

Lamak

asshole:

Aaron - see edit, I hope you sleep better today :-). — JonH 1 min ago

Kris Gruttemeyer

21.4k rep, you'd think he'd shut up and go play in traffic by now

Aaron Bertrand

He's making me want to go play in traffic.

bluefeet

7:53 PM

He can be difficult

Lamak

that's an understatement

Kris Gruttemeyer

I mean, it's simple. What he posted involved something that's deprecated. It works in 2005+ so why even mention the one that isn't going to be around?

Aaron Bertrand

Right, that's all I was trying to point out. Guy can't take any comment at face value.

Kris Gruttemeyer

"I changed the Fill Factor option from 1 to 75." Really? You had instance-wide fill factor set to 1?

Aaron Bertrand

I lulled at that too

Kris Gruttemeyer

8:02 PM

0

Q: Sql Server Fill Factor - Restart sql server services

I changed the Fill Factor option from 1 to 75. When I run a query that displays the fill Factor I am seeing 75 But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True? http://sqlmag.com/database-administration/fill-factor-tr...