The Heap™ – Consultancy ©®

Simon Righarts

00:02

Hm. Have a table with two varbinary(max) columns, there's no non-table-scanning way to break down the average (or overall) size of each column is there? (No indexes on either column, since A: it's binary and B: the total size of the data in that table is over half a TB)

Kermit

01:24

Don't know if I've posted this here before

billinkc

04:53

-1

Q: SSIS Environment variables SSMS vs the OS

As I'm cramming for the 70-463 exam on SSIS, I'm making my way through the mess of terms they've introduced. Now at the project/package deployment. Can anyone explain me the difference (if there is such) between: the "environment variable" which is defined with the OS (control panel -> system -...

Jack Douglas

09:02

I forgot about this and hit it again today. At least it reminded me to accept your answer, thanks :) — Jack Douglas ♦ 52 secs ago

Mark Storey-Smith

09:16

@BrentOzar "SQL backups are tested by having a dedicated server just for restoring them, constantly (that's a free license - do it)". Is that correct?

Simon Righarts

09:46

@MarkStorey-Smith MS licensing can best be described as "Byzantine", so it's really anyone's guess

Mark Storey-Smith

@SimonRigharts Well, until I read that post I was 100% sure this wasn't allowed. Paul Randal seems to think the same (albeit back in 2009). Maybe there's been a change in the 2014 terms?

Has anybody encountered ActifIO in a SQL Server context?

ypercube

10:13

After 3 days and 4 answers, they decided to change the code. I don't like when they do this: dba.stackexchange.com/posts/72367/revisions

Some of the answers as are relevant, despite the change, but still ...

Faheem Mitha

11:10

If I want to feed multiple files to PG's COPY statement, is something like this the best I can do?

2

A: Copy multiple CSV files into postgres

In linux pipe the output of the files listing to psql. Make copy use the standard input: cat /path_to/ys*.csv | psql -c 'COPY product(title, department) from stdin CSV HEADER' Look for the equivalent in other OSs

COPY itself does not seem to support multiple files. Or should I pre-concatenate the files?

I.e. is there any disadvantage in reading from standard input?

Mark Storey-Smith

11:42

@ypercube Not very useful response back from the estate agent mob was just that order of preference for areas is Crystal Palace, Norbury, Thornton Heath. No suggestions as to where in those areas is good/bad unfortunately.

ypercube

@MarkStorey-Smith No worries.

We have wondered around all day on Saturday with my wife and saw several houses/flats.

ypercube

11:54

@MarkStorey-Smith We lean towards to not moving there but staying in Woking.

JNK

12:44

The UK has great place names

Yasir Arsanukaev

0

Q: Replication saying my snapshot Publication Does Not Exist. But it does exist

I used the SQL Server (2008) wizard to create a new snapshot publication on my sever called "BlinkViewsOnly" in the BlinkDataMaster database. Publication was created successfully. I then used the wizard to create 1 subscription to my laptop. Results messages all appear to be successful, and I ...

sql replication database-snapshot

VtC (off-topic)

Kermit

13:21

@JNK do we want the question above?

Faheem Mitha

@JNK Certainly colourful.

And mostly old too.

JNK

@Kermit seems to be in our wheelhouse

ypercube

@Jack, @dezso or any other Postgres user around?

Is it normal I see "Running clusters: 9.1/main" when I check the Postgres service?

ConcernedOfTunbridgeWells

13:43

@ypercube I used to live in Woking. It's OK. The nice areas are Horsell and St. Johns. The train links into London are pretty quick as well. If you hunt for digs around the brewery road area you can get a nice house that's walking distance to the train station as well.

@JNK I used to live just up the road from here:

ypercube

@ConcernedOfTunbridgeWells Knockhot and bottom? Nice

ConcernedOfTunbridgeWells

@ypercube Pratt's Bottom no less.

bluefeet

@ConcernedOfTunbridgeWells ha, my last name is Pratt :)

ConcernedOfTunbridgeWells

@bluefeet If you ever come to London I'll take you to visit Pratt's Bottom. Not that there's anything much there.

dezso

13:59

@ypercube where do you see this?

bluefeet

@ConcernedOfTunbridgeWells I'll keep that in mind.

swasheck

14:40

Cdr Sarah West must really love her seamen

2

bbc.com/news/uk-28507406

ConcernedOfTunbridgeWells

@swasheck That really needs to be followed by fnarr fnarr

swasheck

fair enough

ConcernedOfTunbridgeWells

Golly. It's in the OED

A coinage from Viz made it into the OED.

@swasheck It this case it really does help to be British and of a certain age, although Viz made it as far as New Zealand. Never seen any reference to it gaining any traction across the pond.

swasheck

i'll just believe you that it's frickin' hilarious ;)

billinkc

14:56

@ConcernedOfTunbridgeWells tl;dr but would it be a more explicit version of Mad magazine?

Assuming you have a frame of reference for Mad...

ConcernedOfTunbridgeWells

@billinkc Sort of, with a very North-England cultural basis.

Really big on puerile toilet humour and satire on popular culture.

Very un-PC and occasionally hysterically funny.

viz.co.uk

Kermit

is this GROUP BY valid in oracle?

0

A: Row that should have a 0 count not showing

How about joining to the tables instead of using the WHERE clause: SELECT s.Section_Id, s.Location, COUNT(e.Section_Id) AS Enrolled FROM Course c LEFT JOIN Section s ON c.Course_No = s.Course_No LEFT JOIN Enrollment e ON s.Section_Id = e.Section_Id WHERE c.Description = 'Project Management' ...

bluefeet

15:12

hmm, I wonder why?

0

Q: Bad performance on sql query

SELECT orcamentos.id FROM usuarios, clientes, empresas, orcamentos WHERE ( ( clientes.nome LIKE '%VARIAVEL%' AND clientes.email=usuarios.usuario ) AND orcamentos.cliente=usuarios.id ) OR ( ( ( empresas.razao_social LIKE '%VARIAVEL%' ...

sql performance

Jack Douglas

@Kermit which bit are you worried about?

Andriy M

@Kermit I'd expect it to be. It groups by three columns, of which there are only two in SELECT, but that is fine in SQL. You can't select more (non-aggregated) columns than you group by, but you can group by more columns than you select.

Kermit

@AndriyM (and @JackDouglas) okay that makes more sense... c.Course_No didn't appear in the SELECT clause.. i don't think that would be valid in sql server

Jack Douglas

@Kermit sqlfiddle.com/#!6/d41d8/20512

@ypercube how are you checking it?

ypercube

@dezso @JackDouglas running: service postgresql status

Jack Douglas

15:17

root@dallas:~# service postgresql status
9.3/main (port 5432): online

I guess it's fine

Andriy M

@JackDouglas Wrong Fiddle, it seems

Jack Douglas

sorry: sqlfiddle.com/#!6/d41d8/20513

ypercube

@JackDouglas I get the same as yours in my Ubuntu desktop but in Debian:

> root@dbserver:~# service postgresql status
Running clusters: 9.1/main

Jack Douglas

mine's a vanilla Debian Wheezy install with 9.3 installed on top

Kermit

@JackDouglas makes more sense

ypercube

15:21

@Kermit It's valid in SQL-Server as well.

Kermit

well bob's your uncle

migrate to Database Administrators ?

0

Q: Unable to print SQL Server 2014 Reporting Services in IE8

My company has upgraded all of its client databases from sQL Server 2008 to 2014 and we have now found that none of our clients are able to print the reporting services reports in IE8. Unfortunately we are required to support the legacy app but I have been unable to find a resolution. When clicki...

sql sql-server internet-explorer-8 sql-server-2014

swasheck

@billinkc was you comment directed at me?

billinkc

Nope

swasheck

ok. just couldnt tell. i wouldnt care if it was, i was just trying to decipher the meaning

Zane

16:07

@swasheck Mine was.

swasheck

cannot see it

Zane

That's because it was never really there.

swasheck

keyser soze of comments

Zane

The worst part of my job is after I've submitted my code being forced to go back and tag a little WITH(NOLOCK) after ever FROM or JOIN.

With each add I die a little inside.

swasheck

or you could ...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning

2

billinkc

16:21

sqlblog.com/blogs/aaron_bertrand/archive/2011/07/12/…

So say we all

Marian

@billinkc can I (ab)use your SSIS knowledge, please?

billinkc

sure

Marian

is there an easy way to check if a package is using Enterprise features?

or I have to parse its XML content? like here

billinkc

If by enterprise features, you're just looking to see if they used things like Fuzzy Lookup, etc and not digging into a SQL Task to see if they turned on compression or something, you could grep the file looking for the component ids

Marian

only for SSIS features, not for the SQL Server itself

billinkc

16:27

There's very few EE features in SSIS, personally never had cause to use any of them. It'll be what, the Fuzzy* components, CDC if you're in 2012 and ... I'd have to look at the documentation

Marian

the instances will be mostly 2005 and 2008R2 and we've got packages developed by other devs, so I've no idea what's inside :)

billinkc

2008 ComponentClassIDs
Fuzzy Grouping: {0A1BDBA0-9C6E-4AD7-97B1-E0E79E462629}
Fuzzy Lookup: {5056651F-F227-4978-94DF-53CDF9E8CCB6}

2005 {0A1BDBA0-9C6E-4AD7-97B1-E0E79E462629} / {5056651F-F227-4978-94DF-53CDF9E8CCB6}

If there were other EE features, the component ids can be found at msdn.microsoft.com/en-us/library/gg616758(v=sql.105).aspx msdn.microsoft.com/en-us/library/gg616758(v=sql.90).aspx

Unless Zane's aware of other ways to find it

Looks like Data Mining and cache connection managers (surprised me) are also on the EE feature list msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

Marian

alright, thanks. I was thinking if maybe there's a bit IsEnterprise somewhere and I'm just missing it.

billinkc

You made me snort

Marian

hope is was related to a smile, too :)

Kermit

16:42

swasheck

@billinkc when the day is done and you want to run ...

Zane

17:08

@swasheck ahhh but I can't

swasheck

por que no?

Zane

@billinkc nope.

bluefeet

0

Q: Select returns inconsistant data

How is this possible? insight=> select id from analysis_analysis where status != 'finished' and status != 'archived' and begin_at < '2014-07-28 17:23:27' limit 1; id ------- 46632 (1 row) insight=> select id from analysis_analysis where id = 46632 ; id ---- (0 rows) I execute theses q...

postgresql postgresql-9.3

those darn bugs

Zane

Love when I get the 'I desperately need you help this doesn't work. Let me pick a decidedly inconvenient time to work through it with you." Requests.

Starting to write a presentation on Reading execution plans for beginners and it's trickier than I thought. I'm trying to decide where to go and how deep to dive.

billinkc

17:35

The feedback I received was that I wrote my presentation for the alpha nerds because that's who I hang out with. Instead, I needed to simplify it a few orders of magnitude to reach my audience members

YMMV

Zane

If it goes well maybe I'll submit it to SQLSat MSP as well.

Lamak

@billinkc alpha nerds?

billinkc

Speakers and other a-type, overachievers

Lamak

@billinkc and how do they let you hang out with them?

like, an ego boost?

billinkc

Pity

Zane

17:57

I was looking at the list for MSP is looking more and more intimidating. However there isn't much for SSIS so there may be a chance.

Zane

18:57

@billinkc didn't you say you used to use HierarchyID for a project?

billinkc

I did

Zane

A coworker is telling me there is no way to update it it with a set based operation. I'm assuming this is not true but looking to confirm quickly.

billinkc

Unless I'm misunderstanding their statement, I'd say there's no way what they're saying is correct

Zane

He's using a loop at the moment.

billinkc

Looking at my code, I only generated a hierarchy id, never updated it but I don't recall seeing anything in the documentation as such

Logically, it might be hard for them to calculate the new value but syntactically, I don't think there's anything stopping them.

My calculation is recursive so it's got that going for it...

Zane

19:13

See history if you want to see it.

swasheck

dont tell me what to do

Zane

I already did. What are you going to do about it.

Stack Exchange

swasheck has removed Zane from the list of this room's owners.

swasheck

:)

Stack Exchange

swasheck has added Zane to the list of this room's owners.

Zane

19:16

It's a double loop

Why?!

@swasheck Point taken... :)

swasheck

fbcdn-sphotos-c-a.akamaihd.net/hphotos-ak-xfa1/v/t1.0-9/…

2

Zane

This HierarchyID seems like a needless ass pain.

bluefeet

@billinkc ssis question for you

Zane

It seems like just having a relational table would solve the problem with less jacking off.

bluefeet

we've got an Excel file that we load in. The Source uses Sheet$A3:O - if I add new columns to the spreadsheet - how can I easily tell it to start using the new columns. I've only been able to edit the XML to include the new ones. Is there some other way I'm missing?

billinkc

19:44

Excel, SSIS and easy never go in the same sentence unless the sentence also contains "can't make it Fing"

Rant aside, you could look at querying the worksheet instead of table access mode

You could also try named ranges or whatever they're called in Excel.

SELECT * FROM Worksheet$MyRange (I'

'd have to bingle the exact syntax). Range doesn't have $ in it, IIRC correctly

Also, script approach via 10lb sledge

2

Q: script task in SSIS to import excel spreadsheet

I have reviewed the questions that may have had my answer and unfortunately they don't seem to apply. Here is my situation. I have to import worksheets from my client. In columns A, C, D, and AA the client has the information I need. The balance of the columns have what to me is worthless inf...

excel ssis

bluefeet

ok. I'll take a look but that'll involve more changes at this point.

The XML edit was quick

I just wasn't sure if I was missing some easy fix

billinkc

Negatory. My feedback for SSIS pain points to the MS folks was "Excel in general. I don't even touch the SSIS questions that pertain to using Excel as a source or destination."

Zane

It's surprising how poorly they play togather.

bluefeet

good to know. I'll stick with my XML edit until later. I don't feel like completely redoing it right now

@Zane it is microsoft

Zane

Excel doesn't play well with a lot of things from what I can tell.

Mike Fal

19:52

Excel is a great tool for data analysis. But not for data storage/ETL

Zane

This hierarchy id crap is the worst project. I can't believe how poorly this is all set up.

billinkc

The problem with Excel is that they haven't been able to give it a clean rewrite due to a need to retain backwards compatibility. I have to believe that if they could take all the lessons learned and just start with a not-stupid file format, world peace would be achieved

5

Zane

@billinkc but then the spreadsheet I made back in 94 wouldn't be compatible!!!!

swasheck

@MikeFal yo

billinkc

troof

Mike Fal

19:53

@swasheck oy

swasheck

what's the frequency, kenneth?

Zane

@swasheck REM blows.

swasheck

20:15

@MikeFal ... so ... how're things

how're the talent-poaching ventures?

Mike Fal

@swasheck Things are mediocre. Stressful weekend. Poaching.....I need to get an update.

swasheck

sorry man. work-related stress?

gosh. why is my fullscan stats update taking so long in this 16bn-row table

Mike Fal

@swasheck Regrettably, no. But it will be fine.

swasheck

well, sorry nonetheless.

JNK

@swasheck did you try NOLOCK?

swasheck

20:22

@JNK maybe this is what your histogram should be. maybe not, though.

JNK

@swasheck "This is closeish!"

swasheck

and i seem to recall that @PaulWhite said something along the lines of "a good estimate is acceptable" in a blog post. ergo, UPDATE STATS WITH NOLOCK!

sadly, all my stats updates show as running with READ COMMITTED

JNK

seems legit

Zane

This loop crashed SSMS.

Oh how I hate this issue.

Mike Fal

loops = You spin me right round, baby, right round.

Paul White

20:27

@swasheck Yes, in my most recent one

swasheck

;)

@PaulWhite i think that this thing can be truly awesome, sys.dm_exec_query_profiles, but am struggling to find a way to actually use it properly.

i've read some blog posts

it seems like i would have to be excellent with timing things in order to capture final output

Paul White

@swasheck Yes it needs tool support to make it easily usable/informative

Zane

This isn't a great MSDN page.

msdn.microsoft.com/en-us/library/bb677298.aspx

JNK

@Zane seems to be last page in a tutorial to be fair

Zane

20:44

@JNK Just seems odd that it essentially a link to the first page.

mmarie

@Zane Oooh, are you playing with hierarchyID?

We have some of that on this project that Bill was working on.

Zane

@mmarie No I'm trying to give emergency surgery to a project that is populating HierarchyID in the worst possible way.

mmarie

@Zane Eew, that sounds unpleasant

Mike Fal

Hey @swasheck: meetup.com/Denver-PowerShellers

Max Vernon

<-- goes down the HierarchyID rabbit hole.

swasheck

21:05

@MikeFal NSFW

Mike Fal

@swasheck That's right, man. Hardcore, full-frontal scripting.

3

Zane

21:21

@MaxVernon let me know if you find any gems in that hole as all I'm finding is misery.

swasheck

^^^ bro. do you even english?

Zane

21:43

I've been reading and reading and reading but I don't see the value of this datatype. Am I missing something here. It seems just having a table to track the management hierarchy would be infinitely easier to work with.

Paul White

Holy War material right there.

bluefeet

and you guys think the sql folks are bad on SO

-2

Q: A bunch of questions about generator-cg-angular

My app generator comparison has a bunch of unknowns about generator-cg-angular. The True/false items are benefits or attributes that a generator may have or not have. These things might also be 'optional' or not applicable due to other factors in the generator. For the Explain items, I'd like ...

angularjs

a bunch of questions = 73 in a single post

Paul White

At least you now have a definitive example of "Too Broad" :)

Max Vernon

@Zane its a varbinary -> not sure how wide it is. I think it is officially a varbinary(max), but I wouldn't be surprised if I was wrong!

bluefeet

@PaulWhite that is an understatement :)

Zane

21:54

@MaxVernon "The type internally holds a binary value as large as 892 bytes that represents the path of ancestors leading to the current node."

swasheck

it's so funny how many people misspell rogue as rouge. totally change the meaning and it's HILARIOUS

Zane

@PaulWhite over the hierarchyID datatype?

Paul White

@Zane Yes.

@Zane @MaxVernon It's actually a system CLR type. It does use a proprietary binary format for serialization, but that's not the same as saying it is a varbinary.

@swasheck Could certainly lead to some rogue faces

swasheck

yep

we have a few queries that have "gone rouge"

reminds me of this

►

Paul White

The binary format of the hierarchyid CLR type can be seen in the source here

Actually that's not quite the whole thing, but the ideas are there

Zane

22:23

@PaulWhite Do you stand on any particular side of that holy war?

Paul White

@Zane All the alternatives have pros and cons. It depends on the project requirements. So, sitting on the fence, yes. I have never had the need to work with hierarchical data in a real system.

Zane

I feel like just having a table structure to handle hierarchy feels more intuitive to me.

This may have to do with the scenario that I'm working with hard to tell as this is my first time using HierarchyID

Paul White

22:43

Simplest is often best.

Transcript for

The Heap™ – Consultancy ©®