CLR Quiz

You're developing an application which has a feature to read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server computer. You decide to use CLR function and need to register the assembly with SQL Server by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

A. Default
B. SAFE
C. EXTERNAL_ACCESS
D. UNSAFE
E. Both B and C
F. None of the above

Answer: [C]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Read RSS feed by using SQL Server stored procedure

If you want to read RSS Feed by using SQL Server stored procedure, you can use CLR stored procedure as described below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Text;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void spReadRSSFeed(string strURL)
  {
      XPathDocument doc = new XPathDocument(strURL);
      XPathNavigator nav = doc.CreateNavigator();
      XPathNodeIterator i = nav.Select("//item");
      SqlMetaData[] rss_results = new SqlMetaData[4];
      rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
      rss_results[1] = new SqlMetaData("Publication Date",
          SqlDbType.DateTime);
      rss_results[2] = new SqlMetaData("Description",
          SqlDbType.NVarChar, 2000);
      rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
      SqlDataRecord record = new SqlDataRecord(rss_results);
      SqlPipe sqlpipe = SqlContext.Pipe;
      sqlpipe.SendResultsStart(record);
      while (i.MoveNext())
      {
          record.SetString(0, (string)
              i.Current.Evaluate("string(title[1]/text())"));
          record.SetDateTime(1, DateTime.Parse((string)
              i.Current.Evaluate("string(pubDate[1]/text())")));
          record.SetString(2, (string)
              i.Current.Evaluate("string(description[1]/text())"));
          record.SetString(3, (string)
              i.Current.Evaluate("string(link[1]/text())"));
          sqlpipe.SendResultsRow(record);
      }
      sqlpipe.SendResultsEnd();
  }
};

After building to an assembly named RSSReader, for example, you can deploy it to your SQL Server like this:

CREATE ASSEMBLY [RSSReader]
AUTHORIZATION [dbo]
FROM 'Path to your RSSReader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Note that you have to use EXTERNAL_ACCESS because we need to access Internet
GO
CREATE PROCEDURE [dbo].[spReadRSSFeed]
@URL [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RSSReader].[StoredProcedures].[spReadRSSFeed];

-- Running for testing
spReadRSSFeed 'http://msdn.microsoft.com/sql/rss.xml'

-- Remove them
DROP PROCEDURE spReadRSSFeed
DROP ASSEMBLY RSSReader