The Heap™ – Consultancy ©® - 2015-08-13 (page 2 of 2)

swasheck

8:05 PM

not bad for a pentagon-funded operation

James Lupolt

They are?

swasheck

some military branch has invested in Pure

James Lupolt

Oh. Looks like it's In-Tel-Q, the CIA's venture capital firm.

swasheck

ah. there you go. nice googling. or alphabet.googling

Paul White

@MDCCL And there were plenty of others that did not self-nominate (as is required).

swasheck

8:12 PM

the fools

MDCCL

@PaulWhite Yes, I know there are many talented persons that are part of this community.

swasheck

@MDCCL ... and @billinkc

Paul White

You don't have to be talented to be a moderator.

I mean, just look at them.

Ha ha ha ha ha

MDCCL

@PaulWhite If you consider there's a situation in which I can provide assistance, let me know and I'd be glad to contribute.

Paul White

@MDCCL Thanks. Keep an eye on the bounties tab. I feel sure some design questions will get there in the not too distant. I'll ping you here too (assuming I remember).

MDCCL

8:17 PM

@PaulWhite I've been a bit bussy lately, but I have also been waiting for some interesting scenarios to get more involved, hopefully there will be more appealing questions soon.

mikeTheLiar

I've got a merge join in SSIS 2012 that is behaving...oddly. It doesn't always match records correctly. It appears to be a binary failure (either all records are matched correctly or none of them are matched correctly). I'm quite at a loss.

ypercube

@PaulWhite Are you on a presonal quest with the bounties?

I see you have awarded a lot lately.

swasheck

@mikeTheLiar liar

mikeTheLiar

You caught me

Phrancis

@MaxVernon Yeah. Right now I'm just refactoring to make more readable + flexible + idiomatic, without changing behavior; it's only my 4th day at this job so I don't want to mess things up by accident until I know the structure better

swasheck

8:19 PM

i'm also pretty creative

MDCCL

@ypercube Quite generous of him, I have also noted that trend.

Max Vernon

@Phrancis lol, good idea!

mikeTheLiar

But basically there's some data incoming that needs to be compared against existing data. The two sets (incoming and existing) are joined on a composite key and if a database record already exists that record (should be) discarded.

But what happens instead is the join fails and I end up with a bunch of duplicate key errors.

Sometimes the join fails.

swasheck

wait. a merge join or a merge with a join?

Paul White

@ypercube Sort of. I have no use for rep any more so I might as well use it to encourage topic areas, participation, and resurrect unloved but good questions.

mikeTheLiar

8:21 PM

Merge join, sorry.

swasheck

merge join the query plan operator?

sorry if i'm being dense and obtuse

mikeTheLiar

whoosh right over my head. The data flow component

msdn.microsoft.com/en-us/library/ms141775(v=sql.110).aspx

swasheck

well i've been caught with my bias showing. how embarrassing

Paul White

@mikeTheLiar Are you 100% certain the input sort key properties are all set correctly. Merge doesn't detect such things IIRC. @billinkc would know.

mikeTheLiar

brb

Paul White

8:23 PM

I don't use SSIS much, but I do remember getting weird Merge results when I changed something small in the Data Flow and the Sort Key properties became unset or otherwise incorrect.

MDCCL

@PaulWhite From what point is it considered too much comments for a single answer/question? When is it suggested to move a particular discussion to chat?

Paul White

@MDCCL There's an automatic flag - though I forget the details it might be 12 or 20 in 24 hours. I will check in a minute.

But ultimately it's subjective. Don't know about the others, but if it looks 'long' and is just 2 or 3 people, I'd be quite likely to move it to chat.

Really comments aren't a great place for a discussion anyway, even if it weren't a misuse.

MDCCL

@PaulWhite Thanks. Yes, it's quite subjective, but I wanna keep it inside the recommended "official" limits, so to say.

Paul White

Auto flag is triggered by more than 20 comments posted in the past 3 days

@MDCCL You can always create your own chat room, that's what they're for. Anything you want me to look at?

mikeTheLiar

@PaulWhite yes, the import sort keys are set correct. The merge join component throws a fit if they're not ("columns must have matching names and metadata")

And the thing about that is that if it were incorrect, I'd expect it to fail all the time. Which is not what I'm seeing.

Paul White

8:31 PM

@mikeTheLiar Hm well I definitely had a problem in that general area. Took me a couple of minutes to identify it, I remember that. Was a while ago. Bill will likely know - when he wakes up.

mikeTheLiar

Sometimes the keys are matched (DT_I4 and DT_DBDATETIME if that makes a difference)

Composite key

Paul White

Does it fail sometimes with exactly the same inputs, or does it always pass/fail on a given input set?

MDCCL

@PaulWhite I am going to share my take on this question but it seems that the comments aspect can get a bit messy, so please let me know what you think and this can serve as a meassure for future situations.

mikeTheLiar

It appears to succeed when both sets of data have a match in the other.

So when an inner join == left outer join == full outer join, it succeeds on matching the keys.

Paul White

Ok. But just to labour the point for a moment, all inputs have IsSorted and SortKeyPosition set correctly?

mikeTheLiar

8:35 PM

Otherwise it fails (inner join is empty, left outer join has all rows from left input matched with null, full outer join has both sets matched with null)

Yes, I'm manually running both of them through a sort..

I was setting the IsSorted and Keys on the sources but moved to a separate sort when I started running into this weird AF problem

Paul White

Ah so there's a Sort on both inputs? That was my next suggestion - putting temporary sorts in.

mikeTheLiar

Paul White

So now I have nothing to add, will stop pretending I know SSIS, and go look at @MDCCL's link.

mikeTheLiar

:) Thanks for the help anyway.

Paul White

Well I didn't help, but it's nice of you to pretend I did :)

mikeTheLiar

8:38 PM

I'll hang around but AFK in case anyone has any input/questions

Paul White

SSIS diagrams are so pretty these days.

mikeTheLiar

Well, you helped me make sure that I'm not insane

Paul White

Did I? ;c)

mikeTheLiar

At least, in this particular instance

MDCCL

@mikeTheLiar ha ha ha

Paul White

8:40 PM

I enjoy SSIS when it is working. As soon as something goes wrong, I know I'm in for an extended period of irritation and frustration.

MDCCL

@mikeTheLiar A reasonable amount of insanity is healthy most of the time. Not applicable for databases, haha.

mikeTheLiar

>Of all the things I've lost, I miss my mind the most

And my primary keys

MDCCL

@mikeTheLiar Yo do not lose them, you surrogate them, ha!

Paul White

@MDCCL Doesn't look too bad right now on a first look. One thing I will say: comments are designed to be transient. Ideally they would go away as soon as they had served their purpose. Feel free to flag any pointless/out-of-date ones you see as "obsolete". Or for any other comment flag reason as appropriate.

Trivial example:

Please show us some of the SELECTs. — Rick James 2 days ago

The OP saw that comment and responded. No point it existing any more. It is just clutter.

People should be flagging comments a lot.

swasheck

@PaulWhite your wish is my command

MDCCL

8:47 PM

Ok, thank you for your observations, I will take them into account from now on. Yes, there are comments that don't add to much to the "discussion" having already been responded.

I'm going to attend the question now. See you soon.

Paul White

The great thing about comment flags is that it usually doesn't take many of them before the darn things go away automatically, without moderator intervention.

@MDCCL Ok let me know if/when you'd like those comments moved to a chat room. I still might do it anyway if it goes on, but you can always request it, either via flag other or in here.

@swasheck Thanks.

32

A: Is it OK to casually flag comments as obsolete?

That's exactly what the obsolete flag is for. Flag away! Comments are, by their very nature, transitional. Flagging helps keep them under control and tidied up. Note that if a comment has been flagged multiple times, it'll be auto-removed, so you are not overly burdening the janitoCTRL+Wmodera...

Phrancis

@ all - What's the best site to get SQL code reviews / best practices? DBA.SE or CR.SE?

(assuming a complete, working query is included in the question)

ypercube

I've answered the question properly (and not just in comments). Some mod will be happy ;) — ypercube 22 mins ago

and feel free to delete some obsolete comments there, @HappyMod ;)

Paul White

9:08 PM

@ypercube The one thing I can't do is up vote your answer!

0

A: Right Outer Join (column name doubt)

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) i...

ypercube

vote limit again?

Paul White

Yep. Every day. And it's not like I go nuts with votes.

Ha. You didn't change "doubt" to "question" in the title :)

Not that it matters. Just amused me.

ypercube

If I changed it to "question" I'd then had to remove it. Right?

Paul White

Er, would you? :-s

ypercube

i mean a question title with "question" ?

Paul White

9:14 PM

Better or worse than a doubt title with "doubt"?

I see what you mean though.

Fundamental issue: not a great title

ypercube

un-doubt-edly

Paul White

groan

I think I'll leave it. It's not clear what the right title should be, and it's not like it's the world's most generic problem.

ypercube

better?

Paul White

Yes, perfect. Nicely done.

Oh and btw if anyone ever wants to flag a whole comment block using "other..." that's fine too, just try to be helpful with the description.

ypercube

But we'll get only 1 helpful flag that way :(

Max Vernon

9:37 PM

so, @PaulWhite - it's 9:35 in NZ now? and you're STILL up!

Paul White

@MaxVernon Installing Andriod Lollipop on my phone :)

But yes.

@ypercube And what, pray tell, can you buy with your helpful flags? Seriously, I don't mind either way so long as it gets done. Totally all the same to me. Can only flag a comment every 5s though, which can get tedious.

ypercube

@PaulWhite a Marshall badge off course ;)

500 flags for badge

Paul White

Oh yes of course. Flag away!

Ha. This question has gone rather quiet. How very surprising:

ypercube

it's good that you got it because you can't raise flags any more

Paul White

-4

Q: Which indexing technique will be better for above scenario for updating records?

Orchid Textile is a large scale organization consisting of hundreds of employees in different office branches across country. HR department decided some new benefits and bonus policies according to pay scale and designation of employees. So they need to update employees’ relevant record based on ...

@ypercube I can too!

I just can't handle my own flags.

ypercube

9:47 PM

Really? Mods can raise flags?

Paul White

Not fully sure what the point of it is. But yes.

Phrancis

Quick question; I've always used current_timestamp to get the "now" value, but I see getdate() used everywhere here. Is there a significant difference or are they the same, for all intents and purposes?

I realize getdate() is a function and current_timestamp is a server clock value (I think)

Paul White

@Phrancis Exactly the same internally. More modern alternatives are available for the newer datetime2 and datetimeoffset types.

Andriy M

AFAIK, they are synonyms. getdate() is proprietary, current_timestamp is ANSI – something like that. They even return the same type (datetime).

Phrancis

OK. I'm tempted to use the ANSI standard over proprietary, is that a good idea?

Paul White

9:58 PM

Yes CURRENT_TIMESTAMP is silently translated to GETDATE() internally.

Both are fine, it's a matter of personal choice.

Phrancis

Ah ok

Max Vernon

From the perspective of a SQL Server developer, I'd rather see GETDATE() since I immediately understand what it does. CURRENT_TIMESTAMP is, at least to me, not nearly as common and therefore might create misunderstandings about functionality when doing code-reviews, etc.

Phrancis

OK good point

Hm. One more thing if I may... I've been using constructs like these while refactoring these queries, where I made the field optional (in case that information is not provided):

if @so_senttovendorby = ''
    set @so_senttovendorby = '%' + @so_senttovendorby + '%'

If I had a field that was an int identity and I wanted to do something like this, is there a simple way?

Paul White

How do you use @so_senttovendorby? In a LIKE clause?

Andriy M

Shouldn't it be if @so_senttovendorby <> '' ...? Or I misunderstood the purpose of that

Phrancis

10:07 PM

where /*foo*/ and "orders".so_senttovendorby like @so_senttovendorby

Paul White

That's what I thought. How would you extend that to work with integer?

Phrancis

I'm afraid if I knew the answer, I wouldn't be asking =;)

Paul White

@Phrancis Ok :) A typical answer is ... AND (@intval IS NULL OR column = @intval)

Assuming NULL means 'give me all the records here.

Phrancis

Just not sure if there was a simple way, or if I would have to write it as dynamic

billinkc

CAST("orders".so_senttovendorby AS varchar(20)) LIKE @so_senttovendorby

:P

Paul White

10:10 PM

Oh Bill shush. Go look at that SSIS Merge Join :)

@Phrancis Dynamic can work well, as can the construction I posted, especially if OPTION (RECOMPILE) is specified.

Andriy M

I think there's an Erland Sommarskog article about dynamic search that covers various options.

Paul White

Yes but it's very comprehensive.

Phrancis

Might be a good thing to bookmark for future reading; got a link?

Paul White

Which version of SQL Server?

Phrancis

2008-R2

Paul White

10:11 PM

sommarskog.se/dyn-search.html

Phrancis

Danke

Paul White

0

A: Best way to get time zone key name from SQL Server

Reading the reg key with this or SQLCLR is the only correct way I know (I personally would create a job which updates a table instead of enabling xp_regread though.). This powershell script is an example of how to update a config table with this information. $timeZone = (get-itemproperty 'HKLM:...

Phrancis

1 hour ago, by Phrancis

@ all - What's the best site to get SQL code reviews / best practices? DBA.SE or CR.SE?

Paul White

Good answer ^

@Phrancis Good question :)

@Phrancis Actually, you should ask that on Database Administrators Meta.

Phrancis

Good answer ^

Paul White

10:17 PM

:)

billinkc

@mikeTheLiar When you were "setting the IsSorted and Keys on the sources" you weren't just setting those flags, right? You were setting those flags in conjunction with your extract queries having an explicit ORDER MyInt, MyDate

@PaulWhite My replay thread works in reverse order

Paul White

@billinkc The flow has explicit sorts on each input.

Andriy M

On the one hand, if it's working code and you want people to review it, CR would seem the proper place. On the other hand, probably not many of us are hanging around at CR, so... :)

Paul White

2 hours ago, by mikeTheLiar

billinkc

@PaulWhite It does now, but I interpreted their comment as saying originally they had set those properties in the sources originally. Met a number of people that think that automatically sorts the data but as with queries, you only get sorted results when you provide an explicit ORDER BYin your source query or when you put an explicit Sort transform into the data flow

Paul White

10:22 PM

@mikeTheLiar You around?

SUPER PING! Get out of bed!

brb

billinkc

The only time I've have had "weird" results was when I was sorting the advisor id, alpha-numeric field, in both source systems and the merge join gave inconsistent results. Lesson learned was that while I sorted both ascending, once was an ascii sort, one was ebcidic

Wow, PDubs is heading to mikeTheLiars house to get him out of bed. Didn't take long for absolute power to corrupt absolutely

Paul White

The door's locked!

SUPER RAM!!!

Is there any useful/easy way to save an SSIS Data Flow as (gasp) XML or something that we could inspect for issues?

billinkc

It is xml

/snort

one of the reasons I've been a big fan of using Biml to describe my SSIS packages is that it is something I can share with someone

Pragmatic works has a product, BIDocumenter that rips apart your package and builds documentation showing what it does. That would show the source queries and tell us whether it's a lack of explicit sort in the components causing the impedance

Paul White

What is Biml? An open source tool?

Business Intelligence Markup Language

Business Intelligence Markup Language (Biml) is a domain-specific XML dialect for defining business intelligence (BI) assets. Biml authored BI assets can currently be used by the BIDS Helper add-on for Microsoft SQL Server Business Intelligence Development Studio (BIDS) and the Varigence Mist integrated development environment; both tools translate Biml metadata into SQL Server Integration Services (SSIS) and SQL Server Analysis Services (SSAS) assets for the Microsoft SQL Server platform. However, emitters can be created to compile Biml for any desired BI platform. While Biml’s declarative design...

Ah so BIDS Helper is the codeplex tool.

billinkc

@mikeTheLiar If you only ever get zero to one row match, a cleaner implementation would be to use a lookup component

@PaulWhite Yeah, BIDS Helper has functionality in it to translate the biml xml into the SSIS xml. Much cleaner, less clicking than actually coding SSIS packages

+ you can automate the daylights out of it

Paul White

10:33 PM

And it plays nicely with all versions of SSIS?

Phrancis

0

Q: Which site is best for code reviews of queries?

Imagine a junior database developer / DBA that may not have an avenue to get their SQL code reviewed as they are learning the ropes. There is of course the Code Review Stack Exchange (CR.SE) site, which can and will review SQL if it is posted and falls within the site guidelines. However, there ...

discussion allowed-questions

Paul White

Yes I saw. Also @MasterDatabase will auto post that here soon too. Thanks.

I see you've had some success on CR.

11

Q: Sales, Taxes and Totals

I'm working to get some practice with SQL functions which are often used in a financial setting, and I created this simple table with products, prices and tax rates basically. I then wrote a query to simulate a purchase invoice type of result set (I realize things like that are most often done a...

sql sql-server finance

But then it's easy to be successful when you...

I wish more people included scripts to create sample data. ++ — RubberDuck Jul 21 at 23:51

Phrancis

Hahah

Paul White

Which, in passing, is an awesome name for a Code Reviewer.

billinkc

@PaulWhite It does (assuming I didn't use functionality that didn't exist on that version). One biml can be used to emit ssis packages targeting 2005 to 2014

Paul White

10:36 PM

@billinkc Sweet!

billinkc

So as a consultant dealing with who knows what versions, I solve the problem once and tune my deployment for the client environment

Phrancis

@PaulWhite Indeed

TTQW

Paul White

@Phrancis I notice in that Q you don't mention "best practices" as you did in here earlier.

Phrancis

Ah, let me edit that in

billinkc

Hi!

mikeTheLiar

10:40 PM

@billinkc yes, I was sorting the data. And a lookup is what I ended up doing. Thanks for the response at any rate.

Paul White

@Phrancis TTQW?

Phrancis

Time To Quit Work

Paul White

That was new to Google and Urban Dictionary.

ThingsLearned++++

Phrancis

21

A: What's a Zombie? And what are the many other memes of Code Review?

Meme: [TTQW | TTGH] | TTGTB Originator: [attributed to] Simon André Forsberg (first TTQW here) Cultural Height: The 2nd Monitor Background: leaving the chatroom and/or going AFK isn't something we do often. These funky acronyms efficiently communicate that it's either time to quit work (or tim...

Master Database

1

Q: Which site is best for code reviews of queries?

Imagine a junior database developer / DBA that may not have an avenue to get their SQL code reviewed for best practices and such as they are learning the ropes. There is of course the Code Review Stack Exchange (CR.SE) site, which can and will review SQL if it is posted and falls within the site ...

discussion allowed-questions

Paul White

10:42 PM

ThingsLearned++++

And there we go.

Master Database is user ID -247

Phrancis

huh^

Paul White

Was just curious.

Max Vernon

@PaulWhite I'm just a teeny bit envious!

Paul White

@MaxVernon Because I learned what TTQW means? ;)

Max Vernon

@PaulWhite yup!

along with all that other uber-cool mod stuff.

Paul White

10:47 PM

@Phrancis So, you are specifically asking about SQL coding "best practices" rather than anything more general related to DBAing?

@MaxVernon It's really not as cool as you might think. @jcolebrand was right about the dishes.

Max Vernon

@PaulWhite yes, but there is still a certain mystique for me. :-)

Paul White

Well I shall endeavour to look mysterious from time to time then.

Hm.

Max Vernon

good!

How's the Android install going?

obviously you rooted your device?

Paul White

Ooo good point, should be done by now. Was optimizing app 130 of 140 last time I checked.

I certainly hope not!

Just a standard Lollipop 5.1, hopefully will just work.

Max Vernon

I rooted my HTC EVO 3D, and afterwards that summab*tch was mine!

Paul White

10:50 PM

So rooting is a good thing?

Max Vernon

When it starts up now, it shows the classic Linux boot screen. Just cool.

Paul White

Ha ha ha that so means something different over here.

That is cool!

Max Vernon

I was born in England, so I understand the root reference.

I love having my phone running what I want it to run.

Paul White

I thought the English slang was "shag"

Max Vernon

Having said that, for the last 2 years I've had a Nokia Lumia 920 running Windows Phone 8.1, so that's not exactly gonna be rooted.

@PaulWhite yep pretty much. I guess I must've heard rooting somewhere from my Australian family/friends maybe.

Paul White

10:53 PM

Anyway ... (sniggers) ... the upgrade is complete but it's in the other room on charge for another 30 mins before I can really check it out.

I mean I could move my laptop but laziness.

Max Vernon

She's got a whole new thing going on now.

how long have you been up, man! I don't think laziness comes to mind.

Paul White

I need to buy a flash phone one day. Even my wife has an iPhone 5s (I think).

I have this $99 Motorola G thingy.

Something like that anyway.

Max Vernon

I'm really looking forward to getting a giant slab of a phone. They say Microsoft is releasing a 5.7" Lumia this fall.

@PaulWhite hey, if it works, why mess with it, right!

Paul White

Are MSFT moving to Android with Lumias?

I thought I heard rumours but then there's Win 10.

It's hard to keep up.

Max Vernon

I just read an article yesterday showing how you can run select Android apps on Windows 10 mobile right now.

of course, if the app relies on Google platform services, it won't work.

Paul White

10:57 PM

Right.

Max Vernon

anyway Paul, it's been a blast chatting with you; however I have to get home or my wife will wonder where I've been, and that's never a good thing!

take it easy and get some sleep!

Paul White

Cheers

Max Vernon

cheers

Transcript for

The Heap™ – Consultancy ©®