The Heap™ – Consultancy ©®

1:44 PM

wow has this room been dead lately.

1:57 PM

@MaxVernon I have other priorities, we have a new coffee machine at work

@TomV mmmmmm coffeeeeee!

2:10 PM

Do you get any specific errors when you run the query? — Tom V 1 min ago

@TomV - it's a "shrug report" - a bug report except you shrug and say, "I dunno"

I never realized there was so much to learn about dashes

All that thanks to the fact that apparently you cannot copy paste code from the authority and expect it to work

Dash

The dash is a punctuation mark that is similar to a hyphen or minus sign, but differs from both of these symbols primarily in length and function. The most common versions of the dash are the en dash (–) and the em dash (—), named for the length of a typeface's lower-case n and upper-case M respectively. Usage varies both within English and in other languages, but the usual convention in printed English text is: Either version may be used to denote a break in a sentence or to set off parenthetical statements – ideally with intradocument consistency. Style and usage guides vary, but often in this...

2:40 PM

@TomV it's not just dashes that cause that code not to work ;-)

3:35 PM

Hi, I am trying to write up a question but having difficulty phrasing the actual question. I think I can describe the problem sufficiently, but not necessarily succinctly in the title. Perhaps someone could help, even if it involves hitting me over the head with a blunt object?

Given a table with foo, bar, and type as fields (also other fields), I want to select unique Foos. However, I want to select those of a specific type over those of another type where there are multiple foos (there won't always be). Bar will vary based on foo and type, so it's relevant to the results.

Kin

3:54 PM

@PaulWhite Provided my input .. sorry for being late.

4:09 PM

finally have a release date on 2016. finally.

@PaulWhite if it's crap, we migrate to SO

@swasheck banned :P

@bluefeet please dont ban me from SO. oh please please please

Andriy M

@peacedog So you essentially want one of each foo, only not a random one but based on type, correct?

@swasheck how about network wide? I'll abuse all the powers

@AndriyM correct. I will always have a foo of type "a". I will sometimes have foos of type "b". In that case, I need the type "b" and not the "a" (and all associated data, of course)

4:24 PM

@bluefeet I'm not sure that would constitute abuse

I know how to do this if I use an intermediate table variable (e.g.), it just seems like something I should be able to do without that

@MaxVernon :)

Andriy M

@peacedog Although I don't know which DMBS you are using, I guess I'd be able to implement this using a single query without temporary tables. But I would likely be struggling with the title for the question as well.

"Select one row from each group of foo based on type"?

This class of questions is called greatest-n-per-group, by the way

Sql-Server-2014

hmmm, that might provide inspiration, let me go read the "n rows per group" and see what sparks, thanks!

@bluefeet go for it. i dont really contribute anything significant here anyway

4:40 PM

oh @swasheck you know you'd miss us.

1 hour later…

5:57 PM

@peacedog The question turned out more than OK

6:10 PM

@Tom V thanks, gotta give a lot of credit to @AndriyM

Because that title really fit once I read it

@peacedog It's not only the title

I mean it has table structures, sample data and so on

Well I did put thought in to it ;) Like I said I thought I had the problem well described, but I just couldn't think of a good way to title it.

Seems like an incomplete set of Avengers though

@PaulWhite Me has power 5 while you only have power 7, so there are more issues with the data quality than incompleteness

1 hour later…

7:45 PM

@peacedog Just to clarify (now I'm not on my phone), you want one row per Avenger, preferring a 'bar' type if it exists, otherwise any one row (no preference)?

yes. Only. . . well someone said "oh, sorry, the requirements on this are wrong here's what they really are' and now never mind :( (I need to join Avengers to itself so I can show power levels for the foo record and the bar record if it also exists)

OTOH, that this was a very instructive day and I learned some new stuff so yay

oh, and sorry, it was "prefer bar, then foo, then not care about the rest"

with the understanding that there would always be foo records

@peacedog That can happen.

yeah, it would have been nice to know that earlier today but not the end of the world.

It's always the end of the world when the Avenger's are assembled

@billinkc the avenger's what? which avenger, at that?

8:01 PM

The farmer typo is the result having used a mobile keyboard. The offending device has been sacked

Those responsible for sacking those responsible for the initial sacking have also been socked

@peacedog You mean like:

SELECT
    A.Avenger,
    foo = MAX(CASE WHEN A.[Type] = 'foo' THEN A.[Power] ELSE NULL END),
    bar = MAX(CASE WHEN A.[Type] = 'bar' THEN A.[Power] ELSE NULL END)
FROM @Avenger AS A
GROUP BY
    A.Avenger;

i.e. a pivot?

That was actually quicker than I expected.

haha

Must have that on speed dial.

8:08 PM

bookmarked and ready to go

@peacedog Still, you should accept one of the answers that solved the original problem.

@Kin Thank you!

@billinkc while you're here, let's render you useful. is there a way to programmatically change server names in the connection manager for an SSIS package SQL Agent Job?

A: Passing Source and Destination for SSIS package created from Export/Import Wizard of SSMS

8:42 PM

Overview SSIS, via the Import Export Wizard, is available in all editions of SQL Server. However, SQL Server Express does not allow you the ability to save a package generated using the wizard. And a saved package is what we will be operating on. What can I make dynamic You have correctly iden...

Lamak

@swasheck don't ask for the impossible

@billinkc that's kinda tangentially related ... i think .... i'd like to change the SQL Agent job ... but have an upvote anyway

@swasheck what were you looking for then, perhaps I misunderstood the question

@billinkc my packages generally have two sources and a target ... those sources are changing ... i can change that by opening the job step and changing the values in the job step connection manager tab of the "Execute SSIS Task" step

i have a lot of packages and dont want to manually edit each of those

@billinkc or maybe your answer does address this but i'm too dumb to understand

8:55 PM

Sorry work ended for me and now I am home. I am going to accept an answer just haven't decided which one yet.

2012+ projects deployed to SSISDB catalog or not?

For some reason this morning I started out saying "I should be able to do this with coalesce" and then ghosts made me think that was wrong.

@billinkc 2012 deployed to ssisdb

> SSISDB.internal.execution_parameter_values

has my values

but i dont think i can update a system table like that, can I?

DECLARE @var sql_variant = N'.\dev2014';
EXEC SSISDB.catalog.set_object_parameter_value
    @object_type = 30
,   @parameter_name = N'CM.tempdb.ServerName'
,   @object_name = N'mvp_PeterLarsson.dtsx'
,   @folder_name = N'so'
,   @project_name = N'so'
,   @value_type = V
,   @parameter_value = @var;
GO

@billinkc oh SNAP. thanks ;)

9:03 PM

Configuration is what you're looking for then. The above query will change the ServerName property of a connection manager called tempdb for a package named mvp_PeterLarsson.dtsx in the project called so which is stored under the so folder

i really appreciate this. i'll give it a go tomorrow

You can then couple queries against the catalog views to find all the connection managers in your packages and go go dynamic es que EL!

1 hour later…