Looking forward to giving my first presentation at a large event.  Come here me talk about using SSIS to handle Slowly Changing Dimensions in a data warehouse.  For more info on the event: http://www.sqlsaturday.com/eventhome.aspx?eventid=32.  I hope to see you there!

Giving a presentation on using SSIS to handle Slowly Changin Dimensions tomorrow night at the Space Coast SQL User Group.  Goto http://spacecoast.sqlpass.org for details.  If you can't make it, then try and catch me at SQL Saturday on the 17th - http://www.sqlsaturday.com/eventhome.aspx?eventid=32

The run this Thursday will be the dry run so come to see me bumble through the presentation.  Come the Saturday to see the polished version.

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held October 17, 2009 at Seminole Community College, 100 Weldon Boulevard, Sanford, FL 32773. Admittance to this event is free, but to to the challenging nature of fund raising this year there will be a $10 fee due at registration to cover the cost of lunch (a great boxed lunch from Jason's Deli). All other costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event.

More Info: http://www.sqlsaturday.com/eventhome.aspx?eventid=32

This is impossible.  Don't try.  There are a few 'hacky' ways to do this, but why? It can be doneif you slightly modify the requirement. If you can abandon the use of a stored procedure for a User Defined Function, you can use an Inline Table-Value User-Defined Function. This is essentially a stored proc (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement. Here's a good quick article on it and other user defiend functions. If you still have a driving need for a Stored Procedure, you can wrap the Inline Table-Value User-Defined Function with a stored procedure. The stored proc just passes parameters when it calls select * from the Inline Table-Value User-Defined Function.

So for instance, you'd have a Inline Table-Value User-Defined Function to get a list of customers for a particular region:

CREATE FUNCTION ufCustomersByRegion (@RegionID int)
RETURNS TABLE
AS
RETURN SELECT * FROM customers WHERE RegionID = @RegionIDGO

You can then call this function to get what your results a such:

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

If you still need a stored proc, then wrap the function as such:

CREATE PROCEDURE uspCustomersByRegion
 @regionID int 
AS
BEGIN
 SELECT * FROM CustomersbyRegion(@regionID);
END
GO

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the Inline Table-Value User-Defined Function in the stored proc, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

Just a quick note on a small Codeplex project I am starting.  After having done some BizTalk RFID work, I found the supplied code sample for a duplicate eliminator to be an excellent example of a custom event handler, but a poor implementation of a duplicate tag eliminator.  This codeplex project is a little better thought out dupicate tag eliminator with a few added features.

Go take a look if interested and let me know what you think:  BizTalk RFID Better Duplicate Tag Eliminator

So we've been being pestered by our DBAs (you know the ones) to move our BizTalk Server 2006's Data Tier from our aging SQL Server 2000 Cluster to our new shiny SQL Server 2005 Cluster.  The end of our fiscal year was approaching so we brought in a consultant to help us. I highly recommend Catapult Systems if you need any BizTalk help. They're smart guys and have reasonable rates (in comparison to Oracle, who doesn't?).  We met with our consultant Mark and gave him the tour of our environment and he came back a week later with a solid procedure... BAM, MsgBox, WSS, Jobs, DBs... all of it.  We executed the procedure along with enough red lines to maintain their human status.  All seemed to go well - hosts started, jobs started, Group Hub back up, but wait!  Couple of jobs are failing?  Hmmm.  Update some physical paths in the backup job and all is well.  Wait. Tracked Message Copy keeps failing: 

Executed as user: HARRIS\MLBSQLVLM1. Could not find server 'mlbxxx\sql3' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202).  The step failed.

Now this is very confusing.  We're all on one server.  No need for any linked servers.  Why does it think mlbxxx\sql3 is a linked server it can't connect with.  Is it a permission issue?  I checked and double checked the database configuration from the old server and nothing jumped out at me.  OK.  Time to step into the deep dark reaches of the BizTalk stored procedures... the starting point: bts_CopyTrackedMessagesToDTA at line 70: