SQL Server Integration Services has a significant performance issue with large result sets returned by SQL’s “FOR XML” clause.  The problem is due to memory consumption and the way SSIS’s data flow engine handles row-based results.  When SSIS receives a data stream, it buffers a portion of the stream, performs the necessary operations on the rows in the buffer, clears the buffer, and repeats the process until there is no more data remaining in the stream.  The problem with the way FOR XML returns data is that it doesn’t return rows, but rather a result set with a single row, which is seen by SSIS as a large Binary Large Object ( BLOB). 

The initial reaction many developers have to this issue is to decrease the DefaultBufferSize property of data flow task.  Unfortunately, this won’t help at all since the buffer is not able work with fractional rows, which means the buffer(s) will continue to fill beyond their threshold until a whole number of rows is reached.  With a single row, this means the entire stream will be loaded into memory.  SQL 2005 and above provide a solution to this problem with the ability to shred the xml data type into individual rows.  Compare the result returned by the below queries.

DECLARE @XML xml

SET @XML = (SELECT
	UserID,
	FirstName,
	LastName
FROM
	Users
FOR XML PATH('User'))
SELECT @XML AS UserXML

DECLARE @XML xml

SET @XML = (SELECT
	UserID,
	FirstName,
	LastName
FROM
	Users
FOR XML PATH('User'))


SELECT nref.query('.') AS UserXML
from @XML.nodes('//User') AS R(nref)

SSIS can efficiently buffer the result set returned by the second query, which will enable large sets to be processed without issue.

If you receive the following error on a front-end web server of a WSS 3.0 farm while performing a search against Search Server 2008

Object reference not set to an instance of an object.   at Microsoft.Office.Server.Search.WebControls.CoreResultsWebPart.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

First make sure you have the front-end web server component of Search Server 2008 installed on the server where you are performing the search.  Next, add the URL you are using to access the server in your alternate access mappings.  Within Central Administration, navigate to Operations -> Global Configuration -> Alternate Access Mappings.  "Select Add Internal URLs".  Select your web application in the Alternate Access Mapping Collection drop down list.  Specify the path and port you use to access the application (example:  http://machinename01:80).  Click Save.  There is no need for an IIS reset.  The change should immediately take effect .

A scenario I've repeatedly encountered in database development is to create a stored procedure that accepts a range of values as a comma delimited list and returns a result set that contains at least one of the values from the list.  To do this, the parameter value must be parsed as something that can be understood by the IN clause.  I prefer to turn the delimited list into a single tabled column.  Because this task is so common, I wrote the following function that takes a comma delimited string and returns a table variable.

CREATE FUNCTION [dbo].[fnCommaDelimitedToTable]
(
        @CommaDelimited varchar(5000)
)
RETURNS @returntable TABLE
(
         FilterColumn varchar(50)
)
AS
BEGIN
SET @CommaDelimited = REPLACE(@CommaDelimited, ', ', ',')
WHILE CHARINDEX(',' , @CommaDelimited) > 0
BEGIN
        INSERT INTO
                @returntable
        VALUES
        (
                SUBSTRING(@CommaDelimited, 0, CHARINDEX(',', @CommaDelimited))
        )

        SET @CommaDelimited = SUBSTRING(@CommaDelimited,  CHARINDEX(',', @CommaDelimited) + 1,
LEN(@CommaDelimited))
END
-- Insert the last one, or if there was only one supplied.
INSERT INTO
        @returntable
VALUES
(
        @CommaDelimited
)
        RETURN
END

With this function you can now write queries like

SELECT *
FROM
    Customers
WHERE 
    Name IN (SELECT SearchFilter FROM dbo.fnCommaDelimitedToTable(@DelimitedNames))

In SQL Server Integration Services (SSIS) the DataReader Source component is analogous to ADO.NET's DataReader classes.  It retrieves a Binary Large Object (BLOB) stream from the data source and is therefore efficient and the least expensive option in terms of memory.  Having said that, there are times when it is convenient to convert the returned BlobColumn into a string object.  Within a script component, the following will build a binary array from the BLOB stream and then encode the binary array to a Unicode string.

Dim result As String = _
System.Text.Encoding.Unicode.GetString(Row.FieldName.GetBlobData(0, CInt(Row.FieldName.Length)))

Again, one should be cautious when doing this.  It is similar to using the DataAdapter class to convert a DataReader to a DataSet, so depending on the size of the data, this may or may not be a good idea.

Microsoft is offereing Visual Studio 2008 along with Windows Server 2003, Expression Studio, and XNA game studio at no cost to college students.  Visit the Microsoft DreamSpark site to register and download .  Enjoy.

Disabling a range of dates on a calendar control is a great way to reduce data-entry error.  For example, imagine a scheduling system for an organization whose doors are open Monday through Friday.  Ideally, any calendar controls should disable Saturday and Sunday to prevent users from accidentally choosing them.  This can be accomplished by taking advantage of the ondayrender event of the calendar control.  First, in your aspx  markup, wire up a method to the event.

<asp:Calendar Visible="false" ID="DisabledWeekendsCalendar" runat="server" ondayrender="DisabledWeekendsCalendar_DayRender"></asp:Calendar>

Finally, in your .NET code, create the method which checks to see if a day is a weekend, and if so disables it.

protected void DisabledWeekendsCalendar_DayRender(object sender, DayRenderEventArgs e)
    {
        if (e.Day.Date.DayOfWeek == DayOfWeek.Saturday || e.Day.Date.DayOfWeek == DayOfWeek.Sunday)
        {
            e.Day.IsSelectable = false;
            e.Cell.ForeColor = System.Drawing.Color.Gray;
        }
    }

With Microsoft SQL Server 2005, paging on the database side is simple.  There is a built-in ROW_NUMBER function designed specifically for this task.  However, versions of SQL Server prior to 2005 do not have this capability.  Below is one way to implement similar functionality in pre-2005 versions. 

SELECT 
    TOP X * 
FROM 
    Records 
WHERE 
    RecordID NOT IN (SELECT TOP Y RecordID FROM Records)

You must replace X and Y with actual integer values, Unfortuantly, due to sql sytanx, you can’t use varaibles.  The records returned from the above query will follow

Y + 1 = Beginning record
X + Y + 1 = Ending Record

So 10 and 20 plugged in to X and Y respectively would return records 21 thorugh 31.

Microsoft just released the “Microsoft BizTalk Server Operations Guide” document.  They describe it as, “…detailed information for planning a BizTalk Server environment, as well as recommendations and best practices for configuring, testing, maintaining, monitoring, and optimizing this environment.”  You can view it from MSDN or download a copy for offline use.

To debug a piece of .NET code called by the BRE you must first attach the Visual Studio debugger to the BRE process.   To accomplish this, open the debug menu and select “attach to process…” and select the Microsoft.RuleComposer process.  Now you can put breakpoints on the desired lines of .NET code and when the rule executes from the BRE, the execution will catch at the first breakpoint.

Facts can be asserted to the BRE from .NET code simply by passing them as an argument to the policy’s execute method which takes a variable number of parameters.  For example,

Microsoft.RuleEngine.Policy policy = new Microsoft.RuleEngine.Policy(“PolicyName”);
Policy.Execute(Object1, Object2,…);

There are three types of facts used by the BRE:  An xml document, a Database table, and a .NET class.  The latter two have a couple peculiarities about them which require further explanation. 

Asserting a Database Table

When using a database table, the BRE expects an object of type Microsoft.RuleEngine.DataConnection to be asserted, which tells the BRE where to find the table.  This is achieved with the below code.

//Create the DataConnection
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(“connectionstring”);
Microsoft.RuleEngine.DataConnection dconn = new Microsoft.RuleEngine.DataConnection(connection);

//Create the policy and assert the DataConnection
Microsoft.RuleEngine.Policy policy = new Microsoft.RuleEngine.Policy(“PolicyName”);
Policy.Execute(dconn);

Static .NET Methods

Calling a static .NET method from the BRE can be achieved without asserting an instance of the class by setting the the StaticSupport (DWORD value) registry key located under HKEY_LOCAL_MACHINE\Software\Microsoft\BusinessRules\3.0 to a value of 1.  Without this registry entry, static method calls within the BRE will require asserting instances of the class that contain the method.