The Heap™ – Consultancy ©® - 2013-02-05 (page 1 of 2)

I'm so going to get bollocked for my latest edit :D

Q: Sql Server Database Mail performances

12:48 AM

I need to send millions of e-mail per hour for, uh, not spam

0

Sql Server Database Mail provides nice built-in features like logging. I would like to use it to sent large amounts of email (millions+) really fast (several millions / hour). Is Database Mail designed for this kinf of usage and will I reach the performance i need in production with this soluti...

sql-server mailing database-mail

njk

@AaronBertrand I commented.

2:16 AM

you guys are so witty

8:26 AM

This question seems more appropriate at Database Administrators. Voting to migrate. — Andriy M 7 hours ago

Any notes/objections?

9:02 AM

@AndriyM I've added my vote

@tomh Hi, I've asked the team to link that question to your account and I'll let you know how they respond :)

gbn

10:12 AM

Morning all

@AndriyM Voted too

@JackDouglas @gbn Thanks

12:15 PM

@AaronBertrand dbareactions.tumblr.com/post/42138803469/…

12:27 PM

@MichaelFredrickson @njk SELECT is not required (by the standard). Postgres has implemeted that (useful?) part and allows TABLE a; as an alternative to SELECT * FROM a;

Fiddle

Q: View SQL Server list of declared variables in SQL batch

1:17 PM

What could be a sensible usage scenario for this?

1

Does anyone know if there is a way to view the list of variables that have been declared in a SQL batch? It would be something like this: declare @Variable1 datetime2 select * from [List Of Declared Variables Table] I realize it would be something I would only have access to in the current SQ...

sql-server variables

@AndriyM can't really think of one. Maybe he is writing a big sp and keeps using the same variables?

@Lamak Yes, something to do with really big scripts – that's the only thing I can think of. I still don't really see in what way this could be useful/used.

Q: How to make a formal complaint about a specific insulting comment

me neither

Seems like the "Point!" guy got suspended for his comments

10

I've just been very insulted by a comment in a question and it's not enough to be able to flag it for moderator attention as 'rude'. I want to make a formal complain about a user and their comment and know that someone has actually done something about it. Just in case it gets deleted (spoiler t...

support

1:37 PM

@Lamak This account is temporarily suspended to cool down. The suspension period ends on Feb 5 '14 at 12:05.

That's tough

@dezso That guy has some serious anger issues

@Lamak but it's still one year

There were a lot of comments like that. Remember the you do not get it. Point! comment with @AaronBertrand?

and some encounters with me also, and a lot of users

@Lamak of course

practically every comment that guy posted was insulting

1:42 PM

just haven't seen this long suspension so far

He was seduced by the dark side.

1:56 PM

Interestingly, English is not his native language

At least that's what he told me once himself

@AndriyM It shows, actually

the: you do not get it. Point!, was clearly meant to be you do not get it. Period

@Lamak or full stop

So, he may have struggled with learning the language and he's now got no compassion for those who are struggling at the moment

In spanish, both point and period translate to the same word: punto

@AndriyM Maybe, though he was rude. This comment doesn't seem like a problem with english:

@ClaraOnager I am sure your english and logic teacher would revoke your school gruaduation for your failure to recognize an answer put into the form of a rethorical question. It seems "mastering langauge" was not on your course? The answer I gave is that I think that it is not available under professional. THanks for showing the downfall of culture in your country

@Lamak No, my point was that he may now be more or less fluent, at the very least confident, in expressing his ideas in English, but he doesn't care for others who are still not.

2:01 PM

TomTom strikes again

@AndriyM oh, I misunderstood you

yikes for a while

@JNK he got a one year suspension

super

He's a prick and gives database folks a bad name

He's not suspended from dba though :)

2:03 PM

yeah I know

He's not very active here

@JackDouglas really?, I thought suspension was for all sites

@JNK You mean, he was not very active

He may now be

dba.stackexchange.com/users/2142/tomtom?tab=activity

@AndriyM that's what I was thinking

well we will see

Most of his ire seems to be .NET based

@JackDouglas yikes, his comments on DBA aren't much better

You should RTFM. Seriously. You really expect us to handhold you on infividual items in an installer selection? Maybe you should - i don't know - get a job that requires no reading? Btw., why you even THINK that b would install all the other points is really something I dont understand. Never seen a tree selection? — TomTom Dec 9 '12 at 15:24

2:07 PM

It appears he's got more rep on SU than on DBA. He might still decide to pursue the growth there first.

@Lamak Flag it and I'll delete it

@JNK done

Deleted

nice

Hah, I was wondering why my attempts to flag it were failing :)

There's no shortage of borderline offensive comments and answers from this chap on dba.se, shall we flag flag and flag some more?

2:18 PM

@MarkStorey-Smith nice pastime :)

gbn

@dezso me neither. One year is long

@Lamak yeah, I've noticed his abrasiveness

@gbn I think he was angry.......with everything

@Lamak Wasn't my impression. His overall tone strikes me as non-negotiable. Although I preferred to attribute that to him speaking from experience.

Until recently

MySQL 5.6 is now GA. dev.mysql.com/tech-resources/articles/mysql-5.6.html

About bloody time! dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

2:40 PM

@gbn it must be his third suspension for something serious

gbn

@JackDouglas One year is the same as banned. And i didn't know 3rd time = one year.

Some folk don't learn

should I tell them to post a new question?

I have another table where i have this slab for commerical/domestic consumers for the number of units consumed. For e.g 50 units * 4 for a domestic consumer and *10 for a commerical consumer. How do i multiply those values to the resulting reading of the above query. — user2018756 35 mins ago

It seems however that he's already "serving" one year on another site.

they already accepted and now they want more help but did not include that in the OP

Yep new Q

2:46 PM

@user2018756: If that's something that you can't apply this answer to, you should probably consider asking a new question. We tend to keep things simple on this site: one problem, one question. — Andriy M 1 min ago

is that all right?

I think so

hi @AaronBertrand

Hi folks

People keep calling me Lamark. Is that a common name?

@Phil Gamblers Anonymous?

Thanks Lamark, I see my error now. Maybe pivot is not the right solution for my problem in this case? Could you suggest a method to achieve the desired results? — cathalobrien 15 mins ago

2:49 PM

Wow a year suspension. The guy's a douche but even I think that's harsh

@Lamak people might loosely associate it with French for "the mark"?

@Lamak or you just participate in questions where a lot of people are terrible at reading and writing? :-)

@AaronBertrand The Mark I like it

@AaronBertrand The durations are automatic

@AaronBertrand that's also true

and they keep increasing with the offense

So I'm guessing it's his third or fourth suspension

Got it. I was suspended for two days once, but I e-mailed team@ to plead my case and it was lifted before the 2 days were up.

2:51 PM

Yeah I think the normal first offense is like a couple of days, then a week, then 30 days

I havent given any longer than that

You can still put a custom value

@Lamak confusing it with Lamarck?

@ypercube That seems a plausible one!

@ypercube that may be

@AaronBertrand Oh my. What for?

@Lamak It's also a not uncommon first name in the US, according to OurBabyNamer

2:59 PM

@ypercube who would've guessed

@dezso was testing out my theories about a sock puppeter without realizing how good the site is at catching those automatically

@Lamak @JackDouglas That guy again? I had gone through and built a list of probably 10+ comments or answers they gave that were just over the top in asshattery

@dezso wanted to see if it was possible to create a second account and upvote every question from my first account. I planted this question and answered it.

Morning

A: Select column values as columns using PIVOT

@Lamak What do you think?

0

Your requirements are not totally clear but it looks like you are trying to create a new column named c with then a row_number() associated with it -- c1, c2 c3, etc. If you were to use the following in your subquery: SELECT Val1, Val2, 'C'+ cast(row_number() over(partition by Val2 ...

I think that is what they wanted

3:08 PM

@AaronBertrand I think I remember this event

@dezso then voted on my own answer. It was captured pretty quickly, and I was suspended for voting irregularities. Tough price to pay, but I got my answer: no, it is not trivial to game the system. I explained that and that I wasn't out to benefit from rep (forget my rep at the time but an ill-gotten +10 was not exactly a big deal), and they lifted the suspension.

@bluefeet Not completely. I was actually gonna try to answer it, but then I look at the results and they are not ordered by Val1, it's the old "natural order of the table" and, as the lazy person that I am, didn't want to comment on it

My eyes hurt from all those Nulls.

@Lamak Oh well, I am leaving it. :)

@bluefeet Look at the results he wants, the first row has S07 for c10

@bluefeet If you add a comment about this on your answer I think it would be better (seeing that you took the time to already answer) :-)

and got my upvote

3:15 PM

@Lamak I posted another query including a note about the ordering issue

@bluefeet Yeap, saw it (you already got my upvote)

Q: SQL Server 2012 installation failure An error occurred for a dependency of the feature causing the setup process for the feature to fail

abandoned question alert

1

I am installing SQL Server 2012 Developer (from ISO image written on DVD), my machine has Windows 7 (Edit: Windows 7 with service pack 1) and it already has SQL Express and SQL 2008 R2 Express installed in it. On running 2012 setup it goes fine from Setup Support Rules to Installation Progress. ...

sql-server sql-server-2012 installation

VtC?

I can't recall what the policy is on those now

It may just be delete

no VTC reason is really accurate

3:30 PM

@JNK how about suggesting one on a meta?

@JNK too localized perhaps. The guy is gone, if he hasn't fixed his problem by now...

@JNK it also could be a dupe of the question suggested in the comment (though I'll confess I didn't inspect and compare the log files to see if the problem is the same)

@Lamak are you stalking me?

@swasheck don't think so

@swasheck are you feeling stalked?

@Lamak just that my profile has been viewed on another social network and the source was chile :)

3:41 PM

i dont really care. i'm not worth stalking. just kinda funny

@swasheck really?, not sure if that was me...mostly because I always use a US server as a proxy

@Lamak that's fine. i really don't care. it was just something to say. the analytics are too rough to be able to narrow down which city the hit was from. i know of a few other people in chile so maybe it was one of them. if it was you i was just going to say that i'm sorry my profile was so boring

@swasheck now I'm really having an urge to actually stalk you

@Lamak you'd probably find youtube videos of kittens to be more interesting

@swasheck ok, you convinced me to not stalk you :-)

3:49 PM

hooray for sp_help_revlogin

Today I learned about sp_who. Are all the sql server procedures named sp_<something>?

@CBredlow sp_who2 is better (on my opinion)

@Lamak Well, it was a quick check, didn't know about who2

Mark Storey-Smith

@Lamak With sp_whoisactive a further refinement

@MarkStorey-Smith right

Mark Storey-Smith

3:54 PM

@CBredlow System procs are sp_xxx. User procs shouldn't be.

@CBredlow @AaronBertrand notes that sp means "Special." I think I recall that.

okay, are they like up_xxx then? because I'm assuming the sp stands for System Procedure

@CBredlow you can name your sp's the way you want

but, you should avoid using the sp prefix on them

okay

@CBredlow sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/…

Q: Still wrong to start the name of a user stored procedure with sp_?

3:56 PM

@CBredlow see here

16

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I could provide my co-worker with an explanation. ...

sql-server-2008-r2 stored-procedures

@Lamak there was another one somewhere along the way

@MarkStorey-Smith sp_whoisactive is a promethean gift

Right or wrong place to ask SQL questions?

Pertaining to writing queries

6

select p.ProdID
from cds.dbo.cds_prod as p
inner join cds.dbo.cds_atr as a on a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
where p.ProdID = 'S10812564'
group by p.ProdID

I have that, and it gives me back one product, which is great

Mark Storey-Smith

@Sean If its a one liner, try your luck. If its an essay, dba.se is quite good for them

4:00 PM

select SUM(qty) as sumQty, p.ProdID, p.CatID
from quoteitem
inner join cds.dbo.cds_prod as p on p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
inner join cds.dbo.cds_atr as a on a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
where ordered = 1
and sageSOPOrderReturnLineID is not null
and LEN(LTRIM(RTRIM(cnetprodid))) > 0
and cnetprodid LIKE 'S%'
and price > 10
and created > DATEADD(MONTH, -3, GETDATE())
group by p.ProdID, p.CatID
HAVING SUM(qty) > 10
order by SUM(qty) desc

now, I get 813 rows back with the inner join cds.dbo.cds_atr as a on a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID line in

even though I'm grouping it

and 95 without

do not understand

You have the same product in multiple categories, maybe?

maybe, maybe not, but it still doesn't explain why grouping by product ID would give me more rows

you're collating to a case insensitive ... i dont know what affect this has on number data types (please tell me that cnetprodid is a numeric data type) ... but if it's text then it's going to match capital, uppercase, lowercase, camel case

@Sean more rows than on your first query?

@CBredlow Snag a copy of sp_whoisactive and you can forget about sp_who and sp_who2

4:02 PM

@Sean of course it can

@swasheck you love camel case

Q: OLE DB Command in SSIS holds Exclusive lock on table indefinitely

I love the smell of people not comprehending transactions in the morning

0

I'm implementing an upsert operation using SSIS The general package structure is <Begin transaction> <read config values> <Data flow task> <commit transaction> Data flow task is comprised of <oledb Source> <Lookup> <matched output to Update using ...

ssis locking

@Lamak mmm camel hair

And my reaction dbareactions.tumblr.com/post/42119130061/…

@Sean i'd say that you should open this as a question on the site. please give the structure of the table(s) in question (also, the default collation of the db)

@billinkc I'll keep that in mind

4:04 PM

@Sean Correct me if I'm wrong, but aren't you filtering to one product on your first query?

where p.ProdID = 'S10812564'

@Lamak Yes, to ensure that for one product I get one row with my grouping

Why its surprising that on your second query you have more products?, you are not doing that filter

@Lamak My problem is that I join the table and group by a unique column and it still drags x million rows back when I only want the one per product, regardless of how I group the tables

@Lamak it's 95 vs 813, not 1 vs any

^

4:06 PM

@Sean also, after looking at it ... your prodid is not numeric. do you have two separate products in quoteitem that look like S10812564 and s10812564

@billinkc agreed.

@swasheck I doubt it. Would that matter given that the collation on one DB is SQL_Latin1_General_CP1_CI_AS and the other is Latin1_General_CI_AS?

They're both case insensitive, I dunno the nitty gritty though

@Sean, is ProdID the primary key of cds.dbo.cds_atr?

@Lamak One of them, yes

@Sean ok. so they're both CI ...

4:08 PM

It's a clustered index on 3 columns

prodid, atrid and valid

@Sean then your join is multiplying the results

@billinkc pretty sure the OP doesn't need begin/end transaction on his package.

@Lamak But in the first query I posted, I'm joining on the same columns as in the second query, and the group by clause sorts that out for me

In the second, it doesn't

I have no idea why, as they're joining on the same thing and I'm grouping by the same thing

@Sean that doesn't mean that the first join was correct

@Sean you are using HAVING SUM()>10

and after you multiply the results, more rows can meet that criteria

But the multiplication shouldn't happen

Because I group on ProdID

in the first query, this works

4:11 PM

@Sean it doesn't works

at least, not correctly

it bloody well does

in what way?

I will never get why people store comma separated lists and then wonder why it is hard to query against

0

Q: MYSQL Conditions

i have a mysql table category with a field events as varchar which its values is like this : 11,29,32.and this values referer to event table ID. ( so i can say : i have a several event ID from event table in category table) So i want to select events from category like that : SELECT * FROM...

mysql

@Sean look, your first query is multiplying the results, but you are filtering to one producto, so if you group by that product, then it will work

it's correctly grouping my results by prodID so I only get one row per unique ProdID, this is the desired behaviour. The second query doesn't behave like this when it should

but if you add a SUM over there, it will be wrong

4:12 PM

So it's down to aggregates

Fair enough, temp table it is

@Sean of course

@Sean this doesn't solve anything

@bluefeet Comma separated lists seem to be the new fad. I think it stems from this noSQL hype.

@Lamak if I sum all the prodIDs that match the criteria into a temp table then join on that making sure there is only one row per product, it'll be fine.

I still don't understand why query 2 multiplies results when I'm doing the exact same thing in query 1

@Sean ok, fine

@Sean it's not the exact same thing

but it is =]

4:15 PM

@Sean come on, I've alreadfy showed you that it isn't

give me a moment to prepare a fiddle for doing a demo

@Sean at this point i'd suggest posting a question, if only so that @Lamak can get a bit of credit for his assistance. Also, this has gone from one-liner to full-on QA material.

@Lamak No you've told me why I get a different number of rows, not why it's multiplying results in what is essentially the same query

@Sean it's not essentially the same query

@Sean we're primarily a group of like-minded folks who hang out in here and talk some DBA and other trash. if you're going to come in and ask for support and then get frustrated with the responses, i would strongly urge you to post a question on the site.

@Sean did you try removing the HAVING on your second query and comparing the rows you get with your join and without it?

4:18 PM

@swasheck Righto.

@Sean thanks :) by the way, you're always welcome to come in here and laugh with us at the latest dbareactions.tumblr.com posts ;)

@Lamak Oh that's just broken. Goddamnit.

@swasheck No offence but I'd rather not

I'm no DBA, just have to work with databases

SQL really gets on my nerves

@Sean none taken. we're happy to help when needed.

@Sean Not everyone can handle a firehose of awesome

@Sean then go for devopsreactions.tumblr.com

4:20 PM

That's why they created javascript

@JNK oh stop it

And the blatant condescension. Always loving that

I actually don't care about JS at all

@Sean I'm actually being facetious

@JNK A firehose of awsome is a new one to me.

please don't take it personally

4:22 PM

@JNK I'll be sure to mention that at the next noSQL meeting I'm forced to go to....

If you get offended I'll be forced to hug you

@Zane spread the word!

@JNK a fate worse than death.

@Zane its annoying :)

@Zane you changed your gravatar

@Sean what do you mean?

@bluefeet I finally got a gravatar lol

4:28 PM

@Lamak I'm sure there's some freaky SQL reason for it but I don't understand why HAVING breaks my query

I was going to put a picture of my hideous face here but I don't exactly have access to many photo's of myself at work.

Posting a question anyway

@Sean It's not the HAVING, is the join that's not being made to the whole primary key

I can't join to the whole primary key from that table

So for now I will be mega man

4:29 PM

@Sean then you shouldn't be joining to it

Nice, I just got a response email from the German company about knowing German as a prerequisite. Apparently it's not.

@Lamak That's not helpful

@Sean why?, I'm trying to explain your issue

Yeah but it's not the explanation I'm interested in, it's getting managers off my back

If you can't use the whole primary key on the join, then you should use the other columns as filter at least. Otherwise you are multiplying your results

@Sean but it is the correct explanation

4:30 PM

Doesn't stop it not being helpful =P

@Sean ok

sigh @Lamak ... just stop ... sometimes management doesn't want "correct"

@Lamak And that's what I was trying to explain, the ProdID is part of the primary key, but I'm only joining on ProdID, so I can only have results that contain that prodID. If I then group by the ProdID, I should no longer have duplicate rows

@MikeFal your query plans look funny. What did you use to generate them mikefal.net/2013/02/05/where-to-join

@swasheck I will

4:32 PM

I'm posting a question anyway

thanks @Lamak

> Notice how both queries have exactly the same plan. This is because our friend, the Optimizer, understands the two approaches and will build the plan accordingly. Want to play with it yourself? You can check out the full example over at SQL Fiddle.

@Sean I understood you, but since you are multiplying the results with the join, the sum is wrong, so more products meet that criteria with your join that without it

too much markdown make chat.se go BOOM!

@Lamak Yeah I get that now, but I don't understand why putting a having clause in there breaks things so badly... Writing up my question now anyway

@CBredlow you see? I've told you (sort of) :)

@CBredlow which city (if public)?

4:44 PM

How do you lot expect table structures formatting?

@Sean I prefer a CREATE TABLE

@dezso Does it matter if I remove things like primary key constraints and foreign keys?

@Sean Do it in a fiddle, it would probably be easiest

Have you seen SQL Fiddle?

No, I haven't

sqlfiddle.com

very handy

4:47 PM

@Sean leaving FKs may be OK if not involved in the query

PKs are a must (as I see)

You can put your DDL in there and some sample data, then post the link

@Sean fiddle is a fantastic website.

and other folks can make forks and whatnot and save links to those

I don't think I want an answer any more....

@JNK fork that

4:48 PM

But the nice part is it runs various versions of SQL in the background

and will actually run it and give results

@Sean you don't have to do the fiddle, it's just easier

I am in heaven. I have never seen so many pivot questions in one day.

@bluefeet I never got a handle on pivoit

@JNK Easier for who? ;P

@dezso It's in Berlin, so glad that careers site exists, wouldn't have found that place as easily with my earlier resources

wanted to star it

4:49 PM

@Sean Everyone. You can copy and paste your create table code directly and it will run

@dezso mine?

@swasheck yup

............................................________
....................................,.-'"...................``~.,
.............................,.-"..................................."-.,
.........................,/...............................................":,
.....................,?......................................................,
.................../...........................................................,}
................./......................................................,:`^`..}

2

thx

@dezso indeed

4:50 PM

@Sean what happened?

@dezso What?

Mike Fal

@billinkc I used screenshots from SQL Fiddle because I am lazy.

@Sean why don't you want an answer?

@dezso Cos I'm properly sick of this SQL crap =P And it's almost time to go home and forget I work here

@MikeFal I think you have a typo at 'comparability '

Mike Fal

4:52 PM

@dezso Thanks, I'll fix that. That's the problem when you're both the writer AND the editor. :)

See half of the stuff in these tables are just extraneous rubbish that doesn't relate in any way to the question.. And sample data... Urgh.. I don't even understand what's in these tables any more

@Sean Who designed the database? Maybe they have some E-R diagrams, or some sort of documentation about the database

@CBredlow AHAHAHAHAHAHAHAHAHAHAHA NOT A CHANCE.

@MikeFal called out by a non-native English speaker. actually, that's probably the best sort of editor to have.

@CBredlow It's a DB populated from a CNET feed. My boss designed it. He also designed the various 3000 line if statements our internal portal runs on. Classic ASP and SQL 2000 ftw.

@CBredlow The documentation is inside his cranium. Oh how I would love to get it out of there forcefully....

3

4:55 PM

@swasheck I'm an official hairsplitter champion, you know

@Sean time for a rewrite?

i mean ... extended support is g - o - n - e as of Apr 8th on 2000

@swasheck time for a career change =]

@swasheck That might make it easier, just say "this database isn't efficient, we might need to redo it"

Mike Fal

@swasheck Dear sir: I am both an IT geek as a profession with a music degree for my college work. I make NO claims to the accuracy of my grammar and/or spelling.

@JNK Since you are mod I have a logistics question for you. When I post my pivot answers. Typically I post multiple versions of the same query in the same answer. Should I continue to do that or should I post the alternate versions as separate answers?

4:58 PM

@bluefeet Unless its a CW or some other weird question I'm normally against multiple answers

I would just put alternatives in the same post