May 15, 2011 at 11:37 pm
Jeff Moden (5/15/2011)
ChrisM@home (5/2/2011)
This is probably the simplest set-based solution.Oh... be careful, Chris. Apologies for quibbling but a Recursive CTE isn't set-based. It's a form of hidden RBAR which can sometimes be worse than a WHILE loop. It IS a simple solution but it's not a Set-Based one.
No worries Jeff, thanks for picking this up. It pays to pick one's word carefully - perhaps I should have said "without using an explicit cursor".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 16, 2011 at 7:33 am
Jeff Moden (5/15/2011)
TheSQLGuru (5/3/2011)
That is by far the biggest shortcoming SQL Server has IMNSHO.Unless you count the fact that there is no split function, no function to generate sequential numbers or dates on the fly, no method to do a Bulk Export directly from T-SQL, the fact that PIVOT sucks compared to the PIVOT available even in Access, that no function that approaches the utility of the deprecated sp_MakeWebTask exists, or the fact that they removed the functionality of the old F4 key, or the fact that you can't work in SSMS while the query designer is open and you can print from it, or the fact that neither BCP or BULK INSERT will successfully skip rows unless they have exactly the same number and type of delimiters, or that T-SQL has no file handling capabilities, or the fact that RAND produces the same value across multiple rows, or that... etc, etc, etc. 😉
1) don't need a split because have that in both CLR and very efficient TSQL code available online.
2) ROW_NUMBER() is quite capable of generating sequential numbers on the fly, as are other TSQL snippets.
3) bcp out anyone??
4) I much prefer CASE for PIVOTing 🙂 But I agree there could be better functionality here. PowerPivot maybe? hehehe
5) you are honestly the first person I have ever come across that used sp_MakeWebTask. But per 2005 BOL: "New Web pages are more easily created using Microsoft SQL Server 2005 Reporting Services (SSRS)." 😀
6) I still use SSMS 2005 the vast majority of the time, so I have F4. Plus the GUI stuff will be SOOOO much better in Denali, right?!?! :w00t:
7) PLEASE don't tell me you actually use the query designer?
8) won't get much if any improvements in BCP/BULK INSERT with SSIS taking over those roles. Other work-arounds for your need to skip rows too.
9) I have no problem with TSQL not having file handling capabilities. And that is essentially not true and hasn't been for ages. XPs could accomplish that and CLR has supplanted that functionality.
10) I can cound on 1 hand the number of times I have used a RAND in production TSQL code. There are CLR alternatives for that too I imagine.
Sorry, still see windowing functions a far and away the most important missing thing in the SQL Server infrastructure. For my entire database career spanning over 15 years I have NEVER been with a client that didn't have a need for efficient solutions provided by various windowing functions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 16, 2011 at 9:35 am
3) bcp out anyone??
While "bcp out" is possible, it is not directly usable from within T-SQL. SQL Agent jobs and xp_cmdshell do not count because those are still a shell away. Even if they did count they are not very good options. SQL Agent jobs don't make dynamic behavior easy and enabling and using xp_cmdshell increases the surface area of the instance and is not desirable or supported in many environments.
Personally I like to export data using external connections via SSIS, bcp or sqlcmd (preferrably onto an application server separate from the SQL instance) however SQL Server would be "more complete" IMHO with a T-SQL "BULK EXPORT" command. You can vote for one here:
https://connect.microsoft.com/SQLServer/feedback/details/224026/sql-engine-bulk-export-command
9) I have no problem with TSQL not having file handling capabilities. And that is essentially not true and hasn't been for ages. XPs could accomplish that and CLR has supplanted that functionality
I prefer not to do these kinds of things on my SQL Server (refer to my earlier comment about where I prefer to export data to) but I currently support code I inherited that uses xp_cmdshell to get directory listings, write text to log files and do copy/move/delete file operations that I am looking at converting to CLR methods so we can eventually disable xp_cmdshell. This library looks like a pretty good place to start:
http://filesystemhelper.codeplex.com
I found it a few weeks ago and have not had a chance to give it a spin but I intend to. Does anyone have feedback on it or an alternative?
Sorry, still see windowing functions a far and away the most important missing thing in the SQL Server infrastructure.
If you visit this site and click on the "Suggestions" tab you'll see a bunch of new Windowing item suggestions that look useful:
https://connect.microsoft.com/SQLServer/SearchResults.aspx?UserHandle=Itzik+Ben-Gan
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2011 at 10:22 am
opc.three (5/16/2011)
...SQL Agent jobs don't make dynamic behavior easy...
I actually use SQL Agent jobs dynamically as a method of executing background tasks from a web application - where the user should not have to wait for the result.
They are created dynamically using a stored proc and delete themselves when they complete successfully.
I can query the progress - either directly checking the results of the job or just checking the agent job status and display the job status to the user if required...
I guess the creation of the stored proc took a few hours but now it is as easy as having my code call the stored proc with a query to run - (there is no way for the user to influence to sql passed to the stored proc - so protected from injection in this case).
I would say SQL Agent jobs are, for me at least, very easy to use for dynamic tasks.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 16, 2011 at 10:44 am
mister.magoo (5/16/2011)
opc.three (5/16/2011)
...SQL Agent jobs don't make dynamic behavior easy...
I actually use SQL Agent jobs dynamically as a method of executing background tasks from a web application - where the user should not have to wait for the result.
They are created dynamically using a stored proc and delete themselves when they complete successfully.
I can query the progress - either directly checking the results of the job or just checking the agent job status and display the job status to the user if required...
I guess the creation of the stored proc took a few hours but now it is as easy as having my code call the stored proc with a query to run - (there is no way for the user to influence to sql passed to the stored proc - so protected from injection in this case).
I would say SQL Agent jobs are, for me at least, very easy to use for dynamic tasks.
Hey MM, based on your commentary on this post and others I am confident that you are an experienced SQL Server'er with superior development skills to say the very least. What you described sounds like a nice solution that works for your scenario however it's a stretch to describe it as an easy way to leverage bcp from within T-SQL. It may be easy now that you have the proc done, but that solution is not for the novice to be sure and it still does not count as a purely T-SQL solution since the SQL Agent job still shells out to cmd...am I off the grid here?
I long for the day when a BOL article exists describing the currently fictional BULK EXPORT command and I would submit Service Broker as a way to handle the "user should not have to wait" requirement and simply poll the SB queue for status.
PS mind sharing the code for that "job creation proc"?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2011 at 2:37 pm
opc.three (5/16/2011)
mister.magoo (5/16/2011)
opc.three (5/16/2011)
...SQL Agent jobs don't make dynamic behavior easy...
Hey MM, based on your commentary on this post and others I am confident that you are an experienced SQL Server'er with superior development skills to say the very least. What you described sounds like a nice solution that works for your scenario however it's a stretch to describe it as an easy way to leverage bcp from within T-SQL. It may be easy now that you have the proc done, but that solution is not for the novice to be sure and it still does not count as a purely T-SQL solution since the SQL Agent job still shells out to cmd...am I off the grid here?
I long for the day when a BOL article exists describing the currently fictional BULK EXPORT command and I would submit Service Broker as a way to handle the "user should not have to wait" requirement and simply poll the SB queue for status.
PS mind sharing the code for that "job creation proc"?
Hey opc.three,
I wasn't intentionally saying it was an easy way to bcp out, although yes that would be possible - but as you quite rightly say, that would involve a "shell" command. The reason I only quoted the bit about SQL Agent being not easy to use dynamically was that I wanted to comment solely on that, I guess out of context of this thread, so sorry for that.
I don't think I can share the exact code for the sql agent stuff as it is not my property, but I will look into doing a new version when I get time that I can share.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 16, 2011 at 3:34 pm
mister.magoo (5/16/2011)
Hey opc.three,I wasn't intentionally saying it was an easy way to bcp out, although yes that would be possible - but as you quite rightly say, that would involve a "shell" command. The reason I only quoted the bit about SQL Agent being not easy to use dynamically was that I wanted to comment solely on that, I guess out of context of this thread, so sorry for that.
I re-read it and yeah, sorry, I think I took that one too far into the context of the bcp conversation...
You are right it can be made to be flexible and dynamic...and the "start sql agent job" proc being async actually works quite nicely to your advantage in your scenario which is interesting because most of the comments I see on that are "how do I make it wait before returning control?".
I don't think I can share the exact code for the sql agent stuff as it is not my property, but I will look into doing a new version when I get time that I can share.
No rush and no worries if you can't get to it. I have thought about exposing Agent jobs for calling from a client app the way you're describing but had not considered, nor ever heard of anyone, implementing dynamic job creation as part of it so got curious what that code might look like.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply