T-SQL Split

I still have seen developer asking me how to Split in SQL Server, although we can enable CLR Integration in SQL Server, here is the classic one from SQL Server Magazine that I have used since SQL Server 2000 and it is very powerful, Treat Yourself to Fn_Split() - This UDF lets you emulate VB's Split() function and parameter arrays

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can抰 find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0 
END
END

END

RETURN
END
GO

-- Sample Code for Using Fn_Split() in Pubs to Emulate a Parameter Array

Use Pubs

CREATE  PROCEDURE procPubsTitleView

@sAuthors varchar(8000)
AS
SELECT title, au_lname, price FROM Pubs.dbo.titleview
JOIN dbo.fn_Split(@sAuthors, ',') 
ON au_lname = value
RETURN
GO

Is BizTalk dead?

One of the frequently asked question - Is BizTalk Dead?

The question arises with the announcement of Dublin able to makes long running workflow implemented in Windows Workflow Foundation (WF) persisted in SQL Store, sounds like Biztalk.

More details at A First Look at WF 4.0, “Dublin”, and “Oslo” http://msdn.microsoft.com/en-us/library/dd200919.aspx

I have implemented BizTalk projects in multinational organizations with industry standard like SWIFT, my personal opinion is, no doubt WF, Dublin and Oslo are evolving technologies in application development, but BizTalk still playing as a strong leader in Integration technologies for EAI and B2B, reason being are

• B2B industry standard like EDI, SWIFT, RosettaNet contains hundred to thousand schemas and only available in BizTalk as Accelerators. I do not think developers can create all these schema parser easily within short time frame
• BizTalk has many built in Network, Data and Application Adapters like MQ, MSMQ, SAP, Oracle, FTP, and etc and all these can be easily tested by BizTalk engineer, without BizTalk, this will need to develop by developers, I believe this is not a difficult task but also not a straight forward task for developer, and how to unit test all these protocol adpaters required sophisticated knowledge, is our developer have all these knowledge to work with different kind of network, data and application protocols?
• BizTalk Business Activity Monitoring (BAM) is a good integration analytical tool can be easily implemented by Business Analysts, Administrators and Developers, without BizTalk, can developers built this tool?
• Last but not least, from the business point of view, BizTalk has been sold to many multinational organizations and implemented successfully, will Microsoft stone themselves by killing BizTalk?

I foresee BizTalk will continue evolve by combining WF, Dublin and Oslo instead of deprecated, and this is clear in BizTalk roadmap:-
BizTalk Server and "Oslo" and .NET
"Oslo" is the codename for Microsoft’s forthcoming modeling platform. Modeling is used across a wide range of domains and allows more people to participate in application design and allows developers to write applications at a much higher level of abstraction. "Oslo" delivers a new integrated platform for connecting across modeling domains, including a new "Oslo" modeling tool, an "Oslo" modeling language, and an "Oslo" repository. As we gathered feedback from BizTalk customers, they indicated they would prefer to take a disciplined, evolutionary path to adopting some of these newer platform technologies. We have thousands of customers that have deployed mission-critical applications on top of our BizTalk Server architecture; they want to decide for themselves when to move to newer versions of the platform.
Therefore, it’s an important guiding principle to our planning efforts that we preserve our customers existing investments in their BizTalk Server infrastructure. In fact, you won’t need to upgrade BizTalk Server to take advantage of "Oslo" – current BizTalk Server 2006 R2 or BizTalk Server 2009 customers can benefit from "Oslo" by being able to leverage and compose existing services into new composite applications. BizTalk Server today provides the ability to service enable LOB systems or trading partners as web services (using WCF supported protocols), which can be composed with the "Oslo" modeling technologies.
This principle applies to advances in the .NET Framework as well, such as Windows Workflow Foundation (WF). In response to customer feedback, we are committed to continued support for BizTalk Server’s XLANG orchestration technology - the existing BizTalk orchestration engine. Additional support for WF will be prioritized for the coming releases based upon customer demand and scenarios.

Developing Business Process and Integration Solutions Using Microsoft BizTalk Server

BizTalk Server (BTS) is a great product from Microsoft and it solves a lot of integration challenges, I have shared this technology with patterns and best practices to many experience BizTalk engineers and developers at Kuala Lumpur.
Course 2933A:
Developing Business Process and Integration Solutions Using Microsoft BizTalk Server 2006
Module 1: Introduction to BizTalk Server 2006
Module 2: Creating Schemas
Module 3: Creating Maps
Module 4: Deploying a BizTalk Project
Module 5: Routing BizTalk Messages
Module 6: Creating Pipelines
Module 7: Integrating with Adapters
Module 8: Creating a BizTalk Orchestration
Module 9: Automating Business Processes
Module 10: Creating Transactional Business Processes
Module 11: Deploying and Managing BizTalk Applications
Module 12:Integrating with Web Services
Module 13: Integrating Business Rules
Module 14: Enabling Business Activity Monitoring
Module 15: Integrating Trading Partners
http://www.metricsthatmatter.com/iversona54
https://www.metricsthatmatter.com/infotrek12

Connect WSS internal database Microsoft##SSEE using SSMS

Default Windows SharePoint Services internal database can be connected successfully using this connection from SQL Server Management Studio (SSMS):-

\\.\pipe\mssql$microsoft##ssee\sql\query

1) Start SSMS and key in the connection

2) We can access the Microsoft##SSEE now!

ASP.NET populates Excel data without Office installed - DSOFile

Do you have any requirements to generate online Excel form from server without Office installed?

Why not download DSOFile from Microsoft website?

The Dsofile.dll files lets you edit Office document properties when you do not have Office installed

Sample Code in C#:-

DSOFile.OleDocumentPropertiesClass d = new DSOFile.OleDocumentPropertiesClass();
string file = Server.MapPath(@"Files\I9.xls");
d.Open(file, false, DSOFile.dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);

foreach (DSOFile.CustomProperty cp in d.CustomProperties)
{
if (cp.Name == "Name")
{
object objValue = "YB test on " + DateTime.Now;
cp.set_Value(ref objValue);
}
}

d.Save();
d.Close(true);

System.Runtime.InteropServices.Marshal.ReleaseComObject(d);

Response.Write(file + " [Name] field has been updated successfully");

Offshore Development with Agile Software Process

No requirements can get 100% in software development, the better Software Development Process is Agile compared to Waterfall, especially in Offshore Development Business.

This article from Martin Fowler is a good one:-

Using an Agile Software Process with Offshore Development

Documentation in Agile is much lesser compared to Waterfall, but the most important in Agile is user participation.