March 11, 2013 at 6:06 am
SQLRNNR (3/10/2013)
Lynn Pettis (3/10/2013)
GilaMonster (3/10/2013)
Since it's the msdb job tables, it's a bit of a stretch to say we don't have data. That said, that had a feel of 'do this for me'.Not a strech for me at all. I have no scheduled jobs on my computer here at home, so no data to work with. My development VM at work has no scheduled jobs, so again no data. The production systems are in theater (such as Afghanistan) on secure networks, so again, no data.
This is one case where the request for sample data is unnecessary imho. The request wasn't about any scheduled jobs in particular but more about figuring out how to navigate system objects in msdb. To create test data is easy enough if a "test" job doesn't already exist. Then again, I may be accustomed to doing that on a regular basis for a demo.
Ok... curiosity is finely getting the best of me on this particular thread. I realize the link for this was probably posted but could someone post the link so I don't have to wade through all that has been since then? Thanks, folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2013 at 6:23 am
Lynn Pettis (3/11/2013)
What's wrong with expecting someone who is asking for help with a query to post sample data (regardless of the source of the data)..
I don't think there was anything wrong in you asking for sample data expecially as you explained the reason why.
I was fortunate where I have a system that had job data to test the query. I do have systems where this data is absent.
What I find frustrating is where OPs are asked continuously asked for DDL, test data and expected results and nothing is posted or even worse it is done it is done in bits and pieces and looks nothing like the original question was asked about :doze:
Far away is close at hand in the images of elsewhere.
Anon.
March 11, 2013 at 6:27 am
Lynn Pettis (3/11/2013)
Lynn Pettis (3/10/2013)
Okay, first, I am not apologizing for what or how I said it. I am curious, however, if anyone thinks I went to far here.Use the hyperlink in the quote.
Thanks, Lynn.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2013 at 8:10 am
Jeff Moden (3/10/2013)
My take on it is that just because millions of people are using it, I don't have to think they're right. Unfortunately, because there are millions of people using it, I do have to put up with using it on occasion. 😀
I think you missed out the three letters "mis" twice there, Jeff. It fits right before each occurrence of "using".
XML is rather complex and extremely verbose, and it has made quite a mess of muddling data types and structures; but its biggest problem is not a technical one, it is that people will insist on using it (because XML is cool, fashionable, isn't it, and that's far more important than whether it's the right tool for the job) in contexts where it is utterly inappropriate, such as where one cares about parallelism and doesn't want everything serialized. It suffers from misuse caused by two sorts of user stupidity: sledgehammer to crack nut and butter knife to fell trees. Using it as an extensible markup language to handle things well that used to be handled badly (or unhandlable) in SGML - which I think is where it all started a long time ago - is sensible, and will often lead to good results. Using it, for example, to try to define the syntax of a declarative language, of even to specify data type definition for a procedural language it, or to convert data between two formats each involving complex structure and data types, is serious misuse and will usually lead to extremely unsatisfactory results, if not to total failure.
It really is a "use the tool where it fits" issue, but the world at large appears clueless as to where it fits. I've seen it misused so much that I hate it, but I know that that prejudices me when considering whether it's appropriate for something I'm doing and try to discount that hate.
Tom
March 11, 2013 at 8:18 am
Jeff Moden (3/10/2013)
Matt Miller (#4) (3/9/2013)
Jeff Moden (3/8/2013)
Matt Miller (#4) (3/8/2013)
Jeff Moden (3/7/2013)
Revenant (3/4/2013)
Evil Kraig F (3/4/2013)
Revenant (3/4/2013)
I always find it frustrating when someone asks me or someone else to do things for which T-SQL was not meant, in this case parsing multilevel XML with multiple occurrences of the same tag, yet prohibits use of a tool that would make it a breeze - in this case XML LINQ via CLR Integration.Sorry Revenant. Don't mean to be the cause of your frustration in this case. Clean CLR isn't what scares my DBA's, it's letting my app coders loose with it.
It was not meant at you, Craig. I have been in that situation and I know how it feels.
I feel the same way... I just can't understand why anyone would actually use XML. 😉
Well I'd say XML would be like a lot of other items -it's a tool in the arsenal. Use it correctly and it can be VERY useful; use it badly or in the wrong context, and it blows up in your face.
Kind of like any other tool.
I strongly agree with the "right tool" sentiment. I just don't agree that XML is the right tool for the transmittal of data destined for a relational database. It seems like using a 100 ton crane to turn a monkey wrench especially when it's used to transmit otherwise flat data for a single database entity. Since I'm mostly a data troll, I've not read up on JSON but if it's a markup language that requires description tags for each row or each element, I'm not going to be very happy with that for data transmission, either.
Well in our case it's more like we're moving the 100-ton crane, dissassembling it in transit and making a whole bunch of 1-ton tractors along the way:). The XML is very useful in that, because it allows us some sane way to maintain the relations while the data is in transit, and still have some control over data types etc.... Besides - we leverage an industry-specific interchange spec which was specified using XML, so it also opens us up to all sorts of pre-made tools to help us.
I get all of that especially when someone makes a "standard". Heh... EDI is another "standard" (which I also dislike only worse than XML) that does the type of thing you seem to be doing. With the understanding that I don't know what the actual complexities of what you've need to do actually are, there are some pretty simple ways to do some pretty complex things. XML certainly does what you want it to but it reminds me of buying a stick of gum and carrying it home in a full size shopping bag.
My take on it is that just because millions of people are using it, I don't have to think they're right. Unfortunately, because there are millions of people using it, I do have to put up with using it on occasion. 😀
I agree that XML inbound to the database is a pain and not the best way to do things in many cases. Fortunately, our app developers get this and happily write the code to parse XML in the application layer before sending data to the database.
On the other hand, the ability to package outbound data as XML has been a boon to us. Our applications generally expect to receive one row per entity when their queries return more than one entity. As we've added datasets to our repository, though, we now have situations where one entity may have several values for a particular attribute (for example, a single address usually falls within several school attendance zones - one for elementary, one for middle, one for high school, etc.). Rather than pivoting these values into a single row (and all the hassle of either anticipating the number of values to be pivoted or doing some kind of dynamic pivot) or returning multiple rows per entity (with all the redundancy in the other columns), a FOR XML subquery returns this data as a single XML column no matter how many values there may be. Our app developers are well-versed in handling XML, so our applications readily consume it.
Jason Wolfkill
March 11, 2013 at 8:23 am
SQLRNNR (3/10/2013)
Lynn Pettis (3/10/2013)
GilaMonster (3/10/2013)
Since it's the msdb job tables, it's a bit of a stretch to say we don't have data. That said, that had a feel of 'do this for me'.Not a strech for me at all. I have no scheduled jobs on my computer here at home, so no data to work with. My development VM at work has no scheduled jobs, so again no data. The production systems are in theater (such as Afghanistan) on secure networks, so again, no data.
This is one case where the request for sample data is unnecessary imho. The request wasn't about any scheduled jobs in particular but more about figuring out how to navigate system objects in msdb. To create test data is easy enough if a "test" job doesn't already exist. Then again, I may be accustomed to doing that on a regular basis for a demo.
I don't agree. Sysjobhistory has some bizarrely structured fields in it, and unless you have some data plus the logs from the job runs to help you work out what the format actually is you are lost because some are not documented. Yes, BOL gives us the format of run_duration (which is certainly one of the bizarre ones) but the format is not documented either for run_date or for run_time (both are integers, no other information provided). I knew what the formats were in SQL 2000 because I spent time working it out a decade ago, but I don't know whether they are still the same now and I'm not even sure I remember them correctly.
Tom
March 11, 2013 at 8:23 am
Jeff Moden (3/10/2013)
Stefan Krzywicki (3/9/2013)
Jeff Moden (3/8/2013)
Matt Miller (#4) (3/8/2013)
Jeff Moden (3/7/2013)
Revenant (3/4/2013)
Evil Kraig F (3/4/2013)
Revenant (3/4/2013)
I always find it frustrating when someone asks me or someone else to do things for which T-SQL was not meant, in this case parsing multilevel XML with multiple occurrences of the same tag, yet prohibits use of a tool that would make it a breeze - in this case XML LINQ via CLR Integration.Sorry Revenant. Don't mean to be the cause of your frustration in this case. Clean CLR isn't what scares my DBA's, it's letting my app coders loose with it.
It was not meant at you, Craig. I have been in that situation and I know how it feels.
I feel the same way... I just can't understand why anyone would actually use XML. 😉
Well I'd say XML would be like a lot of other items -it's a tool in the arsenal. Use it correctly and it can be VERY useful; use it badly or in the wrong context, and it blows up in your face.
Kind of like any other tool.
I strongly agree with the "right tool" sentiment. I just don't agree that XML is the right tool for the transmittal of data destined for a relational database. It seems like using a 100 ton crane to turn a monkey wrench especially when it's used to transmit otherwise flat data for a single database entity. Since I'm mostly a data troll, I've not read up on JSON but if it's a markup language that requires description tags for each row or each element, I'm not going to be very happy with that for data transmission, either.
It is, but the construction means that the same size of data fits in a file 2/3 the size of an XML file and it is directly queryable which makes it good for client-side apps.
Since you're able to directly query XML, as well, I'm not sure that's an advantage over XML. Based on what you said about JSON taking only 2/3rds the space of XML, I'll say they're headed in the right direction but it's still bloated compared to more conventional means.
I don't really have a dog in the XML/JSON fight, and I know very little about JSON, but our app developers seem to be using it more and more. The only impact it has on me is because my first name is Jason - every time the app devs in our bullpen start talking about JSON (they pronounce it "Jay - sahn"), that little part of my brain that handles the "did I hear my name?" reflex gets activated. I think I'm slowly becoming accustomed to it and tune it out more readily, but I would be distracted constantly when the app devs first started having these conversations.
Jason Wolfkill
March 11, 2013 at 8:28 am
Jeff Moden (3/10/2013)
dwain.c (3/10/2013)
Jeff Moden (3/8/2013)
...Since I'm mostly a data troll...Does that mean you live under a bridge on Al Gore's information superhighway?
Nah... I've gone wireless. 😛
That ancient laptop of yours does wireless? Dang... how... modern of you
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2013 at 8:58 am
wolfkillj (3/11/2013)
Jeff Moden (3/10/2013)
Stefan Krzywicki (3/9/2013)
Jeff Moden (3/8/2013)
Matt Miller (#4) (3/8/2013)
Jeff Moden (3/7/2013)
Revenant (3/4/2013)
Evil Kraig F (3/4/2013)
Revenant (3/4/2013)
I always find it frustrating when someone asks me or someone else to do things for which T-SQL was not meant, in this case parsing multilevel XML with multiple occurrences of the same tag, yet prohibits use of a tool that would make it a breeze - in this case XML LINQ via CLR Integration.Sorry Revenant. Don't mean to be the cause of your frustration in this case. Clean CLR isn't what scares my DBA's, it's letting my app coders loose with it.
It was not meant at you, Craig. I have been in that situation and I know how it feels.
I feel the same way... I just can't understand why anyone would actually use XML. 😉
Well I'd say XML would be like a lot of other items -it's a tool in the arsenal. Use it correctly and it can be VERY useful; use it badly or in the wrong context, and it blows up in your face.
Kind of like any other tool.
I strongly agree with the "right tool" sentiment. I just don't agree that XML is the right tool for the transmittal of data destined for a relational database. It seems like using a 100 ton crane to turn a monkey wrench especially when it's used to transmit otherwise flat data for a single database entity. Since I'm mostly a data troll, I've not read up on JSON but if it's a markup language that requires description tags for each row or each element, I'm not going to be very happy with that for data transmission, either.
It is, but the construction means that the same size of data fits in a file 2/3 the size of an XML file and it is directly queryable which makes it good for client-side apps.
Since you're able to directly query XML, as well, I'm not sure that's an advantage over XML. Based on what you said about JSON taking only 2/3rds the space of XML, I'll say they're headed in the right direction but it's still bloated compared to more conventional means.
I don't really have a dog in the XML/JSON fight, and I know very little about JSON, but our app developers seem to be using it more and more. The only impact it has on me is because my first name is Jason - every time the app devs in our bullpen start talking about JSON (they pronounce it "Jay - sahn"), that little part of my brain that handles the "did I hear my name?" reflex gets activated. I think I'm slowly becoming accustomed to it and tune it out more readily, but I would be distracted constantly when the app devs first started having these conversations.
Yeah, my brother's name is Jason. I have a similar problem.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
March 12, 2013 at 6:21 am
L' Eomot Inversé (3/11/2013)
Jeff Moden (3/10/2013)
My take on it is that just because millions of people are using it, I don't have to think they're right. Unfortunately, because there are millions of people using it, I do have to put up with using it on occasion. 😀I think you missed out the three letters "mis" twice there, Jeff. It fits right before each occurrence of "using".
XML is rather complex and extremely verbose, and it has made quite a mess of muddling data types and structures; but its biggest problem is not a technical one, it is that people will insist on using it (because XML is cool, fashionable, isn't it, and that's far more important than whether it's the right tool for the job) in contexts where it is utterly inappropriate, such as where one cares about parallelism and doesn't want everything serialized. It suffers from misuse caused by two sorts of user stupidity: sledgehammer to crack nut and butter knife to fell trees. Using it as an extensible markup language to handle things well that used to be handled badly (or unhandlable) in SGML - which I think is where it all started a long time ago - is sensible, and will often lead to good results. Using it, for example, to try to define the syntax of a declarative language, of even to specify data type definition for a procedural language it, or to convert data between two formats each involving complex structure and data types, is serious misuse and will usually lead to extremely unsatisfactory results, if not to total failure.
It really is a "use the tool where it fits" issue, but the world at large appears clueless as to where it fits. I've seen it misused so much that I hate it, but I know that that prejudices me when considering whether it's appropriate for something I'm doing and try to discount that hate.
I have the same understanding of the original premise of XML (simplified SGML that is supposed to promote human-readability within extensible documents), but fail to see how this extends the "semantic web" idea, which is what it was supposed to do. At least, I don't understand how stuffing *everything* into XML makes searching for anything easier.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 12, 2013 at 6:23 am
WayneS (3/11/2013)
Jeff Moden (3/10/2013)
dwain.c (3/10/2013)
Jeff Moden (3/8/2013)
...Since I'm mostly a data troll...Does that mean you live under a bridge on Al Gore's information superhighway?
Nah... I've gone wireless. 😛
That ancient laptop of yours does wireless? Dang... how... modern of you
It' a VLA (Very Large Abacus) 😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 12, 2013 at 6:25 am
haha - listening to a Fats Waller cut of "I'm crazy bout my baby/my baby's crazy bout me", he wraps up with "I'm exasperated by my offspring/my offspring is exasperated by me"
wordplay, how do I love thee...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 12, 2013 at 8:41 am
Where have all the crickets gone?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2013 at 8:45 am
SQLRNNR (3/12/2013)
Where have all the crickets gone?
Long time passing?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 39,106 through 39,120 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply