The Heap™ – Consultancy ©®

ypercube

10:16

1

Q: SQL Server : create dynamically queries to select all related data in DB based on entry table and ID

I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name. It doesn't have to be that fast, but it should be able to use against every db, given only 1 ID and the...

An interesting problem but probably too much for a dba.se question.

Tom V

10:29

@ypercube it depends

a fully working solution, probably, I started some POC but it gets messy with recursion and functions and string concatenation

but he has c# as an option too, performance doesn't matter he says

so some starting points could get him going

I was thinking of having a go at an answer later today, but a fully working solution recursing over sys.foreign_keys, supporting multi-column relations and the likes is going to be quite a bit of work

ypercube

I'd go with something like:
First identify all FKs in the db in question.
Copy all tables to a new db - but without the FKs and no data.
Copy the "starting rows" from the db to the copy-db
Create two statements for each FK (one for each direction) that finds rows in the db that are related to the rows in the copy-db and copies them (counting the copied rows) if they haven't been already copied.
Iterate over all these statements
If one iteration produces 0 copying, stop.

Tom V

@ypercube I was under the impression he needed one large result set

so this is what I had in mind

ypercube

I read the comment where they say "a lightweight db export wich will export only the data we need ..."

Tom V

would still need recursion though

if tablea is related to tableb, and tableb to tablec & tabled you can recurse until you have no more related tables

I asked clarification, not sure I agree with your interpretation of that line :)

Mark Storey-Smith

wassup

Tom V

12:57

I guess someone peed in this guy's cornflakes this morning

dba.stackexchange.com/review/first-posts/50686

Paul White

13:10

Yep.

mmarie

That guy is clearly grumpy, but is the SQL Server Agent crippled compared to other technologies? I've only ever written ETL on SQL Server and used the agent. I don't think I've ever heard that complaint. But now I'm curious.

Tom V

@mmarie I dunno, I actually like it with the proxy accounts and stuff

not sure what he means with the cryptic errors too, the errors are just propagated from your failing job (albeit truncated in the job history)

billinkc

14:11

I've used 4 scheduling tools: SQL Agent, Windows Scheduler, Cisco's Tidal and AdvSysCon's ActiveBatch. The last two are are Enterprise schedulers so they are much more powerful. I easily liked Tidal the best even if it has a Java interface. Agent would be my second preference just because I can never seem to find the right level of granularity with Windows Scheduler. ActiveBatch was a steaming pile of feces

ypercube

14:23

@billinkc a steaming pile of Enterprise feces ;)

billinkc

Probably Greek feces. They seem to be the originators of all the great works

of poop

Feel free to Close this one

-1

Q: What are the advantages of using an web services over SSIS Package?

This is a general question which i came across while working on migrating an SSIS package to Web API project. We have an SSIS package which is primarily used to transform some text files and then store the data from the files into the database or send the files to other systems. I wanted to know...

web-services ssis difference

Also, too localized

0

Q: Maximum character length for a column if I concatenate all the columns together in SQL Server for SSIS packets

I have a project requirement which extracts data on a daily basis and sent to another data warehouse. Each query has different set of data and number of columns returning are different and also the datatype. I want to schedule it to run on everyday and the result should be populated in a single f...

sql-server ssis reporting-services

swasheck

14:40

we paid for all of these enterprise feces, we must use them

would the first index satisfy queries similarly to the second index?

create index on dbo.table1 (col1,col2)

create index on dbo.table1(col1)

i.e. if i filter just on col1, would i be better served to keep the second index or to drop it and allow the first to satisfy the query?

Tom V

where col1 ='something' would be served by the first index

on first sight i'd think i would drop the second

but others in here know better than me

swasheck

that's my thought as well ... using the phone book analogy it makes sense to me

dezso

@swasheck unless col2 is something really wide, I wouldn't expect much difference between queries served by either one

ypercube

@swasheck I think some queries with this condition and joins to other tables will prefer the second index - and do the join more efficiently.

dezso

meaning the second can be dropped

except cases

swasheck

14:55

@ypercube hm. tell me more ... i'm slow. you mean a single-column join?

ypercube

I mean a join that uses the table's CI column.

swasheck

ok

ypercube

or a query with no joins: ... where col1='something' order by table1_ID;. Assuming the ID is the CI.

The first index has the CI values mixed up.

swasheck

clustered is ...

create clustered index on dbo.table1(col0,col3)

pseudocode, natch

sounds like microsoft had some news today

Tom V

@swasheck win10 release?

swasheck

15:09

yeah. just a quick little joke

dezso

@swasheck I thought you thought postgres didn't NOLOCK

in this case you'd be even right

or you'd even be right?

I think speaking German doesn't help my English

billinkc

You'd be wrong

instantrimshot.com/index.php?sound=rimshot&play=true

swasheck

RONG

dezso

you are wrong

furthermore, your link doesn't produce a sound for me

billinkc

you're browsur is wrang

dezso

15:21

boss of zem

swasheck

RONGE

BRIYOHN

billinkc

Invader Zim

Invader Zim is an American animated television series created by Jhonen Vasquez for Nickelodeon. The series centers on an extraterrestrial named Zim from the planet Irk, and his mission to conquer Earth and enslave the human race along with his malfunctioning robot servant GIR. He is antagonized by Dib, a young paranormal investigator who is determined to stop Zim with the help of his sister, Gaz. Vasquez pitched the series to Nickelodeon, who desired a series for their older demographic. This led to it being greenlit and premiering on March 30, 2001. As the series went on, ratings declined and...

ypercube

@swasheck Check the differences of the plans when the index is dropped: sqlfiddle.com/#!6/d0ebe/5

I'm not saying that the efficiency will be horrible but in some cases, the optimizer will choose the (col1) index over the (col1,col2) and the produced plan will differ, possibly in other aspects as well (merge joins vs nested loops, etc) due to this.

And what is "5 lakh" rows? Is that 5,000? — Max Vernon 2 mins ago

Max Vernon

15:54

@ypercube that's my new-and-improved community friendly self.

billinkc

Still not very sportsman like

I'M JUDGING YOU

Max Vernon

@billinkc ROTFLMAO

5 lakh is 500K. — ypercube 14 mins ago

so a tiny table, then.

Vérace

16:06

Lakh

A lakh or lac (/ˈlæk/ or /ˈlɑːk/; abbreviated L) is a unit in the Indian numbering system equal to one hundred thousand (100,000; scientific notation: 105). In the Indian convention of digit grouping, it is written as 1,00,000. For example, in India 150,000 rupees becomes 1.5 lakh rupees, written as ₹1,50,000 or INR 1,50,000. It is widely used both in official and other contexts in Bangladesh, India, Myanmar, Nepal, Pakistan, and Sri Lanka. It is often used in Indian, Pakistani, and Sri Lankan English. In Pakistan, the word lakh is used mostly in local languages rather than in English media. ...

ypercube

Crore

A crore (/ˈkrɔər/; abbreviated cr) is a unit in the Indian numbering system equal to ten million (10,000,000; in scientific notation: 107). It is widely used in India, Bangladesh, Pakistan, and Nepal, and is written in these regions as 1,00,00,000 with the local style of digit group separators. It is equal to a hundred lakh (a lakh is equal to one hundred thousand and is written as 1,00,000). == Usage == === Money === Large amounts of money in India are often written in terms of crores. For example, 100,000,000 (one hundred million) Indian rupees is written as 'ten crore rupees' or '₹10 crore' or...

for BigData ;)

Max Vernon

@ypercube sweeeet.

swasheck

my god, it's full of lakhs

ypercube

@MaxVernon Perhaps we should vote for an Indian mod, that knows these things ;)

Max Vernon

if only lmgtfy.com was acceptable in comments.

@ypercube I'm thinking that would be an excellent idea. We like being inclusive.

billinkc

16:17

@ypercube Only if they provide food for us

Max Vernon

@ypercube I removed my comment, by the way.

swasheck

@MaxVernon if i was mod i'd decline flags

dezso

@ypercube I usually see this like

> I have 5 lakh data

@MaxVernon sorry, I've annoyed the shog into banning it on all sites

Max Vernon

@swasheck I guess the problem with sarcasm is it doesn't always translate well. lmgtfy.com could also be taken as an insult if you don't understand the inflection present in typed-in comments.

@dezso its actually a good thing it is banned... along with a great many other things. It's just hard to be gracious all the time. Am I right, @billinkc?!

I hope if I get elected mod that I can live up to being half as nice as
@Aaron.

billinkc

I thought graciousness was a byproduct of being Canadian. Like always having a bottle of maple syrup on you

Max Vernon

16:29

@billinkc I also come from "Friendly Manitoba", so there!

billinkc

It's a pity you weren't from Sportsman Saskatoon

dezso

@MaxVernon the measure is @JackDouglas

Max Vernon

@dezso undoubtedly. Never heard a bad word come out of that mouth.

@dezso my comment was a rather poor attempt at a back-handed compliment.

swasheck

@MaxVernon point being? maybe it's being used as an invective against a lazy person

Max Vernon

@swasheck oh, clearly it typically is. That doesn't mean it's actually helpful long term.

swasheck

16:34

mmmmm. depends on how you measure helpfulness

looks like the house republicans have had enough of their boehner

it's lasted too long

billinkc

If your boehner lasts more than 4 years, consult your tea party

I do wish they'd get their act together and pass a six year highway trust fund. It's strangling the ability for cities and states to properly manage repairs

Senate, GG. House, be less dumb

swasheck

@billinkc he's really given congress a hard time

billinkc

The bureaucracy has tumesced under boehners steady hand

I think I've blown my wad for Boehner jokes

swasheck

now his own cronies are giving him the shaft

billinkc

His leadership has been rather flaccid of late

swasheck

16:46

when asked what they didn't like about Boehner, they talked about his wrinkly skin ... and that's just the tip (of the iceberg)

billinkc

Still, they should erect a monument as a testament to his forceful leadership

Lamak

you are forcing it, dude

swasheck

no. that's trump.

Lamak

nice

Max Vernon

in Database Administrators 2015 Moderator Election, 2 mins ago, by Kin

Just posted my nomination.

Mike Fal

16:59

@MaxVernon and @ShawnMelton should fight a cage match for ultimate victory.

Max Vernon

@MikeFal can I have Ronda Rousey on my side?

Mike Fal

@MaxVernon No subs

Mano e mano

Max Vernon

@MikeFal is tag-team acceptable ?

Mike Fal

Nope

:)

Lamak

what happened with the placeholder nomination?

bluefeet

17:04

@Lamak removed by SE

Lamak

I was gonna suggest that, didn't see the point of having a placeholder, since nominations are sorted random (I think)

ypercube

> All nominees are displayed in most recently nominated first order.

The order is random in next phase.

Lamak

@ypercube thanks for showing my ignorance. Anyway, at least it wasn't @billinkc who said it

billinkc

17:33

Dear Diary, billinkc didn't talk about me today. I even combed my hair and wore my favorite shirt but he just ignored me. I hate him so much - I just wish I didn't love him more than my hatred. It looks like my pillows will again be soaked in my tears.

4

Lamak

that's.....kinda disturbing

billinkc

Dude, it's your journal

0

Q: How Do I find what is populating a table?

I constantly run into this problem. I am working in a data warehouse and I cannot find out what is populating a table. Typically the table is being populated on a daily basis from either other table in the warehouse or from an Oracle database. I have tried the below query and can confirm the upda...

sql-server sql-server-2008 ssis

Short of running sp_whoisactive and dumping the results to a table, what else can they do?

Or deny permissions until someone complains

swasheck

or a profiler trace may help

Max Vernon

@billinkc Extended Events (may be tricky with SQL Server 2008)

swasheck

17:49

errrrr ... some unholy combination of these events?

database_transaction_begin
database_transaction_end
sql_statement_completed
sql_statement_starting

James Lupolt

18:26

@jcolebrand This also has some good, specific examples of situations where working with Mongo can be quite unpleasant and poorly suited to the task.

@jcolebrand One thing that bothered me about Mongo was the inconsistency between the DML methods. The update() method only updates one document by default, instead of every document that matches the predicate. The remove() method on the other hand removes all matching documents by default. With either, as I understand it, you only get document-level consistency, so it can fail halfway without modifying all qualifying documents.

There were also other weird footguns. For example, if I understood correctly, if you wrote a predicate that has multiple conditions on the same field without specifying AND or OR, the conditions overwrite each other and the last one to get evaluated wins.

Max Vernon

Now, MangoDB on the other hand suffers none of those problems. It just writes everything to /dev/nul and boy is it quick!

swasheck

i wonder if /dev/null is faster or if /tmp is faster

Andriy M

18:50

This isn't my area of expertise but the two questions at the end seem to me like they are about very different problems:

0

Q: Cluster Quorum Voting

I have a cluster of 4 SQL Servers setup. 2 of the servers are in RackSpace in Chicago. 1 is in Rackspace, in Reston, VA, and one is in our office in Greenville, SC. I want to change the cluster quorum, to to only allow the 2 servers in Chicago to control the cluster. I want to setup a File Share...

sql clustering

Do they sound related enough to you guys to be asked in the same post?

swasheck

two questions on the same topic.

Max Vernon

agreed

Andriy M

I can see two specific (sub)topics: 1) configuring (a shared folder for) a cluster and 2) cluster quorum voting best practices

swasheck

@AndriyM the only reason i'd say the same topic is that OP has specified that they want to use a FSW so 1&2 are more tightly-integrated.

Andriy M

@swasheck So they probably belong together because if e.g. the answer to the second one is "no", they won't need an answer to the first one, correct?

swasheck

19:06

not necessarily --- i'm just not sure how to make it better. i'd actually just remove the first question altogether. it's bad question. it's a lmgtfy question at best, but the exact error text has not been provided so we can only guess

i think a better question would be: here's my configuration and i'd like to make sure that the sql instances are all set up for the best possible HA coverage. here's my initial thought but i'm not 100% certain about a few of the details involved. how can i best ensure that i'm covered for the greatest HA coverage possible?

Andriy M

OK, I think I see. It's their first question, they are still learning the ropes of how to ask.

swasheck

yep.

clustering is where it's good to have a decent windows admin.

Max Vernon

@swasheck perhaps this question would be better suited to ServerFault

swasheck

perhaps. let's see how OP responds

James Lupolt

19:50

@AndriyM They're closely related because the fileshare they're talking about is one that only exists for cluster quorum. It's just phrased badly.

swasheck

mui merde

dezso

20:22

Why don't you see a question like 'My HHD crashed, and I have a backup from the morning, but I am wondering if there is a way to fix the following IO error: ...', ever?

0

Q: Restore Postgresql data after hdd crash

My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this: pg_dump: Error message from server: ERROR: could not read block 2 of relation base/16384/2607: IO error pg_dump: The command was:...

postgresql backup restore crash postgresql-8.4

billinkc

New rule, all database installers must take daily full backups upon creation of a database until an actual backup plan is implemented. It will overwrite the previous backup to keep them from running out of disk space

dezso

@billinkc only if the backup is restorable?

Mike Fal

Eh? Is this something you have to do or something you want?

billinkc

Something I think database platforms, especially the free ones, ought to think about.

Yes, people should RTFM and be severely beaten with a clue-by-four but how many forums posts are out there with "I accidentally the database"

The backup never taken is guaranteed to not be restorable

Also, it's 2015 and I'm dealing with a table called Logins with a column of Password. varchar(100).

...

Mike Fal

20:40

If the backup taken is never guaranteed restorable, why take it? Backups are only valuable if you can restore from them.

billinkc

I guess I see it that if people don't know the basics, at least give them the glimmer of hope that they might not be humped when they do the megadumb

Mike Fal

But that's just it, if you give them a database that performs a non-usable backup (i.e. Backup up /dev/null), you're actually making it worse for them.

I'd be more in favor of some sort of default alert or trigger that checks for backups.

dezso

this argument reminds me a classic Hungarian Jewish joke

Grün comes to the rabbi because he has an argument with Kohn, and they cannot agree since a while. He tells his story, and the rabbi, after carefully listening, says him 'You are right, my son'. The next day comes Kohn, tells the rabbi his version, and the rabbi, again after carefully listening, syas him, too: 'You are right, my son.'
The bocher, who heard both cases, tells the rabbi 'But rabbi, it is impossible that both of them are right!' The rabbi, after pondering it for a short while, says him: 'You know what, my son? You are also right!'

billinkc

21:00

Except in this case, like every other case, I'm right

Andriy M

@JamesLupolt Got it, thanks.

dezso

BTW, who did remove the funny guy from the election?

Max Vernon

21:24

an SE moderator removed it

James Lupolt

21:36

Anyone else going to PASS this year?

billinkc

<---

James Lupolt

ozar.me/2015/07/…

I'll buy you a beer

billinkc

Only if I can return the favour

James Lupolt

Is Brent right? Is PASS a total madhouse and everyone is too busy to talk for more than a few minutes?

billinkc

He's not presenting, but not sure if that means he's not attending

ozar.me/2015/07/…

James Lupolt

21:43

@billinkc He says he isn't. Intersection is actually the same week, which seems like a pretty bold move.

mmarie

@JamesLupolt Me

@JamesLupolt No. It's like that if all the people you want to talk to are on the board and are organizing the event. But I had plenty of good long conversations last year.

James Lupolt

@mmarie Good to know. It will be nice to get to meet all of you, and actually get to talk to you as well. : )

mmarie

In fact, I was standing in a group of people/SQL Saturday friends chatting, and then later went to lunch with them. I told them I was looking for a new job and a month later I was working with them.

Max Vernon

@JamesLupolt - I'll be at PASS Summit... Should be a great time!

billinkc

@mmarie The pizza is much better than the calzones at The Alibi room. Service still sucked though when we went back last week

mmarie

21:54

@billinkc Meh. Not worth a return trip.

Transcript for

The Heap™ – Consultancy ©®