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

ypercube
ypercube
10:16 AM
1
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...

sql-server c# generate-script recursive
An interesting problem but probably too much for a dba.se question.
 
Tom V
Tom V
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
 
ypercube
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
Tom V
@ypercube I was under the impression he needed one large result set
so this is what I had in mind
 
ypercube
ypercube
I read the comment where they say "a lightweight db export wich will export only the data we need ..."
 
Tom V
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
Mark Storey-Smith
wassup
 
 
2 hours later…
Tom V
Tom V
12:57 PM
I guess someone peed in this guy's cornflakes this morning
 
Paul White
Paul White
1:10 PM
Yep.
 
mmarie
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
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
billinkc
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
 
ypercube
ypercube
2:23 PM
@billinkc a steaming pile of Enterprise feces ;)
 
billinkc
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?

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

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

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

sql-server ssis reporting-services
 
swasheck
swasheck
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?
 
Tom V
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
swasheck
that's my thought as well ... using the phone book analogy it makes sense to me
 
dezso
dezso
@swasheck unless col2 is something really wide, I wouldn't expect much difference between queries served by either one
 
ypercube
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
dezso
meaning the second can be dropped
except cases
 
swasheck
swasheck
2:55 PM
@ypercube hm. tell me more ... i'm slow. you mean a single-column join?
 
ypercube
ypercube
I mean a join that uses the table's CI column.
 
swasheck
swasheck
ok
 
ypercube
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
swasheck
clustered is ...
create clustered index on dbo.table1(col0,col3)
pseudocode, natch
sounds like microsoft had some news today
 
Tom V
Tom V
@swasheck win10 release?
 
swasheck
swasheck
3:09 PM
yeah. just a quick little joke
 
dezso
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
billinkc
You'd be wrong
 
swasheck
swasheck
RONG
 
dezso
dezso
you are wrong
furthermore, your link doesn't produce a sound for me
 
billinkc
billinkc
you're browsur is wrang
 
dezso
dezso
3:21 PM
boss of zem
 
swasheck
swasheck
RONGE
BRIYOHN
 
billinkc
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
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
Max Vernon
3:54 PM
@ypercube that's my new-and-improved community friendly self.
 
billinkc
billinkc
Still not very sportsman like
I'M JUDGING YOU
 
Max Vernon
Max Vernon
@billinkc ROTFLMAO
5 lakh is 500K. — ypercube 14 mins ago
so a tiny table, then.
 
Vérace
Vérace
4:06 PM
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
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
Max Vernon
@ypercube sweeeet.
 
swasheck
swasheck
my god, it's full of lakhs
 
ypercube
ypercube
@MaxVernon Perhaps we should vote for an Indian mod, that knows these things ;)
 
Max Vernon
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
billinkc
4:17 PM
@ypercube Only if they provide food for us
 
Max Vernon
Max Vernon
@ypercube I removed my comment, by the way.
 
swasheck
swasheck
@MaxVernon if i was mod i'd decline flags
 
dezso
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
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
billinkc
I thought graciousness was a byproduct of being Canadian. Like always having a bottle of maple syrup on you
 
Max Vernon
Max Vernon
4:29 PM
@billinkc I also come from "Friendly Manitoba", so there!
 
billinkc
billinkc
It's a pity you weren't from Sportsman Saskatoon
 
dezso
dezso
@MaxVernon the measure is @JackDouglas
 
Max Vernon
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
swasheck
@MaxVernon point being? maybe it's being used as an invective against a lazy person
 
Max Vernon
Max Vernon
@swasheck oh, clearly it typically is. That doesn't mean it's actually helpful long term.
 
swasheck
swasheck
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
 
billinkc
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
swasheck
@billinkc he's really given congress a hard time
 
billinkc
billinkc
The bureaucracy has tumesced under boehners steady hand
I think I've blown my wad for Boehner jokes
 
swasheck
swasheck
now his own cronies are giving him the shaft
 
billinkc
billinkc
His leadership has been rather flaccid of late
 
swasheck
swasheck
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)
 
billinkc
billinkc
Still, they should erect a monument as a testament to his forceful leadership
 
Lamak
Lamak
you are forcing it, dude
 
swasheck
swasheck
no. that's trump.
 
Lamak
Lamak
nice
 
Max Vernon
Max Vernon
 
Mike Fal
Mike Fal
4:59 PM
@MaxVernon and @ShawnMelton should fight a cage match for ultimate victory.
 
Max Vernon
Max Vernon
@MikeFal can I have Ronda Rousey on my side?
 
Mike Fal
Mike Fal
@MaxVernon No subs
Mano e mano
 
Max Vernon
Max Vernon
@MikeFal is tag-team acceptable ?
 
Mike Fal
Mike Fal
Nope
:)
 
Lamak
Lamak
what happened with the placeholder nomination?
 
bluefeet
bluefeet
5:04 PM
@Lamak removed by SE
 
Lamak
Lamak
I was gonna suggest that, didn't see the point of having a placeholder, since nominations are sorted random (I think)
 
ypercube
ypercube
> All nominees are displayed in most recently nominated first order.
The order is random in next phase.
 
Lamak
Lamak
@ypercube thanks for showing my ignorance. Anyway, at least it wasn't @billinkc who said it
 
billinkc
billinkc
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.
4
 
Lamak
Lamak
that's.....kinda disturbing
 
billinkc
billinkc
Dude, it's your journal
0
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...

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
swasheck
or a profiler trace may help
 
Max Vernon
Max Vernon
@billinkc Extended Events (may be tricky with SQL Server 2008)
 
swasheck
swasheck
5:49 PM
errrrr ... some unholy combination of these events?
database_transaction_begin
database_transaction_end
sql_statement_completed
sql_statement_starting
 
James Lupolt
James Lupolt
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.
 
Max Vernon
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
swasheck
i wonder if /dev/null is faster or if /tmp is faster
 
Andriy M
Andriy M
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:
0
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...

sql clustering
Do they sound related enough to you guys to be asked in the same post?
 
swasheck
swasheck
two questions on the same topic.
 
Max Vernon
Max Vernon
agreed
 
Andriy M
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
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
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
swasheck
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?
 
Andriy M
Andriy M
OK, I think I see. It's their first question, they are still learning the ropes of how to ask.
 
swasheck
swasheck
yep.
clustering is where it's good to have a decent windows admin.
 
Max Vernon
Max Vernon
@swasheck perhaps this question would be better suited to ServerFault
 
swasheck
swasheck
perhaps. let's see how OP responds
 
James Lupolt
James Lupolt
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.
 
swasheck
swasheck
mui merde
 
dezso
dezso
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?
0
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:...

postgresql backup restore crash postgresql-8.4
 
billinkc
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
dezso
@billinkc only if the backup is restorable?
 
Mike Fal
Mike Fal
Eh? Is this something you have to do or something you want?
 
billinkc
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
Mike Fal
8:40 PM
If the backup taken is never guaranteed restorable, why take it? Backups are only valuable if you can restore from them.
 
billinkc
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
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
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
billinkc
9:00 PM
Except in this case, like every other case, I'm right
 
Andriy M
Andriy M
@JamesLupolt Got it, thanks.
 
dezso
dezso
BTW, who did remove the funny guy from the election?
 
Max Vernon
Max Vernon
9:24 PM
an SE moderator removed it
 
James Lupolt
James Lupolt
9:36 PM
Anyone else going to PASS this year?
 
billinkc
billinkc
<---
 
James Lupolt
James Lupolt
I'll buy you a beer
 
billinkc
billinkc
Only if I can return the favour
 
James Lupolt
James Lupolt
Is Brent right? Is PASS a total madhouse and everyone is too busy to talk for more than a few minutes?
 
billinkc
billinkc
He's not presenting, but not sure if that means he's not attending
 
James Lupolt
James Lupolt
9:43 PM
@billinkc He says he isn't. Intersection is actually the same week, which seems like a pretty bold move.
 
mmarie
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
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
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
Max Vernon
@JamesLupolt - I'll be at PASS Summit... Should be a great time!
 
billinkc
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
mmarie
9:54 PM
@billinkc Meh. Not worth a return trip.
 

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