« first day (1667 days earlier)      last day (1671 days later) » 

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

probirdI'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.
10:29 AM
@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
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.
@ypercube I was under the impression he needed one large result set
so this is what I had in mind
I read the comment where they say "a lightweight db export wich will export only the data we need ..."
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 :)
2 hours later…
12:57 PM
I guess someone peed in this guy's cornflakes this morning
1:10 PM
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.
@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)
2:11 PM
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
2:23 PM
@billinkc a steaming pile of Enterprise feces ;)
Probably Greek feces. They seem to be the originators of all the great works
of poop
Feel free to Close this one
Q: What are the advantages of using an web services over SSIS Package?

pikachuThis 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...

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

SivajithI 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...

2:40 PM
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?
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
that's my thought as well ... using the phone book analogy it makes sense to me
@swasheck unless col2 is something really wide, I wouldn't expect much difference between queries served by either one
@swasheck I think some queries with this condition and joins to other tables will prefer the second index - and do the join more efficiently.
meaning the second can be dropped
except cases
2:55 PM
@ypercube hm. tell me more ... i'm slow. you mean a single-column join?
I mean a join that uses the table's CI column.
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.
clustered is ...
create clustered index on dbo.table1(col0,col3)
pseudocode, natch
sounds like microsoft had some news today
@swasheck win10 release?
3:09 PM
yeah. just a quick little joke
@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
you are wrong
furthermore, your link doesn't produce a sound for me
you're browsur is wrang
3:21 PM
boss of zem
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...
@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
3:54 PM
@ypercube that's my new-and-improved community friendly self.
Still not very sportsman like
@billinkc ROTFLMAO
5 lakh is 500K. — ypercube 14 mins ago
so a tiny table, then.
4:06 PM
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. ...
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 ;)
@ypercube sweeeet.
my god, it's full of lakhs
@MaxVernon Perhaps we should vote for an Indian mod, that knows these things ;)
if only lmgtfy.com was acceptable in comments.
@ypercube I'm thinking that would be an excellent idea. We like being inclusive.
4:17 PM
@ypercube Only if they provide food for us
@ypercube I removed my comment, by the way.
@MaxVernon if i was mod i'd decline flags
@ypercube I usually see this like
> I have 5 lakh data
@MaxVernon sorry, I've annoyed the shog into banning it on all sites
@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
I thought graciousness was a byproduct of being Canadian. Like always having a bottle of maple syrup on you
4:29 PM
@billinkc I also come from "Friendly Manitoba", so there!
It's a pity you weren't from Sportsman Saskatoon
@MaxVernon the measure is @JackDouglas
@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.
@MaxVernon point being? maybe it's being used as an invective against a lazy person
@swasheck oh, clearly it typically is. That doesn't mean it's actually helpful long term.
4:34 PM
mmmmm. depends on how you measure helpfulness
looks like the house republicans have had enough of their boehner
it's lasted too long
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
@billinkc he's really given congress a hard time
The bureaucracy has tumesced under boehners steady hand
I think I've blown my wad for Boehner jokes
now his own cronies are giving him the shaft
His leadership has been rather flaccid of late
4:46 PM
when asked what they didn't like about Boehner, they talked about his wrinkly skin ... and that's just the tip (of the iceberg)
Still, they should erect a monument as a testament to his forceful leadership
you are forcing it, dude
no. that's trump.
4:59 PM
@MaxVernon and @ShawnMelton should fight a cage match for ultimate victory.
@MikeFal can I have Ronda Rousey on my side?
@MaxVernon No subs
Mano e mano
@MikeFal is tag-team acceptable ?
what happened with the placeholder nomination?
5:04 PM
@Lamak removed by SE
I was gonna suggest that, didn't see the point of having a placeholder, since nominations are sorted random (I think)
> All nominees are displayed in most recently nominated first order.
The order is random in next phase.
@ypercube thanks for showing my ignorance. Anyway, at least it wasn't @billinkc who said it
5:33 PM
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.
that's.....kinda disturbing
Dude, it's your journal
Q: How Do I find what is populating a table?

Holmes IVI 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...

Short of running sp_whoisactive and dumping the results to a table, what else can they do?
Or deny permissions until someone complains
or a profiler trace may help
@billinkc Extended Events (may be tricky with SQL Server 2008)
5:49 PM
errrrr ... some unholy combination of these events?
6:26 PM
@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.
Now, MangoDB on the other hand suffers none of those problems. It just writes everything to /dev/nul and boy is it quick!
i wonder if /dev/null is faster or if /tmp is faster
6:50 PM
This isn't my area of expertise but the two questions at the end seem to me like they are about very different problems:
Q: Cluster Quorum Voting

Clifton DunawayI 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...

Do they sound related enough to you guys to be asked in the same post?
two questions on the same topic.
I can see two specific (sub)topics: 1) configuring (a shared folder for) a cluster and 2) cluster quorum voting best practices
@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.
@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?
7:06 PM
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?
OK, I think I see. It's their first question, they are still learning the ropes of how to ask.
clustering is where it's good to have a decent windows admin.
@swasheck perhaps this question would be better suited to ServerFault
perhaps. let's see how OP responds
7:50 PM
@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.
mui merde
8:22 PM
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?
Q: Restore Postgresql data after hdd crash

AlexMy 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:...

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
@billinkc only if the backup is restorable?
Eh? Is this something you have to do or something you want?
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).
8:40 PM
If the backup taken is never guaranteed restorable, why take it? Backups are only valuable if you can restore from them.
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
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.
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!'
9:00 PM
Except in this case, like every other case, I'm right
@JamesLupolt Got it, thanks.
BTW, who did remove the funny guy from the election?
9:24 PM
an SE moderator removed it
9:36 PM
Anyone else going to PASS this year?
I'll buy you a beer
Only if I can return the favour
Is Brent right? Is PASS a total madhouse and everyone is too busy to talk for more than a few minutes?
He's not presenting, but not sure if that means he's not attending
9:43 PM
@billinkc He says he isn't. Intersection is actually the same week, which seems like a pretty bold move.
@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.
@mmarie Good to know. It will be nice to get to meet all of you, and actually get to talk to you as well. : )
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.
@JamesLupolt - I'll be at PASS Summit... Should be a great time!
@mmarie The pizza is much better than the calzones at The Alibi room. Service still sucked though when we went back last week
9:54 PM
@billinkc Meh. Not worth a return trip.

« first day (1667 days earlier)      last day (1671 days later) »