<$BlogRSDUrl$>

Wednesday, June 30, 2004

Phonics ?

I cdnuolt blveiee taht I cluod aulaclty uesdnatnrd waht I was rdgniegThe phaonmneal pweor of the hmuan mnidAoccdrnig to a rscheearch at Cmabrigde Uinervtisy, it deosn't mttaer inwaht oredr the ltteers in a wrod are, the olny iprmoatnt tihng is taht thefrist and lsat ltteer be in the rghit pclae. The rset can be a taotl mses andyou can sitll raed it wouthit a porbelm. Tihs is bcuseae the huamn mnid deos not raed ervey lteter by istlef, but the wrod as a wlohe.

Amzanig huh?

Saturday, June 05, 2004

Tunes:
Perfect Day - Lou Reed
Destinys Child - Elbox mix
Barbie girl - mixes
Acker Bilk - Stranger on the shore
Steps - tradgedy
Art Garfunkel - bright eyes
Blondei - heart of glass
Shaggy - it wasn't me
Elvis - now or never
Coolio - gangsters paradise
All saints - never ever
Roberta Flack - killing me softly
E Humperdink - PLease release me
Beatles - Day tripper/we can work it out/i feel fine/imagine/i wanna hold your hand
Puff Daddy - I'll be missing you
Human league - don't you want me
FGTH - two tribes




Thursday, June 03, 2004

Office Business Scorecards Accelerator Benefits and Overview

Creating SQL Based RSS Feed ...
by Vadivel Mohanakrishnan

--------------------------------------------------------------------------------


As you would have noticed off late there are many sites, which provide RSS feeds of their site content. If you have a close look at them you can find that they would be using either an .aspx page or an .asp page etc., Why do this in a round about manner when there are options for generating your RSS feed directly from your SQL Server itself. In this article I have explained about RSS feed and the way to create it through SQL Script directly.

What is RSS?

RSS is a method of distributing links to content in your web site that you'd like others to use. RSS stand for "Rich Site Summary." or "Really Simple Syndication." The main part of an RSS file is the "items" tag. Your RSS file will have to include at least one item tag and they are generally web pages that you'd like others to link to. For instance, let's say you run a technical web site and update the site once in a week. Now you want to share the information about the top 10 articles to the outside world. How do we do that? Just create a RSS file (xml file) and form multiple item tags with information about those articles. To enter your item into the RSS file, you'll need this information: Title, Publication date, Description, Link

After defining “items” which we would like to distribute to the world, we need to now define our site as a "channel." We'll use the same tags as with the items: title, description, link and along with it a new tag called language. But, this time the information will be about your entire site, rather than a particular portion. As long as we don't surround this information with an opening and closing tags, it won't be seen as item information but rather as channel information.

If you are not familiar with RSS do have a look at http://www.webreference.com/authoring/languages/xml/rss/intro/3.html or just google it to find more details about that.

Sample Table Structure

CREATE TABLE [dbo].[ArticleMaster] (
[ArticleId] [int] IDENTITY (1, 1) NOT NULL ,
[ArticleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ArticleDesc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PublishedDate] [smalldatetime] NULL
) ON [PRIMARY]

Sample Data

Insert into ArticleMaster Values ('title one','description one',getdate())
Insert into ArticleMaster Values ('title two','description two',getdate()-1)
Insert into ArticleMaster Values ('title three','description three',getdate()-2)
Insert into ArticleMaster Values ('title four','description four',getdate()-3)

The solution

After creating the sample table and filling it with some test data, create the following stored procedure. This SP would generate the required data in an xml format. Normally the xml tags would be generated based on the field names, I have just overcome that by given an alias name for each field.

CREATE Proc GenerateRssFeed
AS
Select TOP 10
LTRIM(RTRIM(AName)) as title,
Convert(varchar(10),PubDate,101) as pubdate,
LTRIM(RTRIM(ADesc)) as description,
'http://www.yourwebsite.com/content.aspx?n=' + CAST(ArticleId AS varchar(5)) as link
From
ArticleMaster item
For xml auto, elements
GO

Getting results as a RSS feed

Execute the below sql script to generate the RSS feed.

sp_makewebtask @outputfile = 'C:Rss.xml', ---- Point 1
@query = 'Exec GenerateRssFeed', -- Put the SP name here
@templatefile = 'C:RssFeedTemplate.xml' -- Point 2

1) @outputfile : This parameter specifies the output file for the execution of the command.

2) @query : The query that is to be used for generating the HTML output. This is mostly a SELECT statement or a stored procedure call.

3) @templatefile : This parameter specifies the pathname of the file to be used as the template for the page the stored procedure generates. The template can contain HTML tags and/or text that need to appear on the page in addition to the query results. The stored procedure will replace each "insert_data_here" marker within the page template file with data returned by an SQL query.

Point 1: This specifies the full pathname of the xml document that the stored procedure is to create. Please note that you can also provide the path, which is mapped, in your IIS. i.e., 'C:InetPubWWWRootmywebsiteRss.xml' is also valid.

Point 2: This specifies the pathname of the file to be used as the template for the xml which the stored procedure generates. The stored procedure will replace each <%insert_data_here%>marker within the xml page template file with data returned by it.

Source code for Template file



I have just created a template file without any "channel" information in it. If you need it just hard code those content between "channel" tag and "begindetail". I am saying you can hardcode in the template file itself because the information is going to change very rarely. Once in a while if it changes you can edit this template file alone.

Scheduling the task

As of now we saw that the RSS feed is generated from a SQL table based on a Stored procedure. Wouldn't it be nice if the RSS feed were regenerated whenever data changes in the specified table? Believe me it isn't a tough task. Let me explain the same below:

sp_makewebtask @outputfile = 'C:Rss.xml',
@query = 'Exec GenerateRssFeed',
@templatefile = 'C:RssFeedTemplate.xml',
@whentype = 10,
@datachg='TABLE=ArticleMaster COLUMN=ArticleId,AName,ADesc,PubDate'

@whentype specifies when the SQL Server Agent is to run our Web task which creates the RSS feed. By default it takes the value 1, which means it has to run the task immediately and delete the task (and stored procedure that creates the Web page) immediately after execution. That is what has been happening previously. Actually it can take values from 1 to 10.

You can see that I have specified @whentype as 10; it means the stored procedure would create a web task that the SQL Server executes now and again whenever a user changes a value within one of the columns listed in the @Datachg parameter.

@Datachg is the list of table, which is optional, and column names that trigger execution of the Web task after changes are made. As said before @datachg is required when @Whentype is set as 10. Please note that specifying the @datachg parameter creates three triggers Update, Insert and Delete on each table specified by the @datachg parameter. If a trigger already exists on that table, sp_createwebtask adds its sp_runwebtask call to the end of the existing trigger provided the existing trigger was not created WITH ENCRYPTION. If the existing trigger is encrypted, sp_makewebtask will fail.

Note: @procname is the attribute, which specifies the name of the Web task. Its not an mandatory one, so if omitted, the system will generate the name as Web_. You could cross check that by expanding your database in enterprise manager and then click on “Stored Procedures”. If at all you have run the scripts provided in this article you would see a stored procedure which starts with a name “Web_”.

Web Assistant Wizard

On the flip side, we can make use of SQL Web Assistant Wizard for creating these scripts automatically for us. Let me give list down the steps involved in doing the same.

Step1: Open SQL Enterprise manager and choose a database
Step2: Click on Tools >> Wizard option
Step3: There would be a tree structure displayed in the popup window. Expand the node “Management” and choose “Web Assistant Wizard” before clicking on “OK”

Now you could see the wizard in action. It’s pretty straightforward wizard, choose your options and say finish. In the last screen there would be a provision for saving the code as a script (*.sql) file. Do that and go through the code for better understanding.

Conclusion

Hope this article gave some insight into the way RSS feed can be generated directly from SQL Server itself. I suggest you fiddle around a bit with @whentype to get familiarize with its other values also. Please note that in order for sp_makewebtask to execute you need to have admin privileges to that database.


Tuesday, June 01, 2004

Google Answers: Emailing HTML content from SQL Server 2000

Email format detection?
Decking costs
Abbey ISA
HSBC credit card, test AOL 9 then cancel!

This page is powered by Blogger. Isn't yours?