The Heap™ – Consultancy ©®

swasheck

00:00

@MikeFal ^^^^

hichris123

03:03

@PaulWhite Assuming a question is about databases, is off-topic for SO, and is not low quality, is there a good chance it should be migrated?

I can't tell if DBA accepts all database type questions or if there's some questions that would just be rejected.

Paul White

03:16

@hichris123 There's a good chance, yes: if it is well-asked, and aimed at getting an answer from a database professional they are usually well received by the community here. There are exceptions (licencing questions and product recommendations for example). Feel free to ping us here or in the TL before migrating if you want to get a feel for it with some concrete examples.

Michael Green

03:27

@JamesLupolt If you have full history, couldn't you group values by date/ hour/ day-of-week, calculate the N-tile for each group, and determine if the current measurement lies acceptably close to the median?

hichris123

@PaulWhite Alright. Usually I only get to the ones with three/four votes to migrate since I'm not an SO mod (and I'm too scared to vote to migrate otherwise ;)).

Paul White

@hichris123 Yep no worries.

Michael Green

04:21

@swasheck Scottish whisky is distilled twice. Irish Whiskey thrice.

Michael Green

04:35

@MaxVernon cbc.ca/news/trending/bricking-it-for-canada-1.3479184

ypercubeᵀᴹ

08:53

It's fun to think about, but I don't know any way to achieve what you want reasonably given the constraints in the question. It seems like any optimal solution would require a new data structure of some type; the closest we have being the 'function index' approximation provided by an index on a non-persisted computed column as above. — Paul White ♦ 4 hours ago

@PaulWhite I was thinking that a spatial index might be useful. But SQL Server has spatial indexes only for geometry columns, right? It would be some interesting challenge to convert datetimes to geometry (and back) just to benefit from a spatial index. And not worth the effort, when a computed column can solve the issue there.

Paul White

@ypercubeᵀᴹ Yes that's right.

Phil

09:18

Yawn

Mathematics

why not

Phil

09:48

Some sort of clue would be good. Like, what software is it? :P

Mark Sinkinson

@Phil SSRS

I could have sworn I'd blocked all SSRS talk on here after last time...

ypercubeᵀᴹ

@MarkSinkinson We do have ssrs-support in the room's tags ;)

@Mathematics You could have just given us the link to your (SO) question. You have more info there. stackoverflow.com/questions/36080559/…

1

Q: Managing a development database in a group project

I'm working for a small webdevelopment company and we want to set up development environments. We'll be using the Laravel framework in combination with Homestead. Now, Homestead has its own database and can work with migrations from Laravel, meaning that any changes to the existing database from ...

> The biggest con to Laravel's migrations is that all the previously stored data in the database will be lost for every migration ...

That sounds ... f**ed up.

James Lupolt

10:06

@MichaelGreen I actually don't have the full history, just summaries (min, max, total, sample standard deviation, median) for every 15 minute window going back a few months. So I can't calculate individual percentiles (AFAIK), or an exponentially weighted moving average. I've moved on to trying a different approach with a simple moving average and a rolling variance for now until I change the way data is being collected.

Happy to get other ideas, though. At some point I'm also going to have to deal with the fact that much of this data isn't going to be even close to normal.

Tom V

@ypercubeᵀᴹ @Mathematics Still not nearly enough though

Andriy M

10:29

@Mathematics Because you didn't define any?

Andriy M

10:52

This question is much more elaborate (owing, I suspect, to Paul's editing in details from now deleted comments) than its SO counterpart (posted after 15 minutes of probably anxious but, even to this point, fruitless waiting for an answer from database experts).

1

Q: How to update first non null column of each row

I work in a hospital. We figured it out the patient billing table is missing some of the tests done on the patients, so now I have to find the missing tests and add to each patient's record. The reason to add to the first non null column is because data from the table is again has to be put thro...

sql-server sql sql-server-2008

Should we just close it or would it make sense to migrate it to SO and merge the other one into it?

Ah, yes, I'm asking this because the SO question's got an answer now (and it does seem like the answerer have managed to decipher what the problem is).

Paul White

@AndriyM I'm going to close it and add my standard comment about cross-site duplicates. If he wants a dba answer, the so question will have to be migrated here and merged.

Andriy M

@PaulWhite Fair enough

Paul White

@AndriyM And yes there were a bunch of comments by the OP clarifying which I added to the question.

@AndriyM Done. Thanks for the heads up.

ypercubeᵀᴹ

11:08

Another cross-post: dba.stackexchange.com/questions/132636/…

Good thing, they deleted the dba one.

Phil

room topic changed to The Heap™ - Consultancy ©®: General on- and off-site discussion for dba.stackexchange.com [mysql] [nosql] [oracle] [postgresql] [sql-server]

Paul White

@ypercubeᵀᴹ Love the edit about there being no i in English :)

ypercubeᵀᴹ

@PaulWhite It irks me more than performant. More than fields and records, even. These i and u

Paul White

@ypercubeᵀᴹ Me too.

There seems to be a strong correlation between laziness like that and poor quality contributions.

4

Andriy M

My theory is it's what texting does to people.

ypercubeᵀᴹ

11:12

There was a question in the English site some time ago about "i" and its usage in India (I think)

Paul White

@ypercubeᵀᴹ I'm sure it's common there. Doesn't reduce the irk level here though :)

Andriy M

They could argue it's same as American vs British spelling.

"Why are people correcting my perfectly correct Indian English?"

3

ypercubeᵀᴹ

I can't find the q. I remember it asked whether it was common in Asia (or India) to use i instead of I, as a sign of being humble. The answers were "No, it's just a common error" as far as I remember.

I found some nice, but irrelevant questions though:

153

Q: What is the origin of ZOMG?

I have looked in a number of places, with contradicting results. The Urban Dictionary provides a whopping 73 "explanations", of which I will quote just a few. (Original spelling and punctuation preserved.) ZOMG is a varient of the all-too-popular acronym "OMG", meaning "Oh My God". ZOMG...

etymology slang internet

110

Q: What’s a “handegg”?

What’s a handegg? NOTE: This question is primarily related to the etymology of a compound noun which is not in The Dictionary. There is a hat this year called “Handegg”, given out for a posting that reaches a score of +7. But here’s the problem: the word handegg does not occur in the Oxford En...

etymology slang history connotation winterfest

Paul White

@AndriyM Ha ha. No.

ypercubeᵀᴹ

@AndriyM Nice. I should start with: "I have a doubt: Why ...?"

88

A: Can "doubt" sometimes mean "question"?

This is Indian English. See Vishy's Indian English Dictionary. July 12, 2006Vishy's Indian English Dictionary: doubt doubt. /DOWT/. A question asking for clarification. In standard English and American, the noun doubt is uncountable and refers to a lack of complete trust in so...

Paul White

11:30

@ypercubeᵀᴹ Surely "I have one doubt..."

swasheck

12:18

@JamesLupolt when I actually wake up, I'd like to discuss this with you more

Phil

12:58

@ypercubeᵀᴹ "Zoh My God! (gaming, World of Warcraft)". I've played WoW for the last 11 years, and I've never heard of "Zoh My God"

Mathematics

14:03

@AndriyM define what ? :)

do I need to define constants now

I just want to set default value to true in the dataset

Lamak

@Mathematics is this SSRS?

Paul White

1

Q: SSRS why I can't use constants?

I have a shared dataset with stored procedure which has optional parameter like this, @OnlyUniqueEmail BIT = 0 and in end of I have predicate WHERE (@OnlyUniqueEmail = 0 OR @OnlyUniqueEmail IS NULL ) OR ((@OnlyUniqueEmail = 1 ) AND a.RowNumber = 1) But when I try to gi...

sql-server reporting-services sql-server-2012

Lamak

@PaulWhite ah, thanks

Mathematics

@Lamak Yes

I am trying to follow this

blog.hoegaerden.be/2012/03/20/…

well not really following

but in picture they have constants true and false

Lamak

@Mathematics well, that image doesn't seem to match with what you should get when going to the parameters properties of your dataset

Mathematics

14:11

true, they get these values for report parameters

I wonder why I can't use them for dataset parameters

should be straight forward

Lamak

@Mathematics it is

Mathematics

I mean adding default value :)

I can't seem to find any article on it

Lamak

@Mathematics I mean that too

you should go here:

Andriy M

@Mathematics I'm no SSRS expert, but my guess was the Constants was supposed to contain only named (user-defined) constants

Max Vernon

@MichaelGreen thanks! That's genius! I'm surprised I haven't heard of that particular variant before.

Lamak

14:18

@AndriyM I'm saying that you shouldn't have gotten that option when going to the parameters. Do you see in your environment what I showed in my image?

Andriy M

As for "true" or "false", those are probably considered literals and you should simply type them in

Lamak

@AndriyM oops, sorry, I thought that your comment came from @Mathematics

kindly disregard

Paul White

@AndriyM How come you've never created a custom avatar? Just curious.

Mathematics

@PaulWhite Avatar contribute in making this earth less greener

Paul White

Do they.

Mathematics

14:31

@Lamak This is what I am trying

this is a shared dataset parameter and I want to give it default value "True"

I tried checking the box and typing

True

and also tried
1

but it just doesn't takes it

Andriy M

@PaulWhite Wow, I'm really not sure which is easier, to answer your question or to just create the actual thing

Create is not a problem, of course; decide to use it, on the other hand...

Lamak

@Mathematics so you can't actually see the "parameters" folder on its own?

as shown in my image?

Mathematics

I am not setting this at report level, you can assume I don't have any report at the moment

even though I am using one for testing

Andriy M

14:49

Not sure if this applies: Constants in Expressions (Report Builder and SSRS)

If it does, then maybe you should type ="True" or =1 – in short, it may need to begin with an equal sign.

swasheck

@MichaelGreen thanks for the information

> Scotch whisky is distilled twice, while Irish whiskey undergoes triple distillation.
Scotch whisky uses peat-smoked, wholly malted barley, while Irish whisky used kiln-dried, raw and malted barley.
Scotch whisky is produced by ‘blending’, while Irish whiskey is produced by ‘vatting’.
Scotch whisky is casked for a at least two years, while Irish whisky is kept in the cask for at least three years.

Read more: Difference Between Irish whiskey and Scotch whisky | Difference Between | Irish whiskey vs Scotch whisky http://www.differencebetween.net/object/difference-between-irish-whiskey-and-s…

Andriy M

"e" for "extra distillation", perhaps

swasheck

however

differencebetween.net/object/…

Mathematics

15:05

why would someone add entries to host files to server's IP , pointing it to itself

shouldn't one use 127.0.0.1 if must need to but why someone would add server's ip address (internal ip)

Max Vernon

Should the startup tag go away? I think it's meaningless.

dba.stackexchange.com/questions/tagged/startup

Mathematics

yes, it should

Max Vernon

@Mathematics poor man's dns alias?

Mathematics

@MaxVernon spent all day fixing web services 401, thought it was pointing ip to TMG

Max Vernon

TMG ?

Mathematics

15:11

ForeFront Threat Management server

Max Vernon

ahhhh

swasheck

@MaxVernon i used to call that ... fore... nevermind

Max Vernon

@Mathematics - do you have any quick ideas why a self-hosted Windows WordPress site running on IIS 7 with full control over it's website root folder would be getting "access denied" errors when trying to save media into the wp-content folder?

the App Pool has a specific identity assigned, which has directly granted Full Control over the root folder of the website, and inheritance is enabled.

And it's gone (original)

►

swasheck

our virtualized environment can kiss my ass

Max Vernon

15:28

@swasheck I so feel the same way.

makes every problem just that much harder to diagnose.

Master Database

-1

Q: Should the [startup] tag go away?

For me, this tag is unhelpful. http://dba.stackexchange.com/questions/tagged/startup

discussion

Mathematics

15:45

@MaxVernon never hosted wordpress on IIS, so no idea

sometimes some folders are just in readonly mode, so that could be it

but it's just a blind guess

if code is def running under application pool account and folder is not readonly then I can't think of anything else

miguelmpn

16:29

Good afternoon for all of you
When querying for a view is it possible to filter by the underlying tables?
something like this..

SELECT * FROM score WHERE results.created >= 2016-01-01 12:00

dezso

@miguelmpn From what I've seen on the different systems, it only works if you have this column in the view, too

Andriy M

@miguelmpn And of course it would be impossible to reference it by the underlying table's name/alias. If it's exposed by the view, it can only be qualified with the view's name.

miguelmpn

16:52

So there is no way to reference filter columns that are hidden on the view?

Andriy M

Correct

swasheck

if it's exposed by the view then you can filter it.

miguelmpn

ok

swasheck

if it's not ...

nope

miguelmpn

how can I expose it? I have an alias for all columns

(all columns of the view)

ypercubeᵀᴹ

16:56

@miguelmpn Can you alter the view?

If yes, you can add more columns.

Mike Fal

Ok Heapers, I'm going to ask the initial question here before I write and post a question to the site: I am trying to write a UDF that lives in a utility database. This function will give me info on all the tables in a database, but I want that database context to be dynamic.

Normally this would be a dynamic sql string in a sproc, but a UDF feels like a better way to do this.

Andriy M

@miguelmpn "Expose" in this case simply means "add the column to the view's SELECT list".

That is, you need to change the view's definition for that, of course

Mike Fal

Is this do-able? Barring a "God that's stupid, do it like blah" I'll write up a question for the site.

Andriy M

@MikeFal Not stupid, but not possible either.

You can't have dynamic SQL in a function

Mike Fal

@AndriyM Bleh, I was afraid of that

I feel like I should file a connect item "Allow for sp_ExecuteSQL to take a database name as a parameter and execute the submitted query in that database context"

That would totally solve my problem.

ypercubeᵀᴹ

17:01

@AndriyM if it's CLR?

Andriy M

@ypercubeᵀᴹ Don't know about CLR, sorry. I should have specified that I meant a T-SQL function.

Mike Fal

Yeah, I was talking T-SQL function. CLR WAAAAAAAAAAAAY overcomplicates it

ypercubeᵀᴹ

@AndriyM Yes, I undestood you meant that.

James Lupolt

@swasheck Sure.

swasheck

17:38

@JamesLupolt so you're having problems getting the exponential moving average from the data that you have? or you've found a way around it and now you're doing other things?

James Lupolt

@swasheck From the data that I have. So I decided to use an SMA for now.

swasheck

@JamesLupolt what are you pulling? from a dmv?

James Lupolt

You can't calculate an EMA without the whole history, or a previously known EMA, right?

@swasheck Most of it is from the SQLSentry database.

swasheck

@JamesLupolt right.

James Lupolt

Unfortunately I started collecting data without figuring out how to use it first. So I did some dumb things.

swasheck

17:42

@JamesLupolt ok. so sql sentry provides a min/max/stdev and you're calculating SMA from ... ?

James Lupolt

@swasheck Might take a while to explain

I started loading all the metrics from the SQLSentry database into a star schema

swasheck

i'm not familiar with what sqlsentry is providing but i was wondering if you could run your EMA on one of the data points ... like EMA on Max runtime

James Lupolt

I store all the measurements for a few days, then 15 minute summaries of everything after that

swasheck

ok

James Lupolt

For each 15 minute interval, I have a total, min, max, mean, median, and standard deviation

And measurement count

So I use total and measurement count to get an SMA

swasheck

17:46

got it

James Lupolt

The SMA is just over that 15 minute window, on the same day of the week, for as far back as I have history

swasheck

and you're looking at query performance? counters?

James Lupolt

So I wrote a proc that looks at the history for that 15 minute window, that shows me anything that's more than a certain number of standard deviations away from the SMA

The idea was just to have a quick way to ask 'what's different now' instead of looking at charts

swasheck

(sorry ... connecting to my sqlsentry datastore and am re-acquainting myself with the schema)

@JamesLupolt 1.95? :)

James Lupolt

@swasheck Hah. I just picked 2 by default.

It's perf counters, wait stats, and... something I'm forgetting

I haven't added query performance yet

swasheck

17:49

@JamesLupolt that's generally pretty safe ... that's 95% of a normalized distribution :)

James Lupolt

Nice edit : )

swasheck

yeah

realized the (standard) error of my ways

James Lupolt

I haven't looked at how normal it is yet

To be honest... I don't really know what I'm doing.

This is just a fun project for me to make my life and job easier.

swasheck

@JamesLupolt yeah. that's fine. i just love stuff like this. using statistics to analyze our data

James Lupolt

How are you doing it now?

swasheck

17:53

@JamesLupolt you could do a skewness test too to see if your larger values are occurring more recently ... this could work around the fact that you aren't getting an EMA

@JamesLupolt i'm not ... right now. i DONT do this on performance metrics right now. i do it more for tests on index efficacy and stats distribution. <must be friday>

ypercubeᵀᴹ

18:20

dba.stackexchange.com/questions/132437/…

I wonder how you added a comment after deleting the post. Oh, you are a mod ... ;)

For those who don't have enough rep:

> Not sure how I managed to post this twice – Paul White♦ 14 hours ago

Max Vernon

19:34

@MikeFal you need to put the function in master and run EXEC sys.sp_MS_marksystemobject 'dbo.function_name';

you won't need to write dynamic sql for it at all

and actually that probably only works for stored procs. mssqltips.com/sqlservertip/1612/…

Max Vernon

19:51

unless there is some secret naming convention that works for functions the way sp_ works for stored procs, then it definitely won't for a function.

Mike Fal

@MaxVernon Thanks, I will investigate that.

James Lupolt

@swasheck Thanks, I will look into that.

I haven't done anything with skewness or kurtosis in me whole life.

swasheck

@JamesLupolt skewness is a fun one ... i'm putting it into my stats skew analysis proc

@JamesLupolt for query performance you could get this information:

dbo.PerformanceAnalysisTraceData

James Lupolt

@swasheck Yes : ) I query that table often.

I just haven't set up ETL to pull it anywhere yet.

My coworkers keeping asking me about setting an OLAP cube on this data. I am not sure why. But I guess I will do that too at some point.

swasheck

@JamesLupolt nah. just columnstore everything. i mean, columnstore is a drop-in replacement for OLAP cubes and SSAS, right @mmarie?

ypercubeᵀᴹ

20:07

close-voters - this question has been updated substantially since the initial close vote. — Max Vernon 3 mins ago

A_V

20:30

hello

Anyone here went through oracle certifications ?

ypercubeᵀᴹ

@A_V I don't think so. But some may know things about them. You want to know something specific?

swasheck

it's me i was wondering if after all these years you'd like to meet

A_V

Well I'm doing a dba exam in particular and am looking for study material

oracle tests are annoying, they have those study books but many questions in the exam aren't covered by them

Tom V

@ypercubeᵀᴹ thanks for the edit

ypercubeᵀᴹ

Phil and @JackDouglas know stuff about Oracle. But you may have to wait.
It's Friday night in this part of the world.

Tom V

20:35

TBH I didn't expect much from that answer, I just posted it to help the guy before that question goes into the depths of solitude

ypercubeᵀᴹ

yep ;)

A_V

Aah alright, I'm not in a hurry. It's not friday night until 5pm though

ypercubeᵀᴹ

@TomV I was tempted to correct the "fields" to "columns"as well ;)

Tom V

@A_V it's 21.30 here

ypercubeᵀᴹ

@A_V "in some parts of the world it's already Saturday]

Tom V

20:38

@ypercubeᵀᴹ I meant the text fields in his editor

Duh ;)

A_V

What's wrong with ya'll, why would you want to live in europe

ypercubeᵀᴹ

yep ;-)100%

why not?

Max Vernon

@A_V cuz there ain't no Trump over there.

2

Tom V

@ypercubeᵀᴹ dba.stackexchange.com/posts/132633/revisions

ypercubeᵀᴹ

@TomV haha. nice. made my day!

Max Vernon

20:43

I hope you gave it a nice heartfelt burial. — Max Vernon 2 mins ago

Tom V

Don't add those things as images. Right click the table, select 'script as create' and post the generated code. — Tom V 11 secs ago

So much fail in one post

also

close-voters - this question has been updated substantially since the initial close vote. — Max Vernon 45 mins ago

Andriy M

44 mins ago, by ypercubeᵀᴹ

close-voters - this question has been updated substantially since the initial close vote. — Max Vernon 3 mins ago

Tom V

Ah missed that while scrolling up

Andriy M

@TomV No worries. The point is, The Heap™ are watching :)

Tom V

@AndriyM And then I go around complaining about other peoples fail :)

A_V

20:54

So basically the heap is a question edit club ?

ypercubeᵀᴹ

@A_V Not really.

swasheck

no. we make fun of bad questions and then sometimes people edit them

ypercubeᵀᴹ

You can scroll and read the discussions. Chat rooms are free to read

Andriy M

You might even learn why it's called a consultancy :)

A_V

I am illiterate, I wish to one day learn to read

swasheck

20:56

that's 90% of the SO user base

Tom V

@A_V but we make fun of each other too, we're not elitarian. If you hang around here often enough you will eventually be roasted too

A_V

I might, just started playing the DBA

Tom V

@A_V We noticed yesterday :)

A_V

actually studied programming, now I administer applications of which I know nothing.

Tom V

HA that didn't take long :)

Max Vernon

21:01

@A_V ohh that's genius material. :-)

Tom V

@A_V have your servers rebooted yet?

2

Max Vernon

oh man, this guy's having a hard time getting above +1 rep. dba.stackexchange.com/users/81571/sqlprofile87

Tom V

@MaxVernon LOL

> What does IT mean?
> scan vs logical read?

Max Vernon

@TomV yep. There's some good stuff out there today.

Tom V

You sorta see what he means when you read the question, but what a title is that?

Max Vernon

21:07

goodness me, is @PaulWhite actually sleeping?

2

swasheck

@TomV iLOLd

Max Vernon

@TomV agreed. I'm thinking it might be nice to include something to make this not a "shrug report".

Tom V

@MaxVernon Even he only has 5 nines uptime

3

Max Vernon

good one

Tom V

That sqlProfile87 guy reminds me of a customer "I have an error, what's wrong?"

Max Vernon

21:12

this is good for a lol... brickingitforcanada.com 176 million bricks already, in a country of 35.5 million. (re TRUMP of course)

@TomV yep.

swasheck

@TomV "i'm seeing a lot of operator headspace errors"

Tom V

@swasheck Did we run an ad on SO?

swasheck

must've

Tom V

So the most wanted terrorist of europe was found

a frkn block away from where he lived

theguardian.com/world/live/2016/mar/18/…

swasheck

hm

Tom V

21:23

Obama called our Prime Minister to tell him how great their work and cooperation with the french was (not shitting you)

Max Vernon

@TomV it's always nice to get a little pat on the head, just like a lap dog. /sarcasm

Tom V

You can remove that sarcasm tag :)

The last time they got one, the security services did such a great job and whatever, then they ask one of the agents, "yeah the neighbours called us", on the evening news

swasheck

that came across as a dislike of obama. i dont really care one way or the other. i'm pretty disappointed in all parties and presidents

Max Vernon

@swasheck all the good ones get shot.

Tom V

22:19

The great escape

There is really something wrong with that neighbourhood

James Lupolt

23:09

@swasheck This must be a joke I don't get

@TomV Wasn't he also living within sight of the local government's offices? Or was that one of his cohorts?

Transcript for

The Heap™ – Consultancy ©®