The Heap™ – Consultancy ©®

Philᵀᴹ

00:11

At the moment I can’t walk ...

Should be amusing* in the morning

Max Vernon

00:35

Ouch dude that looks nasty!

Philᵀᴹ

00:56

Yeah, i’d got back from rock climbing and wasn’t paying attention on the stairs. Quite looking forward to seeing it in the morning 😂

Evan Carroll

04:24

@JackDouglas you know what would be really cool for dbfiddle, neo4j support.

hot2use

05:44

Good morning

gbn

06:07

for the first time ever, my "syspolicy_purge_history" jobs have failed on SQL2012. WTF

hot2use

06:23

@gbn 'ray

gbn

Is that what the young people say?

Tom V

06:46

Morning

@Philᵀᴹ That'll suck

hot2use

@gbn Just short for Hooray

Change the hostname?

ypercubeᵀᴹ

@Philᵀᴹ that's a bit ironic ("Rock climber injured at stair climbing" ;)

gbn

07:06

@hot2use Our out-source "partner" made some useful changes we had no clue about

hot2use

07:23

@gbn Always a reason to "sing and dance" (aka "swear and cuss")

McNets

morning

dezso

08:55

morning

surprisingly enough, I got a -1 on an old answer yesterday, and now got a +1 on the same

McNets

@dezso Maybe the same user?

Tom V

09:11

@McNets The vote is locked after a couple of minutes, so unless the answer has been edited the original voter can't change his mind

ypercubeᵀᴹ

@TomV and if it changed, it would show as un-downvote (+2) and then upvote (+10)

Tom V

Ah yes, maybe a serial voting reversed or a deleted user then

Paul White

That should say voting corrected or user was deleted

dezso

10:10

@TomV the DV is still there

Philᵀᴹ

10:26

@ypercubeᵀᴹ oh, I know! I’ll be very grumpy if I can’t climb for a few weeks!

gbn

@Philᵀᴹ More grumpy than the average DBA?

ConcernedOfTunbridgeWells

10:55

@Philᵀᴹ Ow

@gbn The only thing more grumpy the average DBA is the 95th percentile DBA.

gbn

@sp_BlitzErik: I'm adding feature requests for sp_databaserstore. At last...

sp_BlitzErik

i was just looking at those

are you planning on adding those things in (like you have existing code), or is it something you want us to work on?

gbn

I'll add them and hopefully be able to commit later

I like git, but lordy it's easy to bollix

I do have code already I can adapt.

I think I have a bug too. @Debug = 1 fails for me: it tries to restore

sp_BlitzErik

ah, cool

as for debug, yeah, it'll execute anyway. if you want to add a noexec param that's cool with me.

gbn

But I need to make sure I'm doing it right first...

ah I see

sp_BlitzErik

11:05

most of our debugging comes from dynamic sql, so it's helpful to get the error msg and the offending block together in output

unfortunate, but hey

gbn

11:20

Crap. can't even clone here (have to use https)

ypercubeᵀᴹ

11:32

@dezso perhaps the question was linked from a newer, relative answer and it got attention

hot2use

@gbn issues with HTTPS? You can force GIT to clone without SSL.

hot2use

11:48

Git config --global http.sslverify "false"

gbn

@hot2use thanks. Will this break other repos that are https?

hot2use

12:38

Yes.

But you could use it in your repo. Then use the flag --local (I think) instead of --global.

Tom V

215

A: How do I set GIT_SSL_NO_VERIFY for specific repos only?

You can do git config http.sslVerify "false" in your specific repo to disable SSL certificate checking for that repo only.

hot2use

Thanks @TomV

Colin 't Hart

Hej

My brain's not working this afternoon:

Andy K

afternoon

Colin 't Hart

What's a good way (in Postgres) to query data for the last year, or the last 50 rows whichever is more?

Want to see some historical data, but if there's not much in the last year, want to show the last 50 events.

dezso

12:43

@Colin'tHart WHERE last_year LIMIT 50 will do exactly that, won't it?

or you mean the last 50, even if they are from this year?

Andriy M

WHERE date <= ... ORDER BY date DESC LIMIT 50?

gbn

@hot2use It's a firewall issue at work

Andriy M

Ah, no, "whichever is more" – so if the last year has more than 50, then it should be more than 50...

Colin 't Hart

Yep, exactly my conundrum

I think I probably need to use ROWNUM()

Andriy M

SELECT
  *
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (ORDER BY date DESC) AS rn
    FROM
      atable
  ) AS derived
WHERE
  date >= CURRENT_TIMESTAMP - INTERVAL 1 YEAR
  OR
  rn <= 50
;

Colin 't Hart

12:57

A bit like the old way of doing LIMIT on Oracle

Andriy M

(not tested)

Colin 't Hart

Yep

I'll do that

Thanks :-)

Andriy M

You're welcome

ypercubeᵀᴹ

@Colin'tHart what does "last year" mean? All data of 2017? Or all data since a year ago (2016-Oct-17)?

Colin 't Hart

The latter.

Trying to show the last year's worth of stock level mutations, but at least 50 rows if it's a slow moving product

Tom V

13:13

120

Q: What options do I have at 26 years old, with 1.2 million USD?

I'm a 26 years old female, got into an accident that almost killed me; I sued, and after attorneys fees and medical bills from hell... I will walk away with around 1.2 million dollars tax free. Here are the crucial details: I have no family (no one I can trust and I appear alone and vulnera...

starting-out-investing

Asking random people on the internet ^^

Lamak

13:24

@TomV I read that last week, she did get some good advice though

Tom V

Yes definitely

I just don't agree with the index funds per se but that's technical

Joe Obbish

@TomV makes sense if you don't want friends or family to know

Tom V

@JoeObbish I'm not saying it was a dumb choice, it's just not something I would consider

Joe Obbish

kids these days

Lamak

@TomV yeah, I have the "luck" of working in an investment bank (or similar), so I'm quite aware of this things, but can be quite hard to grasp for a lot of people

Tom V

13:38

The basic notion that a million won't last very long if you start eating away at the principal amount is already hard to grasp for a lot of people

ypercubeᵀᴹ

@Colin'tHart then dezso got it right. It wasn't clear for me.

Joe Obbish

@Lamak I got some money to invest...

Lamak

@JoeObbish there are quite some good investments opportunities available to you over there that are better for you

Tom V

Yes, the one Seth works for :D

Mar 22 '16 at 20:22, by swasheck

some people thing i work at an investment services company. that's false. i'm actually employed by a failing, idiot rehab center

ypercubeᵀᴹ

@Colin'tHart check this, too (corrected): gist.github.com/anonymous/ef1f5bb79eff2100295430132ceae951

Joe Obbish

13:41

@Lamak no "friends and heap" discount?

Matt

Anyone here actually use ola hallengren's SQL backup procedures?

gbn

@Matt yes, me at least

Matt

...people talking to me.... just a sec

I was curious about a logging choice. In the output log files for most of the tasks it has a job type prefix like DatabaseBackup_(JOBID)_(STEPID)_(DATE)_(TIME).txt

Example: DatabaseBackup_0x1002ED0AF4A6DE429289853FE5D55B91_1_20171017_30000

Why choose JOBID? There is a JOBNAME as well. I am tempted to change them all so they are easier to read.

hot2use

@Matt Yes

Matt

I was trying to figure out if there is a good reason for the ID to be used instead of the name which I find to be more descriptive.

Tom V

13:59

@Matt To make sure it's unique

gbn

I don't like the job setup. I have my own jobs that call SQL directly without this logging. I use his SQL code unchanged of course

Matt

Do you mean you skip the sqlcmd calls and execute the SP's directly?

@TomV I thought that initially but I figured the time stamp would handle that.

Lamak

@JoeObbish don't have that choice, sorry

Tom V

@Matt Jobname isn't unique

it's in the faq

> Using SQL Server Agent tokens for jobid, stepid, date, and time in the output filenames ensures that those filenames are unique.

Andy K

Let's say I have the followinq query

pastebin.com/1egC6PEE

can I replace the 2 left joins below with an IN?

Joe Obbish

14:15

is Code unique?

that's one issue

the other is that if you use IN() then you're stuck with nested loop joins

maybe that's good, maybe that's bad

Andy K

@JoeObbish the location code is unique, so yes

Joe Obbish

I generally don't try rewrites like that

but without seeing a plan, indexes, etc it's hard for me to say much more

Andy K

the code is not very neat, wondering if there is way to deal with it

@JoeObbish an execution plan?

hot2use

@TomV Why isn't JOBNAME unique?

Joe Obbish

are you trying to improve performance or just the coding style?

hot2use

14:21

I can't create a new job with the same name as an existing name.

Andy K

@JoeObbish just the coding style. The performance does not matter as long as it is not taking two hours

but when you have only 2k rows, it should not take long

hot2use

So a combination of DatabaseBackup_(JOBNAME)_(STEPID)_(DATE)_(TIME).txt should result in a unique filename. No?

Joe Obbish

well in that case, having two joins to the same table like that seems perfectly reasonable

Tom V

@hot2use I'm not sure, maybe people log to a file share or log to the same folder from multiple instances

hot2use

@TomV Ah good point.

Joe Obbish

14:23

the HAVING is really ugly imo

Andy K

@JoeObbish is there any way to rewrite this?

hot2use

So DatabaseBackup_(SRVR)_(JOBNAME)_(STEPID)_(DATE)_(TIME).txt would actually be better.

Joe Obbish

this = HAVING?

Andy K

@JoeObbish yes, sorry

@JoeObbish get rid of the having

Joe Obbish

get rid of format, use DATEADD, and move it to the WHERE clause

since the code groups by it I'm pretty sure that you can move it to the where clause without changing results

did not look super carefully

Tom V

14:27

@hot2use Nothing prevents you from changing the output file structure, maybe he has come across situations where he preferred jobid

One thing I know is that he has a crazy powershell datawarehouse thing running where all kinds of data is matched to allow for alerting/monitoring, so maybe he needed the jobid there

Joe Obbish

it smells like bacon in my office :(

gbn

@JoeObbish Why is that a bad thing?

Tom V

Andy K

@JoeObbish pastebin.com/pC4YqrZd

Joe Obbish

@gbn because now I want to eat bacon

but I do not have bacon

@AndyK check DATEADD syntax

also you still have formats

is it not a date column?

Tom V

14:32

@hot2use I saw one of his presentations on that, it was pretty insane

Andy K

@JoeObbish it is ok actually. I'm not writing the query myself, I need to put the business logic out of it for a sql coder to be able to understand everything

ypercubeᵀᴹ

@AndyK I don't see the second join used anywhere. And is that query from an Access database?

Andy K

@ypercubeᵀᴹ yes

@ypercubeᵀᴹ which second join are you talking about?

ypercubeᵀᴹ

@AndyK the 2nd LEFT join from the 2 that you want to convert to IN

Joe Obbish

you tricked me into helping with an access query

6

that is unforgivable

4

ypercubeᵀᴹ

14:41

LEFT JOIN Location_code_Kunshan AS Location_code_Kunshan_1. I don't see Location_code_Kunshan_1 used anywhere

Andy K

@JoeObbish #LMAOOOOO

@ypercubeᵀᴹ I see what you mean. indeed

@JoeObbish OMG. I just laughed out loud in the office

@ypercubeᵀᴹ you see that's the whole point, trying to figure what she did

it is almost like excavating poos

ypercubeᵀᴹ

@AndyK yah, there are so many wrong things about the query. Not counting it's Access

Andy K

@ypercubeᵀᴹ I know

I'm in pain... so in pain...

ypercubeᵀᴹ

Start by relieving some of the pain

- Add aliases into all tables references

Andy K

I will need to drink a glass of red wine to forget that I'm selling my soul with access

@ypercubeᵀᴹ that's a great idea

ypercubeᵀᴹ

14:48

- Then start addressing every useless thing, one by one.

- GROUP BY "O", (seriously?)

Jack Douglas

@EvanCarroll It looks vaguely doable judging by my 10 minutes research — can you suggest a basic sample page like the one I have for pg10?

ypercubeᵀᴹ

- the HAVING should be moved to WHERE

- The NZ() can probably all go.

- The 1st LEFT join should be INNER, the 2nd could go away.

- the FORMAT()` could go, too. etc

hot2use

@ypercubeᵀᴹ thanks

Andy K

@ypercubeᵀᴹ I removed the 2 LEFT JOIN

hot2use

@TomV thanks

ypercubeᵀᴹ

14:50

@AndyK if you removed it, the GROUP BY didn't give you an error?

Andy K

@ypercubeᵀᴹ I need to test that tomo

Andy K

15:12

a guy just came in the SQL forum and said he deleted by mistake his client's fb and that he has no backup...

Forrest

@AndyK Natural selection at work

Andy K

@Forrest that's funny to see that some people can be ... reckless

Matt

@TomV Thanks for the info. They would be unique for me but for how often I have to look at them I will leave it alone. Connecting the IDs is not difficult. Thanks. I had looked in the FAQ but somehow missed that.

Andy K

Thanks @JoeObbish and @ypercubeᵀᴹ. Joe, I hope you were able to overcome your initial shock ;)

Joe Obbish

16:03

well now I can put access on my resume

ypercubeᵀᴹ

@AndyK fb =? facebook? ;)

swasheck

@ypercubeᵀᴹ i was wondering the same thing

ypercubeᵀᴹ

20:09

What part of that can require duplicate entries for computer and user data was not clear? — Paparazzi 30 mins ago

Possibly everything.

Dodi82

20:31

My friend asked me today where would I download a free dataset for a Hadoop - Spark project ?

Any ideas?

ypercubeᵀᴹ

@Dodi82 check this site opendata.stackexchange.com

Dodi82

@ypercubeᵀᴹ Data.gov I heard of this one but wasn't sure whether to suggest it for him or not!

ypercubeᵀᴹ

Example I just found at that site opendata.stackexchange.com/questions/5558/…

You could also download the SO database of questions and answer

13

Q: Download Stack Overflow database

I recently attended a conference where the speaker referenced the Stack Overflow database and actually did queries against it. Some of the queries that he has provided to us also use the Stack Overflow database. I have looked in this forum and in the DBA forum to find it, to download it, so tha...

discussion data-dump

Dodi82

@ypercubeᵀᴹ Looking good! I don't have the experience to work with the unstructured data! But had a similar problem when I worked on my project for BI! I appreciate your help and will immediately forward those useful links to him!

sp_BlitzErik

21:18

@Dodi82 opendata.cityofnewyork.us

There are some interesting ones there

sp_BlitzErik

23:39

@PaulWhite support.microsoft.com/en-us/help/4043951/…

Joe Obbish

good thing I don't use SQL Server 2014 ~

sp_BlitzErik

You know these fixes are coming to 2016

support.microsoft.com/en-us/help/4045814/…

Joe Obbish

"You create a clustered columnstore index on a PARTITIONED table."

safe again!

sp_BlitzErik

Heaven forfend you do an ounce of work

Joe Obbish

I'm too ashamed to work

check out the starred messages

Transcript for

The Heap™ – Consultancy ©®