Patrick\’s Bytes

3, June 2007

Host Integration Server demoes on Channel9

Filed under: Database — patrickyong @ 12:00 pm

The dudes in Host Integration Server team showcase how a whole Microsoft development stack (SQL Server, ADO.NET, Visual Studio) can be used to program against IBM z/OS or AS/400 without additional installtion on both the host and the client side. You just need Host Integration Server 2006 in a box. It is indeed a far cry where by last time I had a website to connect to a AS/400 server using only OCBC calls but then we do not have transaction support.

The coolest thing is there is even a project wizard with VS2005 to code generate .NET objects based on definition on the IBM host. 🙂 So no need to go back to RPG or COBOL or ask the host guy which is very hard to deal with sometimes to get the data you want. 😉

Link to Ricardo Mendes on Host Integration Server


2, June 2007

Must have for BI developers in Feature Pack for SQL Server 2005 Feb 2007

Filed under: Database — patrickyong @ 4:51 am

Microsoft release a feature pack in Feb 2007 and there are a couple of downloads inside that is suitable for BI developers working on SQL Server Analysis Services and Reporting Services. 

Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.

Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes Table Analysis Tools for Excel, Data Mining Client for Excel, and Data Mining Templates for Visio.

Data Mining Managed Plug-in Algorithm API for SQL Server 2005
The Managed Plug-in API is a Microsoft .NET object model that enables software developers to create plug-in data mining algorithms for SQL Server 2005 by using CLI-compliant languages, such as Visual C# 2.0. The object model is available as source code and it needs to be built on the developer’s computer. The package includes a step-by-step tutorial, a compiled HTML help file (.chm), as well as a sample plug-in algorithm developed in C#.

Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.

Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.

22, May 2007

Using NOLOCK and READPAST table hints in SQL Server

Filed under: Database — patrickyong @ 9:09 am

To keep SQL Server on tip top performance, all care is taken into considerations when writing application. NOLOCK is one of the common SQL hint to prevent locking on data selection in the context of transaction. Too many lockings will results into record deadlocks.  Today come across READPAST hint which is similar to NOLOCK. Below is an extraction of the article found on Tech

The READPAST table hint example is very similar to the NOLOCK table hint example. I’ll begin a transaction and update one record in the SalesHistory table.

BEGIN TRANSACTION      UPDATE TOP(1) SalesHistory      SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the table.


My SalesHistory table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.

Link to Using NOLOCK and READPAST table hints in SQL Server

19, April 2007

Logging down dead locks in SQL Server 2000

Filed under: Database — patrickyong @ 2:56 am

CK Chin (eBworx) taught me this before but after spent 2 years in Oracle development I forgotten this trick. Thanks to soonyu in SPAN who remind me.

– add the -T1204 and the -T3605 startup parameters to SQL Server and restart sqlserver services


– from query analyzer
dbcc traceon(1204,3605,-1)
dbcc tracestatus(-1)

Create a free website or blog at