The Heap™ – Consultancy ©®

Evan Carroll

1:44 AM

youtube.com/watch?v=BgcJnurVFag Chris Travers ^

hot2use

5:40 AM

Morning all

Tom V

6:30 AM

Morning

hot2use

6:47 AM

Good or bad:

Fatal NI connect error 12547 (Oracle)

Apparently not too bad

Andy K

7:58 AM

morning

McNets

Morning

Paul White

8:23 AM

morning

Tom V

I'm only 1m76, I can't see the "above query". — Gerard H. Pille 49 secs ago

The sarcasm is strong with this guy

McNets

8:49 AM

@TomV true, 1.76 has been never enough for a query, barely for a subquery

hot2use

9:28 AM

Not disclosing his/her/its affiliation:

-2

A: Where I can get the best online visual SQL query builders?

If you are looking for a web based SQL Query builder, DBHawk from Datasparc.com allows to build SQL query online. DBHawk provides a complete seamless access to heterogeneous database platforms. Features of DBHawk: • 100% Browser Based single tool for all databases • Built-in Charts / Reports...

Well only passive in his/her/its profile

Tom V

@hot2use Yes, flagged it already

hot2use

same same

dezso

10:04 AM

did you all flag this one already?

McNets

yep

Tom V

10:41 AM

Oh I never considered the asker to be a sock puppet of the answerer, but with both posts deleted that makes sense

Paul White

Thanks for flags all.

Tom V

And the user is gone too

Can't leave a job half-finished eh

Paul White

@TomV And not just from Database Administrators :)

Tom V

11:03 AM

Paul must be having a great day

more spam incoming :D

Enjoying himself so much he edits spam before deleting it now :D

Paul White

Well I hope not every answer that links to one's own blog is spam else I'm in a lot of trouble :)

Terrible answer of course, no doubt it will be deleted.

Tom V

@PaulWhite I flagged before the edit

when it only recommended some software

> Try with sios datakeeper sanless software this will solve your issue.

That did look like spam to me

Paul White

@TomV Yep understood. Though of course not every "use this software" answer is spam either, by the SE definition anyway. Low quality, sure.

Funnily enough the other answer attracted a couple of spam flags that were retracted.

I presume that was the Charcoal project. I could check but busy.

angussidney

You talking about this one? dba.stackexchange.com/a/202512

(Charcoal admin here)

For the purposes of classification in our database of spam, would you call it spam or simply NAA?

Tom V

@angussidney No the other one from the same author, but both answers combined are simply a bad answer, neither spam or naa (imo)

Paul White

11:10 AM

@angussidney Yes (retracted flags). NAA for me.

angussidney

Cool, thanks. I've marked it as such.

Paul White

No great shakes either way tbh

@angussidney Say hi to Art and Undo :)

angussidney

Always good to get a second opinion from a site local, I don't like having to classify borderline cases like those ones

Paul White

Yep thanks for asking!

angussidney

Note that we've added the website mentioned in that post, so if it turns up again we'll be sure to let you know

Paul White

11:13 AM

Thanks

Mathematics

12:07 PM

After the Afternoon

Paul White

ten past an hour past Thursday

sp_BlitzErik

12:27 PM

BASICALLY DAWN

Max Vernon

12:48 PM

dba.stackexchange.com/users/136557/lucy is using a stock photo - not that there are any rules against that, just thought you'd like to know.

Paul White

tbh it's just nice not to see an anonymous avatar or identicon

Max Vernon

for sure

sp_BlitzErik

thatsamanbaby.gif

dezso

1:10 PM

@MaxVernon how did you notice that?

Max Vernon

@dezso I had the feeling their photo wasn't real. I have the TinEye plugin installed in Firefox which allows you to right-click any image, and "search on tineye"... that showed the results linked in my previous comment.

Paul White

Max Vernon

lol

this is going so well:

@MaxVernon Yeah there is no need for the parameters ,I took them out — Lucy 33 secs ago

dezso

@MaxVernon I thought you mistook the site for Tinder

Paul White

heh

Max Vernon

1:14 PM

@dezso oh god, I have no need for "relations" like that.

also, who is Jack Kelly?

"Fake Max Vernon"

dezso

@MaxVernon yeah, you have databases at your command ind the end

Max Vernon

@dezso lol

@PaulWhite I love how well that search worked. 25,270,000,000 results. That's a lot of results.

Paul White

@MaxVernon famousbirthdays.com/people/jack-kelly-instagramstar.html

Max Vernon

f*ck

if you want a laugh, check any of these: duckduckgo.com/…

Paul White

Max Vernon

1:20 PM

@PaulWhite lol

that's what I see when I do that search on my gravatar

I wanna know what that second guy is doing

Paul White

The magic of google

Max Vernon

google.com/…

Tom V

@MaxVernon Are you sure?

Max Vernon

@TomV good point. No.

Tom V

@MaxVernon Nice

Max Vernon

1:25 PM

@TomV I love how it thinks I'm literally beer.

Tom V

Lamak

well....it could be worse:

19

Max Vernon

@TomV well, it seems Google knows you!

@Lamak lol!

2

very nice

Tom V

@MaxVernon The picture is only used here

Who knows if it's really me (well except me obviously)

Paul White

@Lamak yikes

ypercubeᵀᴹ

1:30 PM

2

Q: Join N:M relationship

I have this N:M relationship: CREATE TABLE auth_user ( id integer NOT NULL, username character varying(150) NOT NULL ); CREATE TABLE auth_group ( id integer NOT NULL, name character varying(80) NOT NULL ); CREATE TABLE auth_user_groups ( id integer NOT NULL, user_id inte...

I don't see the point of the bounty.

Tom V

4

hmm

Paul White

> There is one working answer. But I guess there different (and feasible) ways to do this.

People can waste their rep how they want I guess

@TomV tbf I've never seen the two together

sp_BlitzErik

several people did at bits

Paul White

so you say

sp_BlitzErik

we even shook hands!

Tom V

1:32 PM

@PaulWhite I've seen Ola, and he sure didn't look like he's lifting weights

Paul White

I still trust google more than any of you lot

it correctly identified Joe as a triangle

Max Vernon

it seems Google has Paul down:

Paul White

ha ha ha ha ha

3

Magisch

heap ing amusement here

Max Vernon

welcome, oh charcoal one.

Joe Obbish

1:35 PM

@PaulWhite end avatar discrimination now

Magisch

Part of my dayjob is actually dba so I relate a little to here

Max Vernon

"how much do you make?" "I make Paul White Money™"

@Magisch part of our days jobs is dba also

Paul White

@JoeObbish says the triangle we don't listen to triangles

Lamak

@MaxVernon so, you are rich

Paul White

you all are so funny

Max Vernon

1:36 PM

@Lamak lol no, I only make MVCT Money™

ypercubeᵀᴹ

I get: Best guess for this image: visual effect lighting

Magisch

If only being DBA gave me dba pay too

3

Max Vernon

@ypercubeᵀᴹ I would have guessed tesseract

Paul White

@ypercubeᵀᴹ For Joe?

Joe Obbish

@PaulWhite you're a triangle

Paul White

1:37 PM

i see the gloves are off

@PaulWhite for mine

@ypercubeᵀᴹ Ah

this is a cool place

wait til evan gets in

next level

Max Vernon

Paul White

1:41 PM

hm

Max Vernon

and as a case-in-point for great branding:

"nailed it"

Paul White

not surprising I guess

shog needs to work on his branding

3

Jack Douglas

@PaulWhite all done, seems to work: dbfiddle.uk/…

Max Vernon

2:02 PM

someone here is trying to get this approved:

CREATE TABLE dbo.Table1
(
    ID int NOT NULL
    ...
    , Extra_Column1 varchar(50) NULL
    , Extra_Column2 varchar(50) NULL
    , Extra_Column3 varchar(50) NULL
    , Extra_Column4 varchar(50) NULL
    , Extra_Column5 varchar(50) NULL
);

like, why not just name every column "column1", "column2", etc

stupidity

a Data Architect approved it. Sad Trombone.

luckily enough it's nothing to do with me.

Joe Obbish

I've done things like that before

Max Vernon

it makes my brain weep

sp_BlitzErik

more than an eav lookup table?

;)

Max Vernon

you just know they're going to store dates in one of those darn columns... or better yet, a comma-separated-list

Joe Obbish

for example

sometimes sorts before inserts into tables don't get a large enough memory fraction

Max Vernon

2:06 PM

@sp_BlitzErik yah, it's not EAV. The rest of the table has about 20 normal column names.

They're saying they need columns for future use but they don't know what they are going to put in there.

sp_BlitzErik

i get that. i'm saying the alternative for stuff like that is often an eav lookup table.

Max Vernon

@sp_BlitzErik oh gotcha

I wonder if they've heard of ALTER TABLE

Joe Obbish

maybe they need to query the table 24/7

who has time for sch-M locks?

Paul White

@JackDouglas Thanks very much works perfectly dbfiddle.uk/…

hot2use

@Lamak They are mixing a mono-brow with a moustache

Lamak

2:28 PM

among other things

McNets

2:40 PM

@Lamak how is going in your new job?

Andy K

@Lamak hey man

Lamak

2:58 PM

@McNets it's been kind of great

@AndyK hey

Andy K

@Lamak how's your baby?

Lamak

@AndyK not born yet, but hopefully alright

McNets

@Lamak well, congrats then

Andy K

@Lamak cool

Lamak

@McNets thanks!

Andy K

3:13 PM

Shog is a bit scary

dezso

@AndyK what would you expect from a monochrome deity?

Max Vernon

4:15 PM

:43670280 WELCOME, O GREAT ONE.

Zane

Test

There we go. That's better.

Evan Carroll

4:26 PM

@MaxVernon I am back again, someone accidentally kicked me.

Max Vernon

@EvanCarroll I love your fortitude

Evan Carroll

Philosophically sound too, Hanlon's razor.

hot2use

5:03 PM

@gbn love it or hate it

Max Vernon

delicious

Evan Carroll

5:44 PM

Why do people insist on using english and not sql to describe problems.

=(

dba.stackexchange.com/q/132029/2639 rewrote that question entirely

Max Vernon

@EvanCarroll I agree, that is annoying. However, English usage like that helps search-engines point future visitors at the answer.

Master Database

6:04 PM

0

Q: Is Storing Json in Mariadb a better choice than using Mongodb for performance?

With performance I mean just Select query with millions of records and concurrent requests. Mariadb Json blog

discussion

Andriy M

@EvanCarroll His initial assumption that the column would be added along with the foreign key was a part of his problem that I think needed to be kept in the question so that it could be addressed in an answer. Perhaps you made his end goal clearer for the audience but I believe keeping the mistaken premise could be helpful for others.

For other beginners in particular

Evan Carroll

@MaxVernon I try to keep all the keywords in there, do you think I missed any that would otherwise be useful? A lot of if is humility-speak ("I am a newbie"), and such.

@AndriyM could you explain more?

Andriy M

Not sure. That says everything I think on the matter.

Evan Carroll

You feel that "that the column would be added along with the foreign key" was taken out? I think it's more explicit now?

Andriy M

Well, you've changed the wording so considerably that no one would say that he indeed thought the column would be added together with the foreign key. In his own words he said that explicitly.

Evan Carroll

6:14 PM

"How do I alter messages table such that, a new column called sender is added to it where sender is a foreign key referencing the users table"

vs (original) "How do I alter this table such that a new column called sender is added to it, which is a foreign key on the User table?"

Andriy M

"Does this statement not create the column as well?"

Evan Carroll

But that was simply bogus syntax, right?

I mean that gets to another question -- why do people write things that clearly don't work. I suppose the idea is to show effort, but should we keep that afterward?

Andriy M

Bogus syntax is useful as well. We often suggest that askers put their attempts so that we could correct them, so that they could learn from their mistakes. And, as Max has already said, it improves chances of the question's being found.

Because others might be trying the same path

and having the same mistakes.

Evan Carroll

Fair enough, I can include the bogus syntax back. I just think it's always more to read and there will always be an infinite amount of things that don't work that could have been tried.

Max Vernon

@EvanCarroll as one who tends to clean up a lot of questions, I try to focus on spelling errors, grammatical problems, and formatting. I try not to change the wording too drastically. IMO, removing this bit didn't need to happen:

> My web app is written in Python (Django), and usually I run migrations to synchronize the database. But in this case, I want to do it manually to understand the anatomy of migrating.

I'm not completely convinced it will help all that much to take it out.

not that it really is required for the question, just that it doesn't hurt the question.

Evan Carroll

6:19 PM

That really has no role what so ever, the problem wasn't python/django (and if it was it would be off topic here).

Andriy M

I think that was an important clarification, so that people wouldn't suggest doing what he wants to do using GUI

Evan Carroll

It's clear no one would suggest that, and no one did. If you want a gui, you need to ask specifically on the GUI.

Max Vernon

(waits for someone else to suggest using the GUI)

Evan Carroll

hahahahah. I think that may just be a cultural thing. In PostgreSQL tools must be explicitly qualified and without which we just assume you're not using anything special.

So we don't assume you're using something like SSMS, or the like.

Andriy M

If you want to add a foreign key but you are struggling to do it the way you are trying now, nothing prevents someone from suggesting an easy (in their view, perhaps) way out, unless the OP specifies they don't want that (as this OP did)

Evan Carroll

6:26 PM

@AndriyM but if you don't want that easy way out just don't mention you're using anything but the tools you want to use (the default), and no one will suggest it..

Max Vernon

I don't think there is really very much wrong with this revision, tbh.

Evan Carroll

If you hate SQL and don't like formatting...

Andriy M

@EvanCarroll I believe he did say just that in his own way

Evan Carroll

We'll see what he thinks of the revision when he gets back. =)

Max Vernon

@EvanCarroll I would put the CREATE TABLE statement etc, in my answer. If they provide a CREATE TABLE, and it's formatted terribly, then I likely would reformat it a bit.

Evan Carroll

6:29 PM

That's also a horrible idea.

Max Vernon

specifically, I might put the commas at the start of each line.

but that's not without it's anger and hate as well

Andriy M

Why "hate SQL and don't like formatting" all of a sudden? He's a beginner, for heaven's sake

Evan Carroll

I know, I'm not faulting him. I'm faulting you two.

We know better, we're experts.

He's not.

Andriy M

Oh come on, one relatively short line needs much formatting?

Max Vernon

@EvanCarroll how to win friends, and influence enemies, hey?

Evan Carroll

6:31 PM

heh.

With love!

Andriy M

So much so that you even need to clarify that :)

Evan Carroll

I think we're talking past each other. This site has a longer history of putting DDL in questions at this point. It stops us -those answering whose time is scarce- from having to replicate the work of parsing his English. It also keeps our answers shorter (as we operate off of a centralized, and official demo-DDL.

Without which we have things like the chosen answer recreating an example that has nothing to do with the questions example. Which isn't theoretical, it happens all the time. It happened on that very question.

So yes, I feel somewhat more strongly, about keeping the DDL of the question, in the question and not having every answer create, publish, and maintain their own versions.

Max Vernon

@EvanCarroll we're not attempting to write documentation here.

Evan Carroll

Right, we're attempting to answer the question better, and we're attempting to make sure we have the best answer. It's harder to tell whose answer is better for the people coming in if they all operate on different sample-ddl, and it's harder (more time consuming) for those of answering questions if we don't have DDL but have to create our own.

Andriy M

@EvanCarroll I have no problem with the added DDL. I did fail to acknowledge that, though, so I'm doing it now: it's a great idea. I just think you've left out a little too much of the original content.

Max Vernon

6:37 PM

@AndriyM agreed

Evan Carroll

Ah, I thought we were all addressing :43673403

Cade Roux

brentozar.com/archive/2018/03/…

So I have 1392 plans for queries.

Evan Carroll

@AndriyM So what of the content is it that you feel should be put back (the django thing), and help me understand why you feel that matters (keeping in mind how the culture of PostgreSQL is substantially different, the default assumption being no-gui SQL-interface).

Max Vernon

@EvanCarroll the point is, what hurt does it do having it in there?

Cade Roux

And it turns out one of the tables is a heap (I know). Is just indexing that table properly going to likely fix that problem?

I have an appropriate clustered index and non-clustered index already recommended to developers.

Max Vernon

6:40 PM

@CadeRoux for the same query? crazy.

Cade Roux

@MaxVernon no shit

SELECT *
FROM LookupValueTbl lf
LEFT JOIN [DutyLogComboFilterTbl] dlcf
ON lf.fkLookupSetID = dlcf.fkLookupSetID
WHERE dlcf.fkLookupSetID = --
AND dlcf.fkFormControlID = --
AND dlcf.fkDutyLogID = --
AND lf.FilterCode = dlcf.FilterCode
ORDER BY lf.SortOrder, lf.LookupText

Evan Carroll

@MaxVernon well, for one people looking for a solution on django (if there is one) may find this (which I don't want as it's not likely to help them), and for two it's needless background information which like the bogus answer can not be other enumerated, we could define an infinite amount of background information which is not useful in answer the question (especially when the author explicitly tells us to overlook it).

Max Vernon

@CadeRoux lovely

Cade Roux

Where the -- are all literals inline (SQL generated in code).

Evan Carroll

For instance, if he has access to a hex editor and doesn't want to use it -- hypothetically, would that be of value?

Because it's always an option.

Cade Roux

6:41 PM

Also the LEFT JOIN is obviously always turned into an INNER JOIN.

Max Vernon

@CadeRoux clearly a lot of possibilities for those params.

sounds like a nice opportunity for a stored proc

perhaps

Cade Roux

There are no indexes on the DutyLogComboFilterTbl - so it needs a CI and NCI.

Max Vernon

for sure, I'd start with making those corrections :-)

Cade Roux

I can also recommend they move that code into a sproc, too. But it still needs indexing. I was wondering if at least the indexing would get rid of the sp_Blitz-reported issues...

Joe Obbish

a heap "filter" table is a bit of a funny concept

although I suppose that I've been guilty of that in the past

Max Vernon

6:43 PM

@JoeObbish agreed

@EvanCarroll I think you should add all the infinite possibilities. It'll take up your time quite nicely. ;-)

ypercubeᵀᴹ

@CadeRoux is the query fixed or are the AND clauses produced by dynamic SQL?

Evan Carroll

ahhahahahahahahha

Cade Roux

@ypercubeᵀᴹ it's dynamic SQL produced in some C#

ypercubeᵀᴹ

@CadeRoux so it can be any subset of the 3 clauses/parameters?

Max Vernon

infinite subsets

Cade Roux

6:46 PM

@ypercubeᵀᴹ It seems like all of them are always passed. I'm waiting for the DBA to give me all 1392 so I can verify there is nothing weird.

Max Vernon

is an infinite subset always equal to the infinite set?

ypercubeᵀᴹ

@MaxVernon No

Max Vernon

@ypercubeᵀᴹ sometimes?

ypercubeᵀᴹ

Did you mean to ask if it is equally big?

Cade Roux

public static string LookupValueWithFilter(int set, string filter, string dutylog, string formcontrol)
{
StringBuilder ret = new StringBuilder("");

ret.AppendFormat("select * from LookupValueTbl lf left join dutylogcombofiltertbl dlcf on lf.fkLookupSetID = dlcf.fklookupsetid Where dlcf.fkLookupSetID = {0} and fkformcontrolid = {1} and fkdutylogid = {2} and lf.filtercode = dlcf.filtercode Order By SortOrder, LookupText", set, formcontrol, dutylog, filter);

return ret.ToString();
}

Spot that the filter parameter is never used...

Max Vernon

6:47 PM

@ypercubeᵀᴹ no, I'm just being silly :-)

ypercubeᵀᴹ

Because equal it would usually mean that it's the set itself.
A set is always a subset of itself.

Cade Roux

There is no {3}

ypercubeᵀᴹ

@MaxVernon But with infinite sets, some weird things can happen. Like a proper subset that is equally big as the whole.

Cade Roux

Russell's paradox

In the foundations of mathematics, Russell's paradox (also known as Russell's antinomy), discovered by Bertrand Russell in 1901, showed that some attempted formalizations of the naïve set theory created by Georg Cantor led to a contradiction. The same paradox had been discovered a year before by Ernst Zermelo but he did not publish the idea, which remained known only to David Hilbert, Edmund Husserl, and other members of the University of Göttingen. According to naive set theory, any definable collection is a set. Let R be the set of all sets that are not members of themselves. If R is not a member...

And that.

Max Vernon

Love Russell. That man was a genius.

ypercubeᵀᴹ

6:49 PM

I prefer Cantor to be honest!

Max Vernon

en.wikiquote.org/wiki/The_Impact_of_Science_on_Society

Andriy M

@EvanCarroll Putting it like that does make me review some of my earlier points. His mentioning the tools that are capable of doing what he's trying to do and immediately specifying that he doesn't want to use that does indeed look unnecessary. But his misconception regarding the the ADD FOREIGN KEY instruction does seem important. I've put it back.

Evan Carroll

7:07 PM

All good, I think there is merit in the bogus syntax too, based on how you assess the value to Google. If anyone happens to use the same bogus syntax, then this is certainly the right place for them. Anyway, it'll be interesting to see if/when he comes back if he picks my answer.

I read the bogus syntax as "I did effort", but it's also possible it's a legitimate attempt.

Andriy M

He even said explicitly that he wanted to do that manually despite having tools that could spare him his misery. Consciously or not but maybe that was why he added that sentence about Django

To make it clearer that he honestly was trying on his own

Of course, now that "we" removed that bit, more people might think, "Did he really did his best?"

Evan Carroll

That's doesn't actually matter, nor should it on this site.

No one has to "do their best" to ask a good question.

Nor is it especially relevant.

But yes, I do think that was his intention in what he wanted to communicate.

In other news, that guy has ask a totally awesome amount of questions on Stackoverflow

Seriously, wowzer. I thought I got that gold in asking questions.

453 questions!

/me jealous.

DBA.SE has only one person w/ a socratic badge.

dba.stackexchange.com/users/22336/…

gbn

7:30 PM

@hot2use quite partial too it

Tom V

9:09 PM

-1

Q: Best Events & Conferences for DBAs?

What are the top conferences and events that DBAs attend? What's most valuable about these events?

sql-server sql

Burn please

Joe Obbish

@TomV 3 different close reasons and counting

dezso

0

Q: Adding columns dynamically with values from another table - MySql

I have decided not to share my question. Thank you

mysql pivot

SQLPRODDBA

Hello experts

Can someone please help me with how do I replace single quote with two single quotes in SQL Server?

for example replace 'C:\Test' with ''C:\Test''. Please note that I need to replace it with two single quotes and not double quotes. I know its weird. I wasn't able to make it work using replace and need your help.

dezso

9:25 PM

@SQLPRODDBA this might help: dbfiddle.uk/…

SQLPRODDBA

@dezso: Thank you! That fixed it for me.

ypercubeᵀᴹ

@SQLPRODDBA why do you need this?

SQLPRODDBA

I have a script to monitor replication and today I encountered below error in my replication.

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)

I am getting this status using replication system procs and sending it as an HTML report via exec(@sql)

and since it contained single quotes my exec(@sql) encountered Msg 102, Level 15, State 1, Line 38
Incorrect syntax near '\'.

so I my query I wanted to replace single quotes with two single quotes so that it can execute it.

I replaced it and now it is working fine.

I was stupid, I couldn't figure out how to replace it. But thank you guys!

ypercubeᵀᴹ

I'm not sure that the best approach would be using replace. How are you building that @sql?

It might be safer to use quotename(errormessage, '''') instead of replace

SQLPRODDBA

9:41 PM

I am doing this.

SET @sql = 'msdb.dbo.sp_send_dbmail @profile_name =
''DBA'', @recipients = ''' + @Recipients + ''',
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''',
@body_format = ''HTML'' '
EXEC (@sql)

@Mailtext is a combination of my HTML header + actual replication data and HTML footer.

ypercubeᵀᴹ

Have a good read of these pages, if you haven't already: The Curse and Blessings of Dynamic SQL

3

SQLPRODDBA

my actual replication data had issues because of the details I gave above.

and because it had single quotes at execute(@sql) it was not forming the proper string.

Thanks @ypercubeᵀᴹ. I'll check the article.

Andriy M

10:20 PM

@ypercubeᵀᴹ QUOTENAME is good for names, or, more generally, for short strings, as it returns an nvarchar(128). If you want to replace characters in a script, you'll need to use something else. REPLACE is probably the only built-in option for that in T-SQL.

ypercubeᵀᴹ

10:39 PM

@AndriyM Right. I went and read the related parts in the link I advertised. Yes, quotename is limited and they advise to use replace (or create a function that does replace, for more clear code)

Transcript for

The Heap™ – Consultancy ©®