# Monday, December 29, 2003

.NET Stored Procedures in Yukon

You have heard about .NET Stored Procedues in Yukon. You have also heard about the power of TSQL. So which to use, TSQL or .NET? Here is the best rule of thumb. TSQL is for data access. Just about 99% of the time you will want to use TSQL to return data. When you want to do heavy CPU computational "things" you will want to use a CLR Language like C# or VB .NET. Here are some scenarios where a CLR language is much better:

1. Your queries have very complex procedural logic and require intensive computation
2. You need to access external resources like a text file or event log
3. You want to take advantage of .NET Base class libraries (like RegEx for example)
4. You want to leverage you database code across multiple projects

How does this work? You create a .NET assembly in Visual Studio and use what is called the In-Process Service Provider, or ADO in-proc for short. To use the In-Process Service Provider you can use the System.Data.SqlServer namespace to get access to all the classes in ADO In-proc. Then using your .NET language and ADO In-Proc you write some stored procedures, etc and then compile the assembly and bind it to your database. Now you can call your new database objects. Let's take a look at how to do that. Let's get started. Open up Visual Studio (you will need a beta of the next version of Visual Studio) and select "SQL Server Project" from the New Project dialog box. After you set your project type to SQL Server Project, Visual Studio asks you to set a "Add Database Reference" or basically set an active connection. Lastly, add a new item to your Visual Studio project like normal and choose a "stored procedure" from the dialog.

Ok, we are now ready to start coding. We are going to return a single value back to the calling application by querying the database using the SqlServer namespace classes and returning a single value. The first thing that you will notice is the namespaces used, by default when Visual Studio loads up a Stored Procedure project it adds the three new namespaces for you:

sing System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

These are the namespaces that you will use in your .NET Stored Procedure. The most important one is the SqlServer namespace. Here is a simple SP.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public class CSingleValue
//My first .NET Stored Procedure! :)
{
[SqlProcedure]
public static int SingleValue()
{
//Use a SqlCommand object and the SqlContext
 //SqlContext will always return you an active
 //command (or what you ask for) from the current
 //database that exists for your project
 //notice a lot like SqlClient, but no connection
 SqlCommand cmd = SqlContext.GetCommand();

 //now that we have an active command we are going
 //to set the command text to return a single value
 //here we are going to send back the total sales
 //figure in the Northwind database
 cmd.CommandText = "select Sum((UnitPrice*Quantity)-Discount) From [Order Details] As TotalSales";

 //now we have to execute a datareader to get the single row
 SqlDataReader dr = cmd.ExecuteReader();

 //our datareader has the value in it
 //now do something with it, like return it to
 //as the result of the procedure
 dr.Read();

 int intTotalSales = (int)dr["TotalSales"];

 //return the  value
 return intTotalSales;
}
};

Let's dissect the code. First you use a SqlCommand from the SqlServer namespace and create it via the SqlContext object. SqlContext is your friend. For starters it will always give you the current connection of the calling function and in this case set all of the command's behaviors due to the scope of the connection. The next line of code sets the CommandText property of the Command object to a valid SQL statement. Then we create and execute a SqlDataReader and read the results into a variable and return that value.
Easy no? Well we are not there just yet.  You have to bind your stored procedure to your database. Visual Studio should do this for you, but here is how to manually bind it.

Create Assembly SqlServerProject1
From 'C:\SqlServerProject1.dll'
With Permission_Set=SAFE

You have three options with the permissions:
1. Safe
2. External_Access
3. UnSafe

Safe will only permit you access inside the database objects, unable to access the outside world. The assembly must be type-safe. External_Access gives you all of safe, but allows you to access the outside world, files, system resources, etc. UnSafe does not have to be type-safe and can call unmanaged code. Now you have to bind your procedure to the database, once again Visual Studio should do this for you, but here is how to manually do it:

Create PROCEDURE SingleValue
As
External Name SqlServerProject1:[SqlServerProject1.CSingleValue]::SingleValue

Calling your stored procedure is quite simple, just call it like any other stored procedure in SQL Workbench or in client side ADO .NET code:

Exec SingleValue

 

posted on Monday, December 29, 2003 5:37:21 PM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Saturday, December 27, 2003

Ho, Ho, Ho

Happy Holidays!

posted on Saturday, December 27, 2003 3:33:41 PM (Eastern Standard Time, UTC-05:00)  #    Comments [14] Trackback
# Monday, December 22, 2003

Microsoft Office 2003 Install/Setup, A- Grade

 

I turned on my computer this morning and something strange happened, my Outlook and Excel were just waaaay too slow. I have a superfast machine with over a gig of RAM, should be no problems. I started poking around and realized that I was still running the Office 2003 beta! So I inserted the RTM Office 2003 DVD and held my breath.

 

Well it did a great job. Install prompted me to uninstall automatically the old version. (This is where I got scared, I have tons of settings I wanted to keep.) Installation offered me the option of a “complete” install which is cool. After the install was complete (no reboot required), all of my settings were restored to where they were when I left, except the email signature (which was preserved but not set up by default after installation.) I had a ton of custom settings, so this was quite impressive.

 

Overall Setup/Installation gets an A-. Good job MSFT.

posted on Monday, December 22, 2003 6:27:41 PM (Eastern Standard Time, UTC-05:00)  #    Comments [21] Trackback
# Saturday, December 20, 2003

Open Letter to Real Networks CEO

 

Dear Robert Glaser,

 

I use WinAmp to listen to my illegal MP3s. I don’t use Microsoft’s Media Player because it sucks. Nor do I use your crappy software because, well it sucks. Big time. (While on the topic of your sucky software, what is the deal with the installation, you have to fill out my life history, etc.)

 

Your lawsuit is a sham. I did a find and replace with “Netscape” and “Real Networks” in the court papers you filed and guess what? I know why your company is suing Microsoft, it is because you have a personal vendetta against Bill Gates (are you mad that he yelled at you in that meeting in 1995 and you quit your well paying MS job?) . Get real.

 

How about this one? Build better software and drop the damn lawsuit.

 

Sincerely,

Stephen Forte

posted on Saturday, December 20, 2003 3:05:19 PM (Eastern Standard Time, UTC-05:00)  #    Comments [3] Trackback
# Friday, December 19, 2003

Exception Handling in Yukon Transactions

 

Ok, it is Friday, so an easy one to digest, but quite powerful. Yukon has made a major improvement in Error Handling inside of TSQL transactions. You can now catch Transaction Abort Errors using the TRY / CATCH model without any loss of the transaction context. This makes me very happy. The syntax is here:

 

BEGIN TRY

sql statement

END TRY

 

BEGIN CATCH TRAN_ABORT

    sql statement

END CATCH

See my article on SQLJunkies.com.

posted on Friday, December 19, 2003 1:29:41 PM (Eastern Standard Time, UTC-05:00)  #    Comments [16] Trackback
# Thursday, December 18, 2003

New Data Types in Yukon

 

There are a few very cool new types. The most noticeable are categorized as: DateTime types, varchar(max), and XML. There are a few datetime datatypes, but the most interesting new DateTime datatypes are the UtcDateTime, Date and Time types The UtcDateTime datatype is time zone aware and is used in scenarios where the server and clients span multiple time zones. It uses an expanded range (from 1 AD for 9999 AD) and precision (100 nanoseconds.) The Date datatype is useful when you just want to store the simple date and has the same expanded range. The Time is a new datatype for Time only and has expanded precision (100 nanoseconds.) Varchar(Max) is an extension to varchar, nvarchar, varbinary that will store data up to 2GB and is an alternative to text/ntext/image. It uses the Uses MAX size specifier, like so:

 

CREATE TABLE TablewithMaxColumn

(Customer_Id int, CustomerLifeStrory varbinary(max))

 

All string functions operate on varchar(max), you can use the TSQL SUBSTRING to read chunks and the UPDATE statement has been enhanced to support update of CHUNKS.

 

To accommodate the growing importance of XML Yukon includes an XML datatype. In the past developers would store XML in a varchar or text/ntext field. That was not optimal since the XML was not in its native format and you could not query its contents efficiently. The new datatype allows developers to store XML in its native format (well actually it is stored in a BLOB field) and gives you the ability to via XQuery to query parts of the XML field.

 

The XML datatype is a full blown TSQL citizen, able to have table columns and participate in a CAST or CONVERT statement. You can even use the FOR XML statement to convert tabular data into XML from a SQL query:

 

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Customers FOR XML AUTO, TYPE)

SELECT @xmlData

 

As expected the results of a row look like this:

 

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>

 

You can also insert this XML data into a new XML field in a table and have data validations based on XSD be enforced by Yukon. Pretty cool stuff.

See my article on SQLJunkies.com.

posted on Thursday, December 18, 2003 3:14:36 PM (Eastern Standard Time, UTC-05:00)  #    Comments [16] Trackback
# Wednesday, December 17, 2003

Common Table Expressions in Yukon

 

A Common Table Expression closely resembles a non-persistent view. A CTE is a temporary named result set that you define in your query that will be used by the FROM clause of the query. Each CTE is only defined once (but can be referred to as many time as it is in scope) and lives for as long as the query lives. CTEs can also be used to perform recursive operations. Here is the syntax to create a CTE:

 

With <name of your CTE>(<column names>)

As

(

<actual query>

)

 

Select * from <name of your CTE>

 

An example of a simple CTE is shown here:

 

With LondonCustomersCTE

As

(Select * From Customers Where City='London')

 

Select * From LondonCustomersCTE

 

Here is an example where we get a list of all the count of Customers’ orders in the orders table as a CTE and then a simple inner join with the Customer’s table to return the full name of the customer. (Sure this can be done without a CTE, but think about the ease of aggregating in the CTE only, it makes your code much easier to deal with.)

The code is here:

 

With CustomerOrderCountCTE(CustomerID, OrderCount)

As

(

     select CustomerID, Count(*)

     from orders

     group by CustomerID

)

 

Select CU.CompanyName, City, COC.OrderCount

From Customers As CU Inner Join CustomerOrderCountCTE As COC

     on CU.CustomerID = COC.CustomerID

Order By COC.OrderCount Desc

 

The true power of CTEs are when you use then recursively. A recursive CTE is constructed from a minimum of two queries, the first or Anchor Member (AM) is a non-recursive query and the send is the recursive query or the Recursive Member (RM). The AM and RM are separated by a UNION ALL statement. Here is the syntax:

 

With SimpleRecursive( field names)

As

(

     <Select Statement for Anchor Member>

 

     Union All

    

     <Select Statement for the Recursive Memember>

)

 

Select * From SimpleRecursive

See my article on SQLJunkies.com.

posted on Wednesday, December 17, 2003 12:25:22 PM (Eastern Standard Time, UTC-05:00)  #    Comments [4] Trackback
# Tuesday, December 16, 2003

TOP Enhancements in Yukon

 

In previous versions of SQL Server TOP allowed you to limit the number of rows returned as a number or percentage in Select Queries. I use this all the time. It has gotten more flexible in Yukon where TOP can be used in Delete, Update and Insert queries. What is even cooler is that Yukon will allow you to specify the number of rows (or percent) by using variables or subqueries. I love the ability to dynamically set the TOP, so much that I have started writing Stored Procedures that accept a NumberofRows parameter like so: 

 

Create Procedure usp_SEL_ReturnTopOrders

@NumberofRows int

As

Select TOP (@NumberofRows) OrderID

From Orders

Order By OrderID

 

Executing the stored procedure is easy, just pass in how many records that you want (in this case it is 100):

 

usp_SEL_ReturnTopOrders 100

 

Using a subquery can be very powerful when you are doing things on the fly. A real simple example to demonstrate the concept is show here, we are getting the TOP n customers based on how many records we have in our Employees table:

 

Select TOP (Select Count(*) from Employees) *

From Orders

See my article on SQLJunkies.com.

posted on Tuesday, December 16, 2003 4:31:23 PM (Eastern Standard Time, UTC-05:00)  #    Comments [8] Trackback
# Monday, December 15, 2003

PIVOT Data in Yukon (RIP Case and Rozenshtein)

 

Let’s face it, users usually want to see data in tabular format, which is a bit of a challenge when we usually store data in a highly relational form. PIVOT is a new TSQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional “Crosstab” query without using CASE, Rozenshtein or subqueries. You have always been able to do this easily in Access with Pivot and Transform, but SQL Server was always a step behind.

 

Using PIVOT is easy. First in your select statement you need to specify the values you want to pivot on, in the following example, we will use the Year of the Order. Our FROM clause looks normal except for the PIVOT part. The PIVOT statement creates the value we plan on showing in the rows of the newly created columns, in this case I am using the aggregate SUM of the TotalAmount (a calculated field in our FROM clause). Then we have to use the FOR operator to list the values we are going to Pivot on in the OrdersYear column.  The example is shown here:

 

SELECT CustomerID, [1996] AS 'Y1996', [1997] AS 'Y1997', [1998] AS 'Y1998', [1999] as 'Y1999'

FROM

     (SELECT CustomerID,OD.UnitPrice * OD.Quantity - OD.Discount AS TotalAmt,

          Year(dbo.Orders.OrderDate) as OrdersYear

          FROM dbo.Orders INNER JOIN dbo.[Order Details] As OD

              ON dbo.Orders.OrderID = OD.OrderID)

          As Orders

     PIVOT

     (

          SUM(TotalAmt)

          FOR OrdersYear IN([1996], [1997], [1998], [1999])

     ) AS XTabData

Order BY CustomerID

 

The results look like this:

CustomerID

Y1996

Y1997

Y1998

ALFKI

NULL

2293.25

2301.9

ANATR

88.8

799.75

514.4

ANTON

403.2

6451.15

660

AROUT

1379

6588.4

5838.4

BERGS

4324.4

14532.25

8108.5

BLAUS

NULL

1079.8

2160

BLONP

9986.2

8371.05

730

BOLID

982

4035.3

279.8

BONAP

4202.35

12460.7

7184.7

 

That is all there is to it. Of course this is a real simple example to show you the new concept, you can then of course get more sophisticated aggregates and even use Common Table Expressions in the FROM clause. Also you can use the UNPIVOT operator to normalize data that is already pivoted.

 

See my article on SQLJunkies.com.

posted on Monday, December 15, 2003 4:44:32 PM (Eastern Standard Time, UTC-05:00)  #    Comments [3] Trackback
# Friday, December 12, 2003

A Dutch Houseboat

 

So yesterday I was on a true houseboat in the Netherlands. Apparently my pal Remi grew up on a houseboat and we went to his childhood home to drop off something to his parents after he picked me up at the airport. It was very cool and you could really feel the house rock. What a treat.

posted on Friday, December 12, 2003 9:54:47 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Thursday, December 11, 2003

An Access Killer?

 

The reporting engine in Access is almost 10 years old with major modification. It is that good. Now something better has come around. SQL Server Reporting Services, due out any week now, could be an Access killer. After demoing it to the London Database Forum last night, we all realized that it is pretty cool. An Access Report Killer, yes it is (but Access will still be better for local disconnected reporting). Attention Access developers, SSRS is based off the old Access report designed, so you have a leg up, so go learn it. If you don’t know about SQL Server Reporting Services, go download it from Microsoft now!

 

And Microsoft: Its about time!

posted on Thursday, December 11, 2003 9:29:01 AM (Eastern Standard Time, UTC-05:00)  #    Comments [11] Trackback
# Monday, December 08, 2003

A killer Infield

 

Japanese star Kazuo Matsui decided to join the New York Mets, accepting the Amazins’ three-year offer today. Kazuo has been called a "faster, stronger version of Inchiro.” Now the Mets will have two killer shortstops. My guess is the Jose Reyes will go to second. With Matsui and Reyes, the Mets now have two switch-hitters at the top of their lineup who have speed, power, high OBPs, high BAs and great defensive skills. Playoff bound? Not yet. A major improvement, sure.

 

Now the true question: Is New York City ready for two Matsui’s??

posted on Monday, December 08, 2003 6:27:17 PM (Eastern Standard Time, UTC-05:00)  #    Comments [12] Trackback
# Saturday, December 06, 2003

Molloy Boys Take Bubble Baths…

 

I was changing this at Kenny Anderson last night while I was in the front row of the Sonics loss against Indiana. He must not have heard this chant in over 15 years. You see Kenny is a fellow Queens boy who attended the (all boys) Arch Bishop Molloy which was a arch rival to my High School St. Francis Prep and we use to taunt him with this chant in his 4 years as the #1 High School player in the country. Kenny is the best passer in the NBA and along with Reggie Miller just housed the Seattle Super Sonics. I had my fun at Kenny’s expense, but he got the last laugh.  

posted on Saturday, December 06, 2003 3:59:35 PM (Eastern Standard Time, UTC-05:00)  #    Comments [14] Trackback
# Friday, December 05, 2003

SCO is Desperate

 

SCO, who is suing IBM over Linux (and is threatening more lawsuits against corporate Linux users), yesterday attacked the GNU GPL (General Public License) in which Linux is distributed. In an open letter from SCO CEO Darl McBride, SCO said the the GPL is in violation the United States Constitution (and also some U.S. copyright and patent laws).  

 

They are bringing in the US Constitution to this debate? Please.

 

Here is my open letter to SCO:

 

Dear Darl McBride,

 

Drop the damn lawsuit already.

 

Regards,

Stephen Forte

New York, NY

posted on Friday, December 05, 2003 6:52:50 PM (Eastern Standard Time, UTC-05:00)  #    Comments [14] Trackback
# Thursday, December 04, 2003

Open Season for Hackers on Linux Systems

Yet another high-profile attack on Linux- someone broke into one of the servers used to distribute versions of Gentoo Linux on Tuesday.

posted on Thursday, December 04, 2003 11:18:11 PM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback

CLR v TSQL Stored Procedures

 

I am here in Redmond chatting with a few fellow .NET gurus about SQL Server and Yukon’s ability to create stored procedures with any CLR language. We have decided, TSQL for everything, except when you 1. need something from the .NET framework like encryption or RegEx and 2. you are doing something that is a very CPU intensive operation.

 

So why bother with the CLR inside of Yukon? I think that the convergence is a good thing, but TSQL will be around for a long time (as it should be). It is not an every day occurrence that you will use the CLR (think replacement for extended stored procedures), but when you need it, you will be very thankful.

 

So I am going to make a prediction that a lot of people will try to create their whole app around CLR stored procedures when they fire up Yukon for the first time. Please don’t fall in this trap!

posted on Thursday, December 04, 2003 10:18:30 PM (Eastern Standard Time, UTC-05:00)  #    Comments [2] Trackback
# Wednesday, December 03, 2003

Linux For the Masses?

 

Linux has always been a back end server and workstation for uber geeks only. A few workstation class systems have come out in the past but a compelling one is now released to the world, the Java Desktop System (JDS) from Sun Microsystems.

 

While this shows how desperate Sun is for any kind of revenue and proof of their relevance, JDS is pretty cool. It combines Linux, Mozilla, GNOME and StarOffice in one unit. Selling for around $150, this creates true competition to the Windows/Office offering. (Attention DOJ and EC, drop your suits. J)

 

So, does this mean corporate American (and elseware) will run out in droves and drop Windows/Office for JDS? Probably not. There are migration, document conversion, TCO and other concerns, but enough will make the move to JDS to scare the crap out of Redmond. I have to say that this is exciting. True competition is only good for the consumer, Microsoft and Sun will create better software and offer it cheaper than it is today, that is Adam Smith at work.

posted on Wednesday, December 03, 2003 6:42:00 PM (Eastern Standard Time, UTC-05:00)  #    Comments [17] Trackback
# Tuesday, December 02, 2003

Serious Security Vulnerability in Linux Kernel

 

In light of the recent attack on the Debian Project’s servers, researchers have found some serious security vulnerabilities in the Linux Kernel. This vulnerability can enable a hacker to gain root access to a machine. (For those of you Windows only types, this is like the Admin). Once you have root, you can do anything, root is the uber user.

 

The vulnerability is in the form of an integer overflow in the brk( ) system call (memory-management). When the call invokes the do_brk( ) function, using user-supplied address and length variables, the call does not check for integer overflows when adding the variables together.

 

I am not one to dwell on an email virus or something like that. But fundamental problems in the Kernel are pretty catastrophic. Let me explain why. Because the vulnerability is in the OS kernel itself, the problem affects just about every distribution of the operating system from kernel version 2.4.0 through 2.5.69. That means that every version of Linux installed on every computer on the planet needs to be upgraded when a fix is available.

 

I am not going to lower myself to the mudslinging of the MS v Linux silliness, but am going to remind all the folks out there who tell us that Linux is free. The Linux community will rally fast to address this very serious security issue and everyone everywhere will have to upgrade their Linux installs worldwide (including Nicole’s machine in my living room sitting on my nicely secure Windows network). That is a total cost of ownership (TCO) issue. Nothing is free. It costs time and money to do this (just as it does when you apply a MS patch or upgrade).

posted on Tuesday, December 02, 2003 5:14:17 PM (Eastern Standard Time, UTC-05:00)  #    Comments [7] Trackback