Configuring a software feature is usually much harder than removing it. Most of the times, removing a feature could be just a matter of a mouse click or a simple command/statement.
So what do you think about this question? Enabling FILESTREAM storage on a database and removing it completely from a database – which one is harder?. Well the correct answer to this question may be ‘It depends!’.
Removing FILESTREAM features completely from a SQL Server 2008 may be a little bit trickier than you would expect. This post explains how to remove the FILESTREAM storage features from a database.
Why would someone ever want to remove the FILESTREAM storage features? Well, this is not something that you will frequently do. One scenario could be while moving the production database to a dev or testing environment where the FILESTREAM data is not relevant any more. In such a case, to reduce the database size, one might decide to remove FIELSTREAM storage completely from the database.
Removing FILESTREAM Storage from a database
At first look, it looks like removing FILESTREAM features from a database will be just a matter of the dropping the FIELSTREAM columns and removing the FILESTREAM filegroup from the database.
Unfortunately, it is a little bit more complicated than that. To completely remove FILESTREAM features from a database, you need to perform the following steps.
- Delete all FILESTREAM columns from all tables
- Disassociate tables from FILESTREAM filegroups (we will see this shortly)
- Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
- Remove all FILESTREAM filegroup (there may be more than one of them)
Let us now see how to do this. Before we perform the above operations, we need to have a database with FILESTREAM features. Run the script below to create such a database.
-- Create a FILESTREAM Enabled Database
CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\Data\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS_FG CONTAINS FILESTREAM(
NAME = NorthPoleFS_FILE,
FILENAME = 'C:\FS\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\Data\NorthPoleLOG.ldf')
GO
-- CREATE a TABLE with FILESTREAM columns
USE NorthPole
GO
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
Dropping FILESTREAM Columns
So lets get into the destructive mood. Our job is to remove the FILESTREAM storage completely from this database. Let us start with dropping the FILESTREAM column from the Item Table.
ALTER TABLE Items DROP column ItemImage
In a real-world scenario there may be more than one table with FILESTREAM columns and each table may have more than one FILESTREAM columns. You need to drop all of them.
Disassociating tables from FILESTREAM filegroups
This is a tricky step and often confusing. Each table that has one or more FILESTREAM columns is associated to a FILESTREAM filegroup in the database. This association is usually made when the table is created. If your database has more than one FILESTREAM filegroup, you can specify in which filegroup you wish to store the FILESTREAM data of that table.
This association exists even after you drop all the FIELSTREAM columns. To SQL Server, it will still look like the FILESTREAM filegroup file is in use. So before deleting the FILESTREAM filegroup file (FILESTREAM Data Container), you need to remove this association. The following TSQL code does this.
-- Disassociate table "items" from its FILESTREAM file group
ALTER TABLE Items SET (FILESTREAM_ON = "NULL")
Removing FILESTREAM filegroup files
The next step is to remove the FILESTREAM filegroup file. The following TSQL code shows how to do this. If the database has more than one FILESTREAM filegroup, you need to do it for the filegroup file of each of them.
-- Remove the File
ALTER DATABASE NorthPole Remove file NorthPoleFS_FILE;
Removing FIELSTREAM filegroups
And finally, you are ready to drop the FILESTREAM filegroup. The following TSQL code snippet demonstrates it. If the database has more than one FILESTREAM filegroup, the operation needs to be performed for each filegroup.
-- Remove the File Group
ALTER DATABASE NorthPole REMOVE FILEGROUP NorthPoleFS_FG;
If you have got a FILESTREAM question, feel free to post it to this forum.
This article explains how to generate an RSS feed with iTunes extensions using TSQL.
What is an RSS Feed?
RSS is an XML document format that websites use to publish frequently updated information. An RSS reader application such as google reader or outlook RSS reader can query the RSS feed associated with a website and download newly updated content at specific intervals. If you are not familiar with RSS feeds, you can find more information here.
An icon similar to the one given here indicates that the website publishes RSS (sometimes RDF or ATOM) feeds and usually you can get feed URL by clicking on the icon. Some browsers like firefox displays a similar icon in the url address bar if it finds a feed on the websitie using its auto-discovery feature.
What is Podcasting?
In the recent years, RSS has become a very popular content model for publishing frequenlty changing information. This resulted in extending the RSS specification to accomodate additional information as part of the RSS feed. Podcasting is online audio content that is delivered via an RSS feed. An RSS reader can download them and play on a phone, ipod or computer. You can read about podcasting here.
What is iTunes?
iTunes is a media player application primarily used in Apple iPods. It allows a user to download, organize and play digital music and video files. You can read more about iTunes here.
What is RSS iTunes Extension?
iTunes extension is an extension to the RSS content specitication to include additional podcasting information to an RSS feed. An RSS reader or iTune aware application cann identify, download and play digital media files associated with an RSS feed item. iTunes extension defines additional elements that can be added to an RSS feed channel and item elements to publish the details of the associated digital media files.
Generating an RSS Feed using TSQL
I had written a series of articles at www.sqlservercentral.com that explain how to generate RSS feeds using TSQL. If you have not seen them, I would reccomend you take a look at them.
Generating RSS feeds with iTune extension
Generating an RSS feed with iTune extension involves generating the required iTune extension elements as part of the RSS channel and item element. Here is an example of the RSS feed that we are going to generate as part of this article.
<rss version="2.0" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<channel>
<title>XML Workshops</title>
<link>http://www.beyondrelational.com/xml.aspx</link>
<language>en-us</language>
<copyright>© beyondrelational.com</copyright>
<itunes:subtitle>XML Workshops</itunes:subtitle>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:summary>Explains SQL Server XML</itunes:summary>
<description>Explains SQL Server XML</description>
<itunes:owner>
<itunes:name>Jacob Sebastian</itunes:name>
<itunes:email>jacob@beyondrelational.com</itunes:email>
</itunes:owner>
<itunes:image href="http://beyondrelational.com/r.ashx?E" />
<itunes:category text="SQL Server" />
<item>
<title>XQuery Tutorials</title>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:subtitle>XQuery Tutorials</itunes:subtitle>
<itunes:summary>A collection of XQuery Tutorials</itunes:summary>
<guid>http://beyondrelational.com/xml/xquery.mp3</guid>
<pubDate>Thu, 04 Mar 2010 19:00:00 GMT</pubDate>
<itunes:duration>01:30</itunes:duration>
<itunes:keywords>SQL Server, XML, XQuery</itunes:keywords>
<enclosure url="http://beyondrelational.com/xml/xquery.mp3"
length="8727310"
type="audio/mpeg" />
</item>
<item>
<title>XSD Tutorials</title>
<itunes:author>Jacob Sebastian</itunes:author>
<itunes:subtitle>XSD Tutorials</itunes:subtitle>
<itunes:summary>A collection of XSD Tutorials</itunes:summary>
<guid>http://beyondrelational.com/xml/xsd.mp3</guid>
<pubDate>Thu, 04 Mar 2010 19:00:00 GMT</pubDate>
<itunes:duration>02:00</itunes:duration>
<itunes:keywords>SQL Server, XML, XSD</itunes:keywords>
<enclosure url="http://beyondrelational.com/xml/xsd.mp3"
length="5650889"
type="audio/mpeg" />
</item>
</channel>
</rss>
You need to add the following to a regular RSS feed to implement an iTune extension.
- Declaration of the iTunes namespace in the root element
- Additional itunes elements in the channel element
- Additional itunes elements in the item element
Getting Started
Let us get started with writing the TSQL code to generate the above XML document. As you would expect, we will use FOR XML to generate the XML document with the required strucutre. Before we proceed, let us create the tables to hold the sample data that we will use in this lab.
-- Create and populare "Channels" table
IF OBJECT_ID('Channels','U') IS NOT NULL BEGIN
DROP TABLE Channels
END
GO
CREATE TABLE Channels(
ChannelID INT IDENTITY PRIMARY KEY,
Title VARCHAR(50),
Link VARCHAR(100),
Summary VARCHAR(200),
Author VARCHAR(40),
AuthorEmail VARCHAR(50),
ImageURL VARCHAR(100),
Category VARCHAR(50) )
GO
INSERT INTO Channels (
Title,
Link,
Summary,
Author,
AuthorEmail,
ImageURL,
Category )
SELECT
'XML Workshops',
'http://www.beyondrelational.com/xml.aspx',
'Explains SQL Server XML',
'Jacob Sebastian',
'jacob@beyondrelational.com',
'http://beyondrelational.com/r.ashx?E',
'SQL Server'
-- Create and populare "Items" table
IF OBJECT_ID('Items','U') IS NOT NULL BEGIN
DROP TABLE Items
END
GO
CREATE TABLE Items (
ItemID INT IDENTITY PRIMARY KEY,
Title VARCHAR(50),
Summary VARCHAR(200),
Author VARCHAR(50),
EnclosureURL VARCHAR(100),
EnclosureLength INT,
EnclosureType VARCHAR(50),
PublicationDate DATETIME,
Duration VARCHAR (10),
Keywords VARCHAR(50)
)
GO
INSERT INTO Items (
Title, Summary, Author,
EnclosureURL, EnclosureLength, EnclosureType,
PublicationDate, Duration, Keywords )
SELECT
'XQuery Tutorials',
'A collection of XQuery Tutorials',
'Jacob Sebastian',
'http://beyondrelational.com/xml/xquery.mp3',
8727310, 'audio/mpeg',
'4 Mar 2010 19:00:00',
'01:30', 'SQL Server, XML, XQuery'
UNION ALL
SELECT
'XSD Tutorials',
'A collection of XSD Tutorials',
'Jacob Sebastian',
'http://beyondrelational.com/xml/xsd.mp3',
5650889, 'audio/mpeg',
'4 Mar 2010 19:00:00',
'02:00', 'SQL Server, XML, XSD'
Deciding the approach
As I mentioned earlier, we will use FOR XML to generate the RSS feed with iTunes extension. FOR XML can be used with AUTO, RAW, PATH or EXPLICIT and each of them provides different level of control over the structure of the XML output. EXPLICIT is the most complecated among all of them but gives you total control over the structure of the XML output. You can generate XML documents with almost any complex structure and shape using EXPLICIT.
Though FOR XML EXPLICIT is very powerful, the syntax is much complecated. SQL Server 2005 added PATH which is almost as powerful as EXPLICIT but has a much simpler syntax. Because PATH is very powerful and easy to use, I always recommend using PATH over EXPLICIT if the desired XML structre can be generated using PATH. However, there are times when you need EXPLICIT to get around a specific shaping problem.
The 'expected output' shown in this article can be generated using FOR XML PATH. Here is the TSQL code needed to generate the XML document having the same strucutre given above.
;WITH XMLNAMESPACES(
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS itunes
)
SELECT
'2.0' AS '@version',
(
SELECT
title as 'title',
link,
'en-us' as language,
'© beyondrelational.com' as copyright,
title as 'itunes:subtitle',
author as 'itunes:author',
summary as 'itunes:summary',
summary as 'description',
author as 'itunes:owner/itunes:name',
authoremail as 'itunes:owner/itunes:email',
ImageURL as 'itunes:image/@href',
category as 'itunes:category/@text',
(
SELECT
title,
author as 'itunes:author',
title as 'itunes:subtitle',
summary as 'itunes:summary',
EnclosureURL as 'guid',
LEFT(DATENAME(dw, PublicationDate),3)
+ ', ' +
STUFF(
CONVERT(nvarchar,PublicationDate,113),
21,
4,
' GMT')
as 'pubDate',
Duration as 'itunes:duration',
Keywords as 'itunes:keywords',
EnclosureURL as 'enclosure/@url',
EnclosureLength as 'enclosure/@length',
EnclosureType as 'enclosure/@type'
FROM items
FOR XML PATH('item'), TYPE
)
FROM Channels
FOR XML PATH('channel'), TYPE
)
FOR XML PATH('rss')
The above FOR XML PATH query produces an XML document with the same structure as the expected output given earlier. However, when looking at the output, you might notice the presence of repeated namespace declarations on all the top level nodes within the root element. In the original document, only the root element had namespace declaration. However, the output we just produced has namespace declarations on the 'channel' and 'item' elements as well. Here is the output produced by the above query (note that I have removed a lot of content for brevity and highlight the point I am trying to explain).
<rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" version="2.0">
<channel xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<title>XML Workshops</title>
<!-- removed for brevity -->
<itunes:owner>
<itunes:name>Jacob Sebastian</itunes:name>
<itunes:email>jacob@beyondrelational.com</itunes:email>
</itunes:owner>
<itunes:image href="http://beyondrelational.com/r.ashx?E" />
<itunes:category text="SQL Server" />
<item xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">
<title>XQuery Tutorials</title>
<!-- removed for brevity -->
</item>
<!-- removed for brevity -->
</channel>
</rss>
The document we just generated is the same as what we saw in the 'expected output' section of this article. The only difference is that in our version, the 'channel' and 'item' elements too have namespace declarations as they exist in the root node. But that is not a problem. As per the XML specification, every child node inherits the namespace declarations from its parent. So whether you include the namespace declarations in the child node or not, does not make a difference. If you do not include a namespace declaration, it is inherited from the parent. So the meaning of the XML document does not change by repeating the namespace declarations in the child nodes.
Though those documents are the same as per the XML specification, there are some client applications that break if the child elements contain the namespace declarations of their parent element. In such a case, if you want to get rid of those namespace declarations, you will have to take a longer route using FOR XML EXPLICIT.
Here is the FOR XML EXPLICIT version of the above query which produces the EXACT result as given in the original sample.
SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS 'rss!1!xmlns:itunes',
'1' AS 'rss!1!sort!hide',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!itunes:subtitle!element',
NULL AS 'channel!2!itunes:author!element',
NULL AS 'channel!2!itunes:summary!element',
NULL AS 'channel!2!description!element',
NULL AS 'itunes:owner!3!itunes:name!element',
NULL AS 'itunes:owner!3!itunes:email!element',
NULL AS 'itunes:image!4!href',
NULL AS 'itunes:category!5!text',
NULL AS 'item!6!title!element',
NULL AS 'item!6!itunes:author!element',
NULL AS 'item!6!itunes:subtitle!element',
NULL AS 'item!6!itunes:summary!element',
NULL AS 'item!6!guid!element',
NULL AS 'item!6!pubDate!element',
NULL AS 'item!6!itunes:duration!element',
NULL AS 'item!6!itunes:keywords!element',
NULL AS 'enclosure!7!url',
NULL AS 'enclosure!7!length',
NULL AS 'enclosure!7!type'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, '2', Title, Link,
'en-us', '© beyondrelational.com',
Title, Author, Summary, Summary,
NULL, NULL, ImageURL, Category,
NULL, NULL, NULL, NULL, NULL, NULL, null,
NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, '3', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, Author, AuthorEmail, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, '4', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, ImageURL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
5 AS Tag,
2 AS Parent,
NULL, NULL, '5', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, Category, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
6 AS Tag,
2 AS Parent,
NULL, NULL, '6' + STR(ItemID,4), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title,
Author, title, summary, EnclosureURL,
LEFT(DATENAME(dw, PublicationDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PublicationDate,113),21,4,' GMT'),
Duration, Keywords, NULL, NULL, NULL
FROM Items
UNION ALL
SELECT
7 AS Tag,
6 AS Parent,
NULL, NULL, '6' + STR(ItemID,4) + '1', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
EnclosureURL, EnclosureLength, EnclosureType
FROM Items
ORDER BY [rss!1!sort!hide]
FOR XML EXPLICIT
The above query produces an XML document with the same structure as the document given in the 'expected output' listing. As I mentioned earlier, FOR XML EXPLICIT is very powerful and allows you to generate XML documents with almost any shaping requirement. However, it has a much more complecated syntax and often people find it too difficult to work with FOR XML EXPLICIT.
I once wrote a series of FOR XML EXPLICIT tutorials. If you are not very comfortable writing FOR XML EXPLICIT queries, I would recommend you take a look at them.
Here is another version of the FOR XML EXPLICIT query which uses an embedded FOR XML PATH query within. This version has one UNION ALL query less than the previous version.
SELECT
1 AS Tag,
NULL AS Parent,
'2.0' AS 'rss!1!version',
'http://www.itunes.com/dtds/podcast-1.0.dtd'
AS 'rss!1!xmlns:itunes',
'1' AS 'rss!1!sort!hide',
NULL AS 'channel!2!title!element',
NULL AS 'channel!2!link!element',
NULL AS 'channel!2!language!element',
NULL AS 'channel!2!copyright!element',
NULL AS 'channel!2!itunes:subtitle!element',
NULL AS 'channel!2!itunes:author!element',
NULL AS 'channel!2!itunes:summary!element',
NULL AS 'channel!2!description!element',
NULL AS 'itunes:owner!3!itunes:name!element',
NULL AS 'itunes:owner!3!itunes:email!element',
NULL AS 'itunes:image!4!href',
NULL AS 'itunes:category!5!text',
NULL AS 'item!6!title!element',
NULL AS 'item!6!itunes:author!element',
NULL AS 'item!6!itunes:subtitle!element',
NULL AS 'item!6!itunes:summary!element',
NULL AS 'item!6!guid!element',
NULL AS 'item!6!pubDate!element',
NULL AS 'item!6!itunes:duration!element',
NULL AS 'item!6!itunes:keywords!element',
NULL AS 'item!6!!xml'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL, NULL, '2', Title, Link,
'en-us', '© beyondrelational.com',
Title, Author, Summary, Summary,
NULL, NULL, ImageURL, Category,
NULL, NULL, NULL, NULL, NULL, NULL, null,
NULL, NULL
FROM Channels
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL, NULL, '3', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, Author, AuthorEmail, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
4 AS Tag,
2 AS Parent,
NULL, NULL, '4', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, ImageURL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
5 AS Tag,
2 AS Parent,
NULL, NULL, '5', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, Category, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM Channels
UNION ALL
SELECT
6 AS Tag,
2 AS Parent,
NULL, NULL, '6' + STR(ItemID,4), NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title,
Author, title, summary, EnclosureURL,
LEFT(DATENAME(dw, PublicationDate),3) + ', ' +
STUFF(CONVERT(nvarchar,PublicationDate,113),21,4,' GMT'),
Duration, Keywords,
(
SELECT
EnclosureURL AS '@url',
EnclosureLength AS '@length',
EnclosureType AS '@type'
FROM items i WHERE i.ItemID = itm.ItemID
FOR XML PATH('enclosure'), TYPE
)
FROM Items itm
ORDER BY [rss!1!sort!hide]
FOR XML EXPLICIT
Conslusions
The purpose of this article is to demonstrate how to generate an RSS feed with iTune extensions. The query given in this article does not generate all the elements in the RSS 2.0 specification. It does not geneate all the elements in the iTunes extension specification as well. However, this will help you to get started and add the additional elements and attributes to your RSS document following the same method demonstrated in this article.
Excepts from a conversation between Sumit and Leena, that I overheard last week. Don’t know who they are?? Never mind, I will introduce them later!
Leena: “Sumit, I am playing with the FILESTREAM access level and I am bit confused as I am getting a ‘3’ while querying!”
Sumit: “I warned you not to play while at work!”
Leena: “Come on Sumit, I do have a real problem. I am getting a different value than what I have set when I query for it afterwards.”
Sumit: “Must be a WYSIWIG”
Leena: “What?”
Sumit: “What You SET Is What You Get. You get something because you have SET it. You can make mistakes (as always), but not SQL Server.”
Leena: “No, I am not GETting what I have SET. In other words, I have not SET what I am GETing. I am setting the FILESTREAM access level to ‘2’ (Transact SQL and Win32 Access) but why am I getting ‘3’ when I query for the current configuration”
Sumit: “bol”
Leena: “bol? What else should I say? (BOL [hindi] means “Say/Speak”). I want you to take a look at this and help me to figure out what is wrong”
Sumit: “BOL (Books Online) Please!”
Leena: “I looked at Books online. Books online has mentioned only ‘0’, ‘1’ and ‘2’. ‘0’ means FILESTREAM disabled, ‘1’ means TRANSACT SQL Access enabled and ‘2’ means both TSQL and Win 32 Access enabled. Nowhere I see ‘3’ mentioned. Please take a look at http://msdn.microsoft.com/en-us/library/cc645956.aspx”
Sumit: (Wakes up). “I will take a look at it and let you know tomorrow”. (dozes again)
Next Day
Leena: “Sumit, did you get a chance to look at the problem we discussed yesterday?”
Sumit: “Yes”
Leena: “So what is the reason for that strange behavior?”
Sumit: “OCSF”
Leena: (quickly googles for OCSF but makes sure that Sumit does not see it) “Object Client Server Framework?”
Sumit: “Order Coffee and Sandwiches First!”
Leena: (Orders coffee and sandwiches)
Sumit: “Well, there is something undocumented here. Though the documentation shows only 0, 1 and 2, there really exists another value that is ‘3’.”
“FILESTREAM Access Level ‘0’ and ‘1’ are interpreted exactly as documented (0 for Disabled and 1 for TSQL Access). The documentation says that ‘2’ stands for ‘TSQL and Win32 Access’ which is also known as ‘Full Access’”.
“The FULL Access may be of two flavors. TSQL + Win32 Access (Local) and TSQL + Win32 Access (Remote). It means that the configuration value ‘2’ means TSQL and Local Win32 API Access. Configuration value ‘3’ stands for TSQL and Remote Win32 API Access”
Leena: “Oh, that is surprising. However, in my case, I am setting FILESTREAM access level to ‘2’ (TSQL + Local WIN32 Access as per your explanation). So how come SQL Server configures it with ‘3’ and give me back 3 when I query the current settings?”
Sumit: “Well, SQL Server takes the configuration values a little bit differently. FILESTREAM configuration on a SQL Server 2008 instance needs to be done at the Windows Administrator level as well as SQL Server Instance level. A windows administrator can enable or disable IO streaming access to FILESTREAM data from SQL Server configuration Manager”
“If IO streaming is enabled for remote access, then SQL Server will translate your ‘2’ to ‘3’. If it is disabled, your ‘2’ remains to be ‘2’”.
Leena: “Oh, that is funny. But what is the configuration dialog you are referring to?”
Sumit: “Go to SQL Server Configuration Manager, right click on the SQL Server instance and go to the properties. You will see a new TAB there named “FILESTREAM”. This is where you enable or disable remote streaming access to FILESTREAM data.”
“So let me explain this. Currently you have got remote access to your FILESTREAM data enabled at the SQL Server instance level. Now let us try the following.”
-- Set FILESTREAM Access Level to 1 (TSQL Only)
EXEC sp_configure filestream_access_level, 1
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
1
*/
Leena: “It works as expected!”
Sumit: “Now look at this:”
-- Set FILESTREAM Access Level to 2 (TSQL + Win32)
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
3
*/
Leena: “Here is the problem that makes me crazy since yesterday!”
Sumit: “You are right. It might make even normal people like me crazy!”
Leena: “Did you intend to say that I am not normal?”
Sumit: “Not really. I always try to avoid redundant data. Let us get back to the problem. When you set the FILESTREAM Access Level to ‘2’, SQL Server internally interpreted it as ‘3’ because remote streaming access to FILESTREAM data is currently enabled.”
“Now let us go to the configuration page and disable remote FILESTREAM access.”
“Note that we have disabled I/O streaming access from remote clients. Now let us run the same code again.”
-- Set FILESTREAM Access Level to 2 (TSQL + Win32)
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
2
*/
Leena: “Wow! Now it is a WYSIWYG now. I am getting the same value that I have configured. Sumit, you are a genius!”
Sumit: Grins, nods in acknowledgement and silently thanks all those folks who helped him throughout the previous night to figure this problem out.
Sumit and Leena were born in third week of January 2010. They made their first public appearance on 30th January 2010 at Bhaikaka Hall, Ahmedabad during the Community Techdays Event. Sumit is a Database Administrator and Leena is an application developer. I hope they will post more information on their linked-in and facebook profiles soon.
After reading XQuery Lab 51, my friend and database expert Brad Schulz send me a note showing another way of achieving the same results. The approach he suggested was much cleaner than my version.
My code used the following expression in the WHERE clause to filter the records where @about and @resource attributes match.
y.value('@r:resource[1]','VARCHAR(100)') =
z.value('@about','VARCHAR(100)')
Brad suggested a different approach where the entire query is moved into a CROSS APPLY and the outer query can select columns returned by the CROSS APPLY operator and apply filters on them.
Here is the version of the query that Brad wrote.
DECLARE @x XML
SELECT @x = '
<RDF xmlns:r="http://www.w3.org/TR/RDF/"
xmlns:d="http://purl.org/dc/elements/1. 0/"
xmlns="http://dmoz.org/rdf/">
<Topic r:id="Top/World/Afrikaans/Besigheid">
<catid>724829</catid>
<link r:resource="http://www.videos-sa.com" />
<link r:resource="http://besigheidcenturion.co.za/bc/" />
</Topic>
<ExternalPage about="http://www.videos-sa.com">
<d:Title>Kobus Petzer Videoproduksies</d:Title>
<d:Description>Vervaardiging van...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
</RDF>'
;WITH XMLNAMESPACES(
'http://www.w3.org/TR/RDF/' as r,
'http://purl.org/dc/elements/1. 0/' as d,
default 'http://dmoz.org/rdf/'
)
SELECT
[Resource],
About,
Title,
CatID
FROM @x.nodes('RDF/Topic') a(x)
CROSS APPLY x.nodes('link') b(y)
CROSS APPLY @x.nodes('RDF/ExternalPage') c(z)
CROSS APPLY (
SELECT
y.value('@r:resource[1]','VARCHAR(100)') AS [Resource],
z.value('@about','VARCHAR(100)') AS About,
z.value('d:Title[1]','VARCHAR(100)') AS Title,
x.value('catid[1]','VARCHAR(100)') AS CatID
) F
WHERE [Resource] = About
/*
Resource About Title CatID
------------------ ---------------- ------------- ------
http://www.videos- http://www.video Kobus Petzer 724829
http://besigheidce http://besigheid Besigheid Cen 724829
http://besigheidce http://besigheid Besigheid Cen 724829
*/
View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials
I heard this question recently on one of the forums. The situation is as follows:
- Save an Excel 2003 document in a FILESTREAM column
- Read the data from the FILESTREAM column and try to open in Excel – It works as expected
- Save an Excel 2007 document in a FILESTREAM column
- Read the data from the FILESTREAM column and try to open in Excel – Excel thinks that the file is corrupted and will prompt for repair.
To SQL Server, both the documents are blob data and there is no chance of discrimination between the two. I suggested the OP to re-check the code that reads information from the FILESTREAM column and double check that the bytes are correctly retrieved from the FILESTREAM column.
He replied that the problem is reported to Microsoft Support and they acknowledged the problem. Further their investigation found that the same problem happens with Word 2007 and the support engineers are looking into the problem.
That sounded very strange. Why does SQL Server behave differently to two different BLOB data? I tried to create a repro. Here is what I did.
Step 1: Created an Excel 2007 document
Created the sample document and saved to a local folder.
Step 2: Created a FILESTREAM enabled Database
-- Drop Northpole Database
IF DB_ID('NorthPole') IS NOT NULL
DROP DATABASE NorthPole
GO
-- Recreate the database
CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\Data\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = 'C:\FS\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\Data\NorthPoleLOG.ldf')
GO
Step 3: Created a TABLE with FILESTREAM column
Use Northpole
GO
CREATE TABLE [dbo].[Documents](
[DocumentID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[DocumentName] VARCHAR(512),
[DocumentType] VARCHAR(50),
[DocumentData] VARBINARY(MAX) FILESTREAM NULL
)
Step 4: Loaded the document into the FILESTREAM column
-- Declare a variable to store the image data
DECLARE @data AS VARBINARY(MAX)
-- Load the image data
SELECT @data = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
'C:\Jacob\...\filestreamexcel.xlsx',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Documents ([DocumentID], [DocumentName], [DocumentType], [DocumentData])
SELECT NEWID(), 'FilestreamExcel.xlsx','excel', @data
Step 5: Retrieved the document from the FILESTREAM column and saved to a local disk
'Create a connection to the database
Dim ConStr As String
ConStr = "Data Source=JACOB-LAPTOP\SQL2008R2NOV;Initial Catalog=NorthPole" & _
";Integrated Security=True"
Dim con As New SqlConnection(ConStr)
con.Open()
'Retrieve the FilePath() of the image file
Dim sqlCommand As New SqlCommand()
sqlCommand.Connection = con
sqlCommand.CommandText = "SELECT DocumentData.PathName() AS PathName " + _
"FROM Documents WHERE DocumentName = 'FilestreamExcel.xlsx'"
Dim filePath As String = sqlCommand.ExecuteScalar()
'Obtain a Transaction Context
Dim transaction As SqlTransaction = con.BeginTransaction("DocumentTran")
sqlCommand.Transaction = transaction
sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
Dim txContext As Byte() = sqlCommand.ExecuteScalar()
'Open and read file using SqlFileStream Class
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)
Dim buffer As Byte() = New Byte(sqlFileStream.Length) {}
'sqlFilesTream.Length = 16896
sqlFileStream.Read(buffer, 0, buffer.Length)
'buffer.Length = 16897
Dim fStream As New FileStream("C:\..\filestreamexcel2.xlsx", FileMode.CreateNew)
Dim bw As New BinaryWriter(fStream)
bw.Write(buffer)
bw.Close()
fStream.Close()
'Cleanup
sqlFileStream.Close()
sqlCommand.Transaction.Commit()
con.Close()
Step 6: Open the document in Excel - Excel prompts for repair
Alright, so I am able to reproduce the problem.
The only reason for this to happen is that one or more bytes in the original document is modified when the document is stored or when it is retrieved. To narrow down the problem, I decided to check if the problem occurs at the time of storing the document.
To verify this, I did a ‘not-recommended’ approach. I went to the FILESTREAM data container (NTFS folder) and located the file. I copied the file to another folder and renamed to “.xlsx”. I then tried to open the file in Excel 2007 and it opened normally. It indicates that SQL Server did not modify the content of the file when storing the document into the FILESTREAM store.
The next step is to see what happens with Excel 2003. I repeated the same steps with an Excel 2003 file. When I retrieved the Excel 2003 document from the FILESTREAM store and opened it in Excel, it opened normally. Surprising!
At this point, it was certain that the document gets modified when reading from the FILESTREAM store. But the question is why is it happening only to the Excel 2007 document and not to the 2003 version. I was also curious to know what exactly gets modified. I tried to do a binary comparison of both the documents.
As you can see from the hex comparison results, there is an extra byte (with 0) getting added to the blob data when the document is retrieved from the FILESTREAM data store. That is why Excel 2007 complains and asks you to repair the document. Makes sense!
So, why is it not happening to Excel 2003? I did a similar comparison on the Excel 2003 document too. Surprisingly, I found that the same is happening to Excel 2003 too.
So we observed the following
- An extra byte is getting added to the blob data when it is retrieved.
- It happens to both Excel 2007 and Excel 2003 documents, which means that it is happening to all blob data.
- Excel 2007 complains but Excel 2003 does not.
So the question now is why is this extra byte getting added. I decided to check the length of the FILESTREAM data.
At first, I checked the length of the FILESTREAM data from TSQL.
select datalength(documentdata) as Length
from documents
/*
Length
--------------------
16896
*/
So the length of the document is 16896 bytes. I then decided to debug the code and found the following.
'Open and read file using SqlFileStream Class
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)
Dim buffer As Byte() = New Byte(sqlFileStream.Length) {}
'sqlFilesTream.Length = 16896
sqlFileStream.Read(buffer, 0, buffer.Length)
'buffer.Length = 16897
So it looks like I found the culprit. It was I. It could be a problem that most people face or it could be my lack of .NET expertise. People reading this with more .NET experience will know it better.
With my limited .NET knowledge, here is the workaround I did.
'Open and read file using SqlFileStream Class
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)
Dim buffer As Byte() = New Byte(sqlFileStream.Length - 1) {}
sqlFileStream.Read(buffer, 0, buffer.Length)
So the workaround is to create a byte array with one byte less than the length of FILESTREAM. That fixed the problem in my repro code.
I am eager to know if a similar problem has happened to anyone else and the fixe/workaround applied to get it fixed.
One of my friends approached me recently with a request to write a TSQL query that reads information from an RDF document. Here is the sample XML document he wanted to process.
<RDF xmlns:r="http://www.w3.org/TR/RDF/"
xmlns:d="http://purl.org/dc/elements/1. 0/"
xmlns="http://dmoz.org/rdf/">
<Topic r:id="Top/World/Afrikaans/Besigheid">
<catid>724829</catid>
<link r:resource="http://www.videos-sa.com" />
<link r:resource="http://besigheidcenturion.co.za/bc/" />
</Topic>
<ExternalPage about="http://www.videos-sa.com">
<d:Title>Kobus Petzer Videoproduksies</d:Title>
<d:Description>Vervaardiging van...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
</RDF>
The challenge is to read the resource names from the Link element and join it with the about attribute in the ExternalPage element. Here is the output required from the above XML document.
/*
Resource About Title CatID
------------------ ---------------- ------------- ------
http://www.videos- http://www.video Kobus Petzer 724829
http://besigheidce http://besigheid Besigheid Cen 724829
http://besigheidce http://besigheid Besigheid Cen 724829
*/
While writing the query to get the above result is pretty much straight forward, many people find it difficult because of the namespace declarations present in the XML document.
Here is the TSQL code that queries the above XML document and produces the required output.
DECLARE @x XML
SELECT @x = '
<RDF xmlns:r="http://www.w3.org/TR/RDF/"
xmlns:d="http://purl.org/dc/elements/1. 0/"
xmlns="http://dmoz.org/rdf/">
<Topic r:id="Top/World/Afrikaans/Besigheid">
<catid>724829</catid>
<link r:resource="http://www.videos-sa.com" />
<link r:resource="http://besigheidcenturion.co.za/bc/" />
</Topic>
<ExternalPage about="http://www.videos-sa.com">
<d:Title>Kobus Petzer Videoproduksies</d:Title>
<d:Description>Vervaardiging van...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
<ExternalPage about="http://besigheidcenturion.co.za/bc/">
<d:Title>Besigheid Centurion</d:Title>
<d:Description>Sakeportaal vir ...</d:Description>
<topic>Top/World/Afrikaans/Besigheid</topic>
</ExternalPage>
</RDF>'
;WITH XMLNAMESPACES(
'http://www.w3.org/TR/RDF/' as r,
'http://purl.org/dc/elements/1. 0/' as d,
default 'http://dmoz.org/rdf/'
)
SELECT
y.value('@r:resource[1]','VARCHAR(100)') AS Resource,
z.value('@about','VARCHAR(100)') AS About,
z.value('d:Title[1]','VARCHAR(100)') AS Title,
x.value('catid[1]','VARCHAR(100)') AS CatID
FROM @x.nodes('RDF/Topic') a(x)
CROSS APPLY x.nodes('link') b(y)
CROSS APPLY @x.nodes('RDF/ExternalPage') c(z)
WHERE y.value('@r:resource[1]','VARCHAR(100)') =
z.value('@about','VARCHAR(100)')
/*
Resource About Title CatID
------------------ ---------------- ------------- ------
http://www.videos- http://www.video Kobus Petzer 724829
http://besigheidce http://besigheid Besigheid Cen 724829
http://besigheidce http://besigheid Besigheid Cen 724829
*/
View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials
I was asked this question in one of the SQL Server forums recently. The OP wanted to generate an XML document with CDATA sections and NAMESPACE declarations.
SQL Server 2005 introduced WITH XMLNAMESPACES() which is very handy to generate namespace declarations in the output XML document generated by FOR XML. FOR XML EXPLICIT can be used to generate CDATA sections in the XML output. However, what is interesting is that you cannot use WITH XMLNAMESPACES and FOR XML EXPLICIT together.
Here is a FOR XML EXPLICIT query that generate CDATA sections as well as XML NAMESPACE declarations in the output XML document.
SELECT
1 AS Tag,
NULL AS Parent,
'Jacob' AS 'Person!1!Name!element',
'Hello world' AS 'Person!1!Greeting!CDATA',
'http://beyondrelational.com/xml.aspx'
AS 'Person!1!xmlns:x'
FOR XML EXPLICIT
Here is the output
<Person xmlns:x="http://beyondrelational.com/xml.aspx">
<Name>Jacob</Name>
<Greeting><![CDATA[Hello world]]></Greeting>
</Person>
I will be doing a webcast on error handling in SQL Server for PASS Application Development Virtual Chapter on 26 January 2010. You can find the session schedule and registration link at http://appdev.sqlpass.org/
This will be the ‘virtual’ version of the session “Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server” that I presented at Community Tech Days, Ahmedabad and SQL Saturday, New Jersey.
SQL Server MVP Erland Sommarskog has written two great articles that covers error handling in SQL Server in great detail. You can find his articles here:
- http://www.sommarskog.se/error-handling-I.html
- http://www.sommarskog.se/error-handling-II.html
One of sources of reference for this session was Erland’s error handling article and I recommend it as a must-read article for every SQL Server developer and DBA.
I was eagerly waiting to look at the session evaluation data after my presentation at PASS 2009 Summit (Seattle, WA) in November 2009. I had written a detailed post sharing my experience at PASS 2009 some time back. You can find the post here: http://beyondrelational.com/blogs/jacob/archive/2009/11/22/summary-of-my-experience-at-pass-2009-summit.aspx
XML Schema Collections
My presentation was on SQL Server XML Schema Collections where we discussed XSD and XML Schema Collections in detail. We discussed the basics of XML Schema Collections, saw a number of demos and had an interesting set of questions and answers.
Session Evaluation
Here is the data that I received from PASS Head Quarters recently (for the above session).
Some of the attendees have rated the session at speakerrate.com and here is the rating I received there. (http://speakerrate.com/talks/1679-sql-server-2008-create-powerful-xml-schema-collections-to-validate-your-xml-documents)
- Delivery: 4.87 out of 5.0
- Content: 4.93 out of 5.0
- Overall Rating: 4.9 out of 5.0
I would like to thank every one who attended my session and provided their feedback. Your feedback helps me to identify the areas that I need to focus to make the ‘blue bar’ longer next time :-).
PASS 2009 was a great experience, both as a speaker and as an attendee. It was a great opportunity to learn quite a lot, not only about SQL Server, but also about developing leadership and presentation skills.
Privacy and Security are two of the key concerns most of us have, when thinking about storing our mission critical data in the cloud. There are people who appreciate the benefits storing data in a cloud based data store such as SQL Azure. There are also people who warns about possible dangers in storing important data in a cloud store.
I recently wrote an introductory article to SQL Azure at sqlservercentral.com. You can read the article here. The discussion forum for this article saw a lot of very interesting arguments on the positive sides of cloud storage and the negative aspects of using a cloud store. You can follow the discussion thread here.
I just found two Microsoft White Papers that answers some of the questions that many of us have about the security and privacy of cloud data.
- Privacy in the Cloud Computing Era
- Securing Microsoft’s Cloud Infrastructure
I have got a very interesting questions to the readers. Did reading these documents bring any changes to your thoughts about SQL Azure or cloud computing in general?
Please feel free to share your thoughts.
This is a full day course that focuses on learning XSD and XML Schema Collections. SQL Server supports a limited subset of the XSD specification. This session will examine all the XSD features supported in SQL Server 2005 and all the new features added in SQL Server 2008.
Session 1: Getting started with XSD and XML Schema Collections This is an introductory session where the participants will develop basic XSD skills. It focuses on helping the attendees to learn the basics of XSD and make them capable of creating and using XML Schema collections to validate their TYPED XML data.
Session 2: Understanding Schema Components This session will take the attendees deeper into the building blocks of XSD. A very detailed over view of simple types, complex types, attribute groups and modal groups is given in this session. This session examines the attributes of element and attribute declarations in detail.
Session 3: XSD and Type Derivation This session starts with understanding XSD primitive and derived data types. It further walks the attendees through creating and using user defined simple and complex types. Finally it demonstrates type derivation in XSD and helps the attendees to derive types from simple and complex types, having different content models and deriving by extension and restriction.
Session 4: Advanced Schema Concepts This session focuses on some of the advanced schema concepts. It starts with a detailed discussion on the regular expression language supported by XSD, which can be used to perform a variety of pattern restrictions. It further takes the attendees into detailed discussion on the attributes of schema element declaration, element and attribute wild card declarations, lax validation support added in SQL Server 2008, XML Schema Collection metadata and the limitations of the XSD implementation in SQL Server.
Contact Us
I am currently on vacation after working non-stop for almost an year and I think I really needed a few days to relax. However, I found out again that “no-work is no-fun” and thought of building content an XML Training course that I always wanted.
It was really fun working on this and I did the first training with this course content at 4Axes InfoTech Services in Bangalore on 29th Dec 2009. It was a full day course that covered the XML support in SQL Server in detail.
You can find the details of the course content at http://beyondrelational.com/content/xmltraining.aspx
I have also setup an XML Resource Center where a lot of XML related information is posted. I have also setup an XML Forum and XML Group for people interested to participate in XML related discussions.
One of my friends emailed me this morning asking help for reading data from Google Analytics Atom feed, using Query with TSQL.
My friend sent me a sample XML document he obtained from Google Analytics and here is the query that reads information from it.
DECLARE @x XML
SELECT @x = '
<feed xmlns="http://www.w3.org/2005/Atom"
xmlns:dxp="http://schemas.google.com/analytics/2009"
xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/">
<entry>
<id>http://www.google.com/analytics/feeds/data?ids=ga:17042320&
ga:city=(not%20set)&ga:country=Canada&ga:date=20091204
&filters=ga:country%3D%3DCanada&start-date=2009-12-04
&end-date=2009-12-18</id>
<updated>2009-12-17T16:00:00.001-08:00</updated>
<title type="text">ga:country=Canada | ga:city=(not set) |
ga:date=20091204</title>
<link rel="alternate"
type="text/html" href="http://www.google.com/analytics" />
<dxp:dimension name="ga:country" value="Canada" />
<dxp:dimension name="ga:city" value="(not set)" />
<dxp:dimension name="ga:date" value="20091204" />
<dxp:metric confidenceInterval="0.0" name="ga:entrances"
type="integer" value="24" />
<dxp:metric confidenceInterval="0.0" name="ga:exits"
type="integer" value="24" />
</entry>
</feed>'
;WITH XMLNAMESPACES(
DEFAULT 'http://www.w3.org/2005/Atom',
'http://schemas.google.com/analytics/2009' as dxp
)
SELECT
x.value('(dxp:dimension[@name="ga:country"]/@value)[1]','VARCHAR(20)')
AS country,
x.value('(dxp:dimension[@name="ga:city"]/@value)[1]','VARCHAR(20)')
AS city
FROM @x.nodes('feed/entry') a(x)
/*
country city
-------------------- --------------------
Canada (not set)
*/
View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials
I shared my PASS 2009 experience in the previous post. One topic that I did not mention in the previous post, and wanted to write separately is about the PASS Outstanding Volunteer Award.
Outstanding Volunteers
As many of you know, PASS is run by volunteers all over the world. These volunteers spend a significant part of their time and energy towards supporting the SQL Server community through PASS. All those volunteers are doing an Outstanding job for the benefit of every SQL Server professional, including me.
I started volunteering with PASS in early 2007. I am having a great time working with those Outstanding volunteers, which contributes significantly to my formation as an active volunteer in the PASS SQL Server community.
PASS Outstanding Volunteer Award
PASS recognizes volunteers with a very coveted PASS Outstanding Volunteer Award every year. I was very fortunate to get the award this year along with a few other PASS Volunteers during the PASS 2009 Summit in November 2009, Seattle, Washington.
Co-owners of this award
Though this award was given to me, there are a few Outstanding Volunteers who equally share the credit of this award. I got this award because of my efforts for PASS in the Asian Region. However, this was not a one-man-show. There is an exceptional PASS Asia Committee that worked very hard for growing PASS in the Asian Countries. This award belongs to everyone who contributed or worked with the committee in one way or another, especially Pinal Dave who worked very closely with me to achieve the goals and Sujata Mehta and Malathi Mahadevan from the Asian Regional Committee. We would not have been able to succeed with our efforts without the continuous guidance and support from Greg Low (PASS Director – Chapters), Rushabh Mehta (PASS Execute Vice President – and President Elect 2009) and Blythe Morrow (all-in-all?).
Other Outstanding Volunteer Award Recipients
Other volunteers who received the Outstanding Volunteer Award this year are the following:
- Tim Ford
- Grant Fritchey
- Amy Lewis
The PASSion Award
The PASSion award is is PASS’s Top Honor which is described as “The PASSion Award is presented annually to a volunteer who demonstrates exceptional dedication to and enthusiasm for PASS and the SQL Server community. PASSion award recipients are nominated by PASS board members and volunteers and selected by the Board of Directors and the Volunteer Committee.” in PASS official website. This is the TOP honor any PASS volunteer can currently receive. This award was given to two exceptional volunteers this year:
- Charley Hanania
- Allen Kinsel
Thank You!
I would like to thank every one who directly or indirectly helped us to grow PASS in Asia. I look forward to meet the 2010 award recipients.
I made my previous blog post right before leaving for PASS 2009 Summit. I wanted to write a short summary of my experience at PASS ever since I was back, but a couple of critical work assignments kept me busy for the last few weeks.
Flight from New York to Seattle
I had a great flight from New York to Seattle. It was a non-stop 4.30 hours Delta flight which took me to Seattle around 12.30 PM on 1st November. During the flight, I read half of the book SQL Server 2008 Encryption written by my MVP friend Michael Coles and Forwarded by another MVP friend Pinal Dave. The other half I read during my flight back to NY :-). It is a great book and Michael has explained the encryption concepts right from its history, in such a way that experts as well as beginners will understand it alike.
In Seattle, once again
This was my third visit to Seattle and I was pretty much familiar with the city and the transportation. I had a comfortable travel from airport to my hotel. I did not take many pictures of the city this time, except for a photograph of the electric bus (to show my 6 year old daughter)
Getting to the Washington State Convention Centre
Washington State Convention Centre was all prepared to receive all the attendees and the people at PASS Head Quarters did a great job making all the necessary arrangements. The registration process was hassle free.
The PASS Ribbons
One of the very interesting accessory that attendees receive as part of their welcome kit is the colorful ribbons issued to each attendee based on their achievement, involvement and responsibilities with PASS. And here is what I got.
Jacob Sebastian, Pinal Dave and Jean Rene Roy
My PASS 2009 Presentation – Creating and using XML Schema Collections
This was my second PASS Summit and the key excitement for me was my own presentation on Tuesday evening. My presentation was on XML Schema Collections which explained the basics of XML Schema collections and had a number of very interesting demos.
My presentation was based on my recent book The Art of XSD – SQL Server XML Schema Collections where I have explained SQL Server XML Schema Collections from basics to advanced levels in close to 500 pages.
The session was quite exciting and we had an interesting time doing the Q&A. What was more exciting for me was the presence of Michael Rys, the Lead Program Manager for XML at Microsoft SQL Server Product Team.
Taking Part in Book Signing
Another interesting experience was the Book Signing program organized by the PASS Program Committee. I could meet a number of people working with XML Schema collections and grabbed copies of my book. It was my pleasure talking to them and autograph the copies of my book that they bought at the PASS book store.
Book Signing - Jacob Sebastian and Steve Simon
Book Signing – Jacob Sebastian and Craig Reitan
A glimpse at the First Version of SQL Server
I started my SQL Server career with SQL Server Version 6.5. During one of the key-note sessions, I got a chance to see the retail package of the first version of SQL Server. It was quite interesting and exciting.
The retail package of the first version of SQL Server
My Favorite Sessions at PASS Summit
I had a wonderful time attending a number of sessions at the PASS Summit. It was quite exciting to be in the sessions of my favorite speakers Michael Rys, Rushabh Mehta and Michelle Ufford.
Michael Rys presenting on SQL Server SPATIAL Indexes
Rushabh Mehta Presenting on SSIS Deployment Challenges
Michelle Ufford presenting on the Challenges in managing Very Large Databases
Experience Summary
My Experience at PASS Summit 2009 was great. Getting to Seattle from India usually takes around 36 hours which includes the flying time (around 21 hours) and the transfer/wait time at various airports (which obviously is not very easy). However, looking at what I gained from PASS Summit 2009, these difficulties are not even worth mentioning.
I learned quite a lot from the different technical sessions and the interactions I did with other industry experts. I had a chance to discuss a number of issues that I experienced in various assignments, with people having expertise in those specific areas.
I had a great time networking/meeting other industry experts. This was the first time I presented in a PASS Summit and that was a great experience too. The various events that the PASS HQ people organized, such as MVP Lunch (Birds of a feather lunch), Book Signing program, Microsoft Insiders sessions, meetings of volunteers, chapter leaders and regional mentors etc gave me a great experience and were very helpful in my growth as a member of SQL Server and PASS community.
Thank you PASS and thank you everyone for the time you spent to meet me and talk to me at PASS 2009 Summit.
More Posts
Next page »