February 1, 2009 at 8:26 am
ALZDBA (2/1/2009)
Jeff Moden (1/31/2009)
That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.If there are bugs in the system, I'm glad they (ms) fix it.
Maybe the fix isn't what we would like it to be, but if it closes weak parts of the implementations, they should post a remark for this behaviour, so we can prepare for the impact.
And because we all have a nice system of Test -> QA -> EnduserTest -> Prod 😀 , this kind of problems should arrise before it shuts down any production revenues.
What is important, is that you know you are using an undocumented whatever and that you have to check its availability / operational status with every sp, hotfix, new realease, ... and be prepared to pay the price if they throw it away.
So first try to figure out the proper way before going to any darker side. 😉
And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.
It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.
--Jeff Moden
February 1, 2009 at 8:34 am
Phil Factor (2/1/2009)
Yeah. spMakeWebTask. I love using this for dishing out excel-based reports via email. I still use it, but with a delegation process for doing daily reports (I'm too idle to use Service Broker) It now, sadly has a sentence of deprecation on it which makes me slightly irritated.Robyn and I describe a delegation system on http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/
Jeff, there was a security issue designed-into spMakeWebTask. Actually, as well as that it used to kill the server intermittently under heavy use when I first used it in v7 . (They fixed that in 2000).
There is nothing quite so useful as spMakeWebTask for the busy programmer doing lots of reporting. I don't quite buy the idea that SQL Server 2005 Reporting Services (SSRS) is as handy. One day, maybe I can persuade Robyn to do a workbench on it, along with a nifty CLR that does the same job.
Yep... that's how we ended up fixing it... a proxy user/delegation system. "Works fine, fails safe, and drains to the bilge".
Again, my point is, it was fully documented and, although I admit the change in security was necessary to cover a security loop hole, the change had an impact just as if an undocumented feature had been used. The only difference is that they included the fact in the "what changed" section of the service pack documentation. Code still needed to be fixed. Like I said, wait until they fix the very well documented SUM() OVER... lot's of people are gonna have to fix a lot of code just as if an undocumented feature had changed. 😉
--Jeff Moden
February 1, 2009 at 10:03 am
Jeff Moden (2/1/2009)
Like I said, wait until they fix the very well documented SUM() OVER... lot's of people are gonna have to fix a lot of code just as if an undocumented feature had changed. 😉
Depends how they 'fix' it. If they leave the ORDER BY optional (contrary to the other windowing functions) and ensure that the default behavior remains as it is now, then there shouldn't need to be any code changes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2009 at 11:00 am
Jeff Moden (2/1/2009)
And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.
Indeed, you cannot effort not to do an acceptable series of regression tests.
But by using undocumented features, you will get into troubles in the long run, and it's removal or alter will not be nor get documented or announced.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 1, 2009 at 7:00 pm
ALZDBA (2/1/2009)
Jeff Moden (2/1/2009)
And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.
Indeed, you cannot effort not to do an acceptable series of regression tests.
But by using undocumented features, you will get into troubles in the long run, and it's removal or alter will not be nor get documented or announced.
My point is, that also happens with fully documented features.
--Jeff Moden
February 1, 2009 at 7:01 pm
GilaMonster (2/1/2009)
Jeff Moden (2/1/2009)
Like I said, wait until they fix the very well documented SUM() OVER... lot's of people are gonna have to fix a lot of code just as if an undocumented feature had changed. 😉Depends how they 'fix' it. If they leave the ORDER BY optional (contrary to the other windowing functions) and ensure that the default behavior remains as it is now, then there shouldn't need to be any code changes.
Heh... yeah... we'll know how that one goes. 😉
--Jeff Moden
February 2, 2009 at 10:38 am
Steve Jones - Editor (11/25/2008)
The typical plan from MS is to announce deprecation in a version, say SS2K8, for a feature, continue to support it in the next version, say SS2K11, and then remove it in the following version, SS2K14.That being said, I think some of this FOREACH functionality will be pushed to Powershell. It's a better fit for that type of language.
Personally, PS feels very complex and convoluted when you are trying to do something simple. I know PS provides a lot of flexibility but when what you need is simplicity and not extreme flexibility, PS is more of a hindrance then a help. And unless I incorrectly understand how PS will work in SQL, PS is even more disconnected from SQL then CLR. For example if you want to take a piece of T-SQL you’ve used for a long time and incorporate into it some ‘For Each’ executions, there is no way to integrate PS into your T-SQL code. Am I incorrect about this lack of integration between PS & T-SQL?
I’m familiar with VB/VB.Net/C++ and so I can appreciate what PS can do and what it offers and how it’s better at Looping but if its so flexible and soo much better then why couldn’t MS just alter the undocumented looping SP’s to internally use PS and then make the undocumented SP’s documented so that those who want to stay within T-SQL can do so while still reaping some of the benefits of PS?
Kindest Regards,
Just say No to Facebook!November 15, 2012 at 11:55 am
Almost 4 years since the last post but just in case someone else reads this:
does anyone know of a simple way to create a html web page from a query like we could using sp_MakeWebTask?
As friends in Texas would say, sp_MakeWebTask is slicker than deer guts on a door knob. Used it in my old position to create html reports that had links to our MS-CRM app. The WOW factor was amazing.
Now a year later and moving to yet another position (my 3rd, all with the same company) saw a way to use it again, would have been the perfect solution and so damn simple. Found out yesterday it was gone...the joy is gone, the job is now drudgery, I am not happy.
November 15, 2012 at 12:12 pm
jbayliss i'm not sure what the end results were from sp_MakeWebTask; i would assume it was a simple grid like results of the table?
if that's true, I tossed a CLR example project on codePlex that exports any table/query to various formats, including html.
take a look at this project:
http://sqlclrexport.codeplex.com/
one of the exports methods is this:
EXECUTE CLR_ExportQueryToHTML @QueryCommand = 'SELECT myAlias.* FROM #Results myAlias WHERE SomeColumn = 1',
@FilePath = 'C:\Data\',
@FileName = '\Results_export.txt',
@IncludeHeaders = 1
@Title = 'Page Title',
@Summary = 'This is my Summary Description For All The business Information you Requested',
@HTMLStyle = 1
and here is a link to one of the sample exports to help you visualize it:
SQL CLR Export Example: Style_01_Caramel.html
since the source code is there, you can easily modify the html definitions that get exported to match anything you'd prefer over the examples.
Lowell
November 15, 2012 at 12:26 pm
Lowell (11/15/2012)
jbayliss i'm not sure what the end results were from sp_MakeWebTask; i would assume it was a simple grid like results of the table?if that's true, I tossed a CLR example project on codePlex that exports any table/query to various formats, including html.
take a look at this project:
http://sqlclrexport.codeplex.com/
one of the exports methods is this:
EXECUTE CLR_ExportQueryToHTML @QueryCommand = 'SELECT myAlias.* FROM #Results myAlias WHERE SomeColumn = 1',
@FilePath = 'C:\Data\',
@FileName = '\Results_export.txt',
@IncludeHeaders = 1
@Title = 'Page Title',
@Summary = 'This is my Summary Description For All The business Information you Requested',
@HTMLStyle = 1
and here is a link to one of the sample exports to help you visualize it:
SQL CLR Export Example: Style_01_Caramel.html
since the source code is there, you can easily modify the html definitions that get exported to match anything you'd prefer over the examples.
While it's true that CLR can be used to do such a thing, the point is that, as they have done with so many things, Microsoft has elected to remove yet another useful feature (anyone remember the {f4} key in query Analyzer?). sp_Makewebtask could even use style sheets and, if you didn't know how to use a style sheet, you still didn't need to know a totally different language to do something "pretty". In fact, if you didn't mind the defaults, you didn't even need to know HTML to use it.
--Jeff Moden
November 15, 2012 at 3:01 pm
Lowell and Jeff,
Glad I didn't bet on getting a reply! And thanks!
I think I got the CLR stuff figured out - have not had the pleasure of using it before. I assuming I do not need to make any changes except for the database name for the CLR assemblies, correct.
About the html though, my objective is to setup a way (using my extremely limited skill set and perhaps some quick learning) to compare customer data in one table with data in another table. We sent our customer records to Dun & Bradstreet for validating and we (those of us who do not want to spend our time cleaning dirty data) want to review and compare that data before making any changes to our production data.
It appears that I may need to put both datasets in one table rather than using two separate sql statements like i would if i used sp_MakeWebTask. I can make that work I'm thinking.
Again, a big thank you for your reply. Everything I have learned I owe to my coworkers, the great people (like yourselves) on web sites who share their knowledge (I miss LazyDBA), and great books like The Guru's Guide To Transact-SQL.
Boring stuff about me:
Was a Lab Rat (Nuc pwr plant to Biotech) most of my career, in 84 began to dabble in DOS, learned Edlin, batch files and Lotus 1A macros. By 95, my Lab working days were over so took some courses in VB and in 1999 got a job as developer. Realized right away I would not make it as a VB programmer but liked writing T-SQL code for reports. In 2005 was hired to do Crystal Reports for MS-CRM 1.2. Migrated it to Version 3 and started using JavaScript to do customizations - without using .NET!
Created my first html report about 3 years ago to email our sales reps as I got tired of emailing Excel files. (Had a great boss who let me learn knew stuff even if it took me a little longer to get it done.) Used CSS, pulled data back for use in in 2 or 3 different sections, created links so the user could click on the Company name and open the CRM web page for that company.
Bottom line is I can write T-SQL, and not too bad with JavaScript. But have not even looked at CLR and has been >10 years for VB.
November 16, 2012 at 5:21 am
It is now much easier to make HTML tables in SQL than it was in those far-off days. I regularly do wicked things like this.
[font="Courier New"]DECLARE @query NVARCHAR(MAX)
SET @query = '<table>
<caption>AdventureWorks Employees</caption>
<tr><th>Employee Name</th><th>Phone</th><th>Email</th></tr>'
+ REPLACE(CAST((SELECT TOP 20 --purely for demonstration purposes
td = COALESCE(Title + ' ','')
+ COALESCE(firstname + ' ','')
+ COALESCE(Middlename+' ','')
+ Lastname, '',
td = Phone,'',
td = EmailAddress
FROM
person.contact
FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX)),
'</tr><tr>','</tr>
<tr>') + '</table>
'
SELECT @Query
[/font]
And it is pretty easy to write these out to disk using BCP. (I've written an article on the topic here http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/ )
Best wishes,
Phil Factor
November 16, 2012 at 7:22 am
jbayliss (11/15/2012)
Lowell and Jeff,Glad I didn't bet on getting a reply! And thanks!
I think I got the CLR stuff figured out - have not had the pleasure of using it before. I assuming I do not need to make any changes except for the database name for the CLR assemblies, correct.
About the html though, my objective is to setup a way (using my extremely limited skill set and perhaps some quick learning) to compare customer data in one table with data in another table. We sent our customer records to Dun & Bradstreet for validating and we (those of us who do not want to spend our time cleaning dirty data) want to review and compare that data before making any changes to our production data.
It appears that I may need to put both datasets in one table rather than using two separate sql statements like i would if i used sp_MakeWebTask. I can make that work I'm thinking.
Again, a big thank you for your reply. Everything I have learned I owe to my coworkers, the great people (like yourselves) on web sites who share their knowledge (I miss LazyDBA), and great books like The Guru's Guide To Transact-SQL.
Boring stuff about me:
Was a Lab Rat (Nuc pwr plant to Biotech) most of my career, in 84 began to dabble in DOS, learned Edlin, batch files and Lotus 1A macros. By 95, my Lab working days were over so took some courses in VB and in 1999 got a job as developer. Realized right away I would not make it as a VB programmer but liked writing T-SQL code for reports. In 2005 was hired to do Crystal Reports for MS-CRM 1.2. Migrated it to Version 3 and started using JavaScript to do customizations - without using .NET!
Created my first html report about 3 years ago to email our sales reps as I got tired of emailing Excel files. (Had a great boss who let me learn knew stuff even if it took me a little longer to get it done.) Used CSS, pulled data back for use in in 2 or 3 different sections, created links so the user could click on the Company name and open the CRM web page for that company.
Bottom line is I can write T-SQL, and not too bad with JavaScript. But have not even looked at CLR and has been >10 years for VB.
Especially being one myself, I love "old school". We do know how to hammer out a work around to "improvements" and deprecations. 🙂
I don't know what your level of HTML knowledge is but Phil Factor's post (post above) shows the same basic method that I use to work around the demise of sp_MakeWbtask. Is that enough or do you need more help on this?
--Jeff Moden
November 16, 2012 at 7:31 am
Phil Factor (11/16/2012)
It is now much easier to make HTML tables in SQL than it was in those far-off days. I regularly do wicked things like this.[font="Courier New"]DECLARE @query NVARCHAR(MAX)
SET @query = '<table>
<caption>AdventureWorks Employees</caption>
<tr><th>Employee Name</th><th>Phone</th><th>Email</th></tr>'
+ REPLACE(CAST((SELECT TOP 20 --purely for demonstration purposes
td = COALESCE(Title + ' ','')
+ COALESCE(firstname + ' ','')
+ COALESCE(Middlename+' ','')
+ Lastname, '',
td = Phone,'',
td = EmailAddress
FROM
person.contact
FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX)),
'</tr><tr>','</tr>
<tr>') + '</table>
'
SELECT @Query
[/font]
And it is pretty easy to write these out to disk using BCP. (I've written an article on the topic here http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/ )
I love that article, Phil. "There are no limits".
--Jeff Moden
November 16, 2012 at 7:50 am
Especially being one myself, I love "old school". We do know how to hammer out a work around to "improvements" and deprecations. 🙂
I don't know what your level of HTML knowledge is but Phil Factor's post (post above) shows the same basic method that I use to work around the demise of sp_MakeWbtask. Is that enough or do you need more help on this?
Phil, that is way cool!. Just did a quick mockup on my system- this is going to be fun to work with. Now to look up your link. Thanks!
Jeff, I too enjoy the challenge of figuring out how to get something to work in spite of the limitations put in our way.
May take me a while but I do believe with the options y'all have given me I can make it work.
Thanks everyone,
Jim
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply