The Heap™ – Consultancy ©®

12:15 AM

@TypoCubeᵀᴹ Deleted. Note deletion requires a mod since merged posts are locked. We keep the stub around for a few hours so everyone can follow what's happened. Once it's reasonably certain everyone involved has a handle on the final state of things, the merged post can be deleted. Flag as Other... to get that done.

Evan Carroll

12:43 AM

I AM SAFE.

@PaulWhite this question is crap.. dba.stackexchange.com/questions/153981/…

you spent 500 rep on it

lol, I want 500 rep.

Paul White

@EvanCarroll It may be. I was just hoping to draw some attention to an under-appreciated database product. 300 reps despatched for that purpose.

It seemed to me like it might have an interesting answer.

I am often wrong.

Evan Carroll

I am all for that, we need to to assemble a guild of people with VTC-migration for PostgreSQL questions on SO.

We can call ourselves, [QGankerz]

Paul White

1:05 AM

@EvanCarroll Also: I'm always on the lookout for good unanswered questions that would benefit from a bounty outside my own area of expertise. Feel free to make the occasional suggestion.

Evan Carroll

I nominate all of these for a 500 point bounty dba.stackexchange.com/…

Paul White

That's very altruistic of you. I'll give it due consideration.

Done.

Evan Carroll

1:26 AM

Horray for consideration

the ether of life

Evan Carroll

1:41 AM

What do you think of mtime-row

to clean up this dead as hell horse.

See these questions a lot.

Paul White

1:55 AM

@EvanCarroll What the heck does mtime-row mean? If you're going to create a tag, at least add some usage guidance and maybe a wiki. Before you get 2k privileges, you might consider asking on Database Administrators Meta beforehand to get the community's view on a proposed new tag.

Evan Carroll

Yea, I'll add the meta for it now.

Paul White

Thanks.

Evan Carroll

On the FS, we call it mtime there is no name for it in the db but everyone wants it in the db, and it makes as much sense as anything else. en.wikipedia.org/wiki/MAC_times

added wiki info

if you want to approve it dba.stackexchange.com/tags/mtime-row/info

Paul White

2:13 AM

I've approved the tag info, but that doesn't mean I necessarily approve of the tag. Off to do some Christmas shopping. Don't forget the meta question.

MDCCL

2:23 AM

@AndriyM Thanks, I'll do some testing. I've noticed Rextester is becoming a common resource here in DBA.SE, and it works really well.

@TypoCubeᵀᴹ Do you happen to have a (digital or printed) copy of Codd's Further Normalization of the Data Base Relational Model, by any chance? (nice username, btw)

Master Database

2:39 AM

0

Q: Tag suggestion: mtime-row

A lot of questions involve the same thing PostgreSQL: Auto update updated_at with custom column Create a trigger on all the last_modified columns in PostgreSQL how to update column on same row with trigger without recursion? How to create trigger to update timestamp when record is updated or in...

TypoCubeᵀᴹ

8:21 AM

blue in the face, yeah!

hot2use

good morning

Paul White

@TypoCubeᵀᴹ Oh nice! Any idea how?

Philᵀᴹ

8:50 AM

Good $TIMEPERIOD. Shortest day of the year today

hot2use

9:04 AM

Tomorrow is the beginning of summer (well sort of).

Tom V

9:45 AM

Hmm my last edit was based on an already edited question, now I think it deviates too much from the original post

morning

Good morning

o/

@AndriyM Yes, best to be explicit about that

hot2use

@MasterDatabase Left some constructive feedback.

0

A: Tag suggestion: mtime-row

There are a number of tags in DBA.SE that I don't fully understand, but which are related either to one or the other RDBMS, as they are part of that specific RDBMS or are common DBA l33t 5p3@k. However, with your suggestion for the new tag mtime-row I guess the only person that would understand ...

Paul White

10:14 AM

@Philᵀᴹ Longest.

@TomV Leave a comment for the question author?

TypoCubeᵀᴹ

@PaulWhite No. I didn't log in the site last night, unless you count a failed attempt to use the app from my old phone. Woke up and the blue hat (or feet, socks, whatever it is) was there!

@MDCCL I don't think so.

@MDCCL Yes, it works fine. It doesn't have SQLite and it needs different delimiters for different DBMS. I miss the SQLfiddle where we could create the tables and scripts and reuse them (with some adjustments) in different systems

@PaulWhite Yeah. I didn't think it needed merge as the answer there was a duplicate as well. Blame me for not mentioning in the flag. I guess the mod (jcolebrand) saw that both qs had answers and decided to merge.

Paul White

10:47 AM

@TypoCubeᵀᴹ Cross-site duplicates are just such a pain, especially when both have answers. It's tough to coordinate perfectly sometimes, and other times things change while one part of the process is already off and running. Huge waste of people's time.

Philᵀᴹ

Cross-network dupe marking would be Terrigood

War

I recall having to solve that problem once ... its brutal

I bult an odata endpoint on both ends then did a ton of complex queries on them both and compared stuff

so much harder with user driven blocks of text for Q&A though

TypoCubeᵀᴹ

@Philᵀᴹ have you opted out of the Silly Hats?

Paul White

@War The worst cases should actually be pretty easy to spot. Almost always a low-rep user that simply copy/pastes the question text to multiple sites.

40

Q: Can we automate detection of cross-posting?

How difficult would it be to detect cross-posts when the relevant questions have identical titles and content, and are posted by the same user? As an example: Original, posted on SO Following day, migrated to SO from SF Also following day, migrated to SO from SU If this is technically possibl...

feature-request flags exact-duplicates cross-posting automation

I'd be in favour of an early flag for the Gun Monkeys™ to handle. So much easier when they're caught early.

I have no problem with people asking essentially the same question in multiple communities, so long as it is on-topic and customized for the audience.

Philᵀᴹ

11:07 AM

I'm only on about actually closing as dupe cross-network. That should be almost trivial

Paul White

@Philᵀᴹ I imagine the implementation to be a nightmare. It's fine on the surface, but now start to think about all the edge cases.

TypoCubeᵀᴹ

and considering that different sites run in different SQL Server installations / databases

Paul White

The SE experience with migrations ought to be enough to put them off ever doing anything cross-site again.

TypoCubeᵀᴹ

@PaulWhite except hats ;)

Paul White

@TypoCubeᵀᴹ Well that's just the same on multiple sites. Bit different.

TypoCubeᵀᴹ

11:11 AM

@PaulWhite yeah, it's not exactly the same. But there are hats you gain only with activity on multiple sites.

Paul White

Both involve multiple sites, I'll give you hat :)

@MDCCL Are you intending to answer this question or just clarify it?

War

11:28 AM

@PaulWhite interesting :)

Philᵀᴹ

@TypoCubeᵀᴹ Linked servers! Ducks

Paul White

🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆

TypoCubeᵀᴹ

@MDCCL check if it's in a library near you: worldcat.org/title/…

Seems the closest library that has this is in Stanford ...

but can be found for 10$ on amazon: amazon.com/Systems-Courant-Computer-Science-Symposium/dp/…

and is also available in some libraries in Germany: worldcat.org/title/…

(the amazon and Germany refer to the symposium book where the paper was published, with other ones)

Paul White

12:52 PM

2

A: PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0). CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL, column_that_we_will_drop TEXT ...

TypoCubeᵀᴹ

1:09 PM

@PaulWhite Yeah, I edited a bit.

Paul White

@TypoCubeᵀᴹ It seems strange that we sometimes have to force people to improve their answers that way. Good job though.

TypoCubeᵀᴹ

I read that "thanks for the report!" from Tom Lane in my mails this morning, it's enough acknowledgement that it's a bug.

Paul White

Absolutely. I will probably award the bounty early.

TypoCubeᵀᴹ

or their questions.

My fellow Greek needs help here but he still hasn't added the CREATE TABLE scripts.

Nor the actual plans. Only images, after more than 1 comments.

0

Q: Tuning join query using indexes

Recently i noticed that one of my queries that is running inside of an etl process is very slow running on SQL Server. This query is a select statement and it is filling a fact table. I decided to take it and make the necessary tests and i found that the problem is in the select statement. This s...

sql-server query-performance optimization

Paul White

@TypoCubeᵀᴹ More than 1 comments eh.

TypoCubeᵀᴹ

1:29 PM

@billinkc in Japan ;)

Why is KFC a Christmas tradition in Japan? http://boingboing.net/2016/12/20/why-is-kfc-a-christmas-traditi.html

Tweeted by codinghorror on December 21, 2016 at 4:35 AM

TypoCubeᵀᴹ

1:40 PM

Square it!

Tom V

@TypoCubeᵀᴹ He's going to write a book explaining the problem instead of just adding the plans

@StavrosKoureas I don't think you need to install the tool on the server, you can probably load a .sqlplan file in it (save as xml) and anonymize that — Tom V 34 secs ago

right?

Paul White

@TomV Yes.

Any valid sqlplan xml can be pasted directly into PE.

Tom V

I never used the anonymize feature

Our schema is public anyway :)

Paul White

Anonymized plans are much harder to analyze. Lots of important stuff that isn't typically 'sensitive' is lost. Still better than part of an image.

I lose patience quickly though. If you need a plan analyzed under NDA, hire a consultant.

TypoCubeᵀᴹ

Can it anonymize DDL as well (and consistently with the plan anonymization)?

Paul White

1:55 PM

@TypoCubeᵀᴹ It works pretty well. I doubt it's perfect in that regard, but something > nothing.

TypoCubeᵀᴹ

I wonder if they have plans to make SQL Sentry work in Linux.

Paul White

That Square It! game is quite absorbing.

TypoCubeᵀᴹ

Otherwise it would be fun having to use Windows just to run SQL Sentry to check execution plans for a Linux SQL Server install

Paul White

I don't know for sure. But if .NET works on Linux, it should have a good chance.

Philᵀᴹ

Did we do this yet?

🎄
He's making a database
He's sorting it twice
SELECT * from contacts WHERE behaviour = 'nice'
SQL Clause is coming to town
🎄

5

Tom V

1:59 PM

Might run under mono or wine

TypoCubeᵀᴹ

that snapchat hat looks a bit like, ...

and I wonder why I got it. Don't have accepted answers lately, except one which was posted 2 hours after the question

Paul White

Making progress, we now have an image of the whole plan. A bitmap appears.

13 revisions, 14 comments, still no SQL Server version.

I've never been keen on people asking for reasons for downvotes, but I'd sure love to know why someone upvoted that.

I'm going to make myself a cup of coffee and perhaps have a biscuit.

TypoCubeᵀᴹ

2:21 PM

@PaulWhite 32 views. We'll have to find the other 29.

Philᵀᴹ

2:40 PM

I often wonder what obfuscated table/column names could be in such questions. tblTheNuclearLaunchCodeIs07583411 anyone?

TypoCubeᵀᴹ

It's probably something dull, like Sales_Orders_BMW_2016

Paul White

The xml probably contains 11 herbs and spices, in particular quantities.

TypoCubeᵀᴹ

5

Q: Why was the snaphat not awarded? Or why was it awarded incorrectly?

There have been a few cases in which the snaphat was not awarded. There have also been other cases in which it was awarded incorrectly, on old questions. What gives?

bug status-completed winterbash-2016

> I've now corrected the implementation to be as expected. We won't remove the hats we've given out, and we will award due hats retroactively.

yey! 1 extra, bug hat

bluefeet

3:04 PM

maybe there is a hat of a bug?

Tim Stone

Wait, who has lice?

TypoCubeᵀᴹ

@bluefeet why is the Philanthropist given if you award the bounty at the last day of hats? And not at the last day of the bounty period?

It doesn't worth much if you have the hat for only a day or less. Since we can't keep the hats after Jan 8, we go back to the "hate fun" status.

bluefeet

@TypoCubeᵀᴹ Honestly, to make you work for the hats over the entire period. Instead of getting them all in the first 2 days.

TypoCubeᵀᴹ

@bluefeet that also means that we should NOT start bounties before Jan 1, if we want that hat.

And don't take too much from my questions, I'm feeling grinchy. Could be the hat I'm wearing.

@bluefeet, honestly though, haven't you fed up with having to discuss about hats the last 3-4 days?

I would.

yesterday, by bluefeet

I cannot confirm or deny anything

bluefeet

exactly

it comes with the territory of being in charge of hats

A_V

3:17 PM

Hello hello

got a lil question about a big question

13

Q: How to clone a user in SQL Server 2008 R2?

Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?

sql-server sql-server-2008 ssms permissions

Is there a better way to clone a user in SQLSERVER than this ? If this said user has access mappings for dozens of databases, then the answers in the question above aren't fast

Forrest

dbatools.io

might work

A_V

3:43 PM

yeah that's actually something insanely useful-looking

but the commands don't quite suit my need, that's really a migration-oriented tool

whereas I just to create a windows login with similar database user mappings as another one

without having to manually crosscheck all the things in the table by hand

To copy only two SQL logins, netnerds and realcajun, as well as one Windows login ‘base\ctrlb’

Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins netnerds, realcajun, 'base\ctrlb'

Would that command work for naming a user under a different name and also targeting the same source database

Forrest

It looks like Export-SqlLogin will create a t-sql script. You can then replace names in there or use that as a basis for your own script.

TypoCubeᵀᴹ

Another secret hat: The Hatter

this seems to be utterly nuts to get (so appropriate for the name!)

Tom V

4:03 PM

@PaulWhite Re-edited and now seems to even have attracted upvotes so I'm fine with it

War

to save putting conn strings in config files i'm consider running all apps in IIS under a windows account and that windows account would be given SQL permissions to manage a given db that the app sits on ...
Do you guys think this is a good idea?

jcolebrand

@War Why not use the .NET heirarchy and put it in the machine.config?

MDCCL

5:10 PM

@PaulWhite Both, actually. I wanna share my take on the scenario, and I started putting an answer toghether yesterday, but realized that some points required clarification.

Andy K

bye folks

MDCCL

@TypoCubeᵀᴹ Thanks for the libraries link, it's very interesting. Yes, I think I'll eventually buy one of the Data base systems: Courant Computer Science Symposium 6 copies for sale at Amazon.

TypoCubeᵀᴹ

@MDCCL is that paper so important?

MDCCL

@TypoCubeᵀᴹ It's not indispensable, but I've seen several references with slightly different wordings of the normal forms (1NF, 2NF and 3NF) all over the web, and I tend to be kinda picky with those things. I wanna have the real Codd's definition, it's mainly a personal liking

TypoCubeᵀᴹ

5:44 PM

Something I learned today: Command line XKCD