Community Launch for Heroes

I have presented LINQ to SQL Introduction on 28 June 2008 (Saturday) at Microsoft Auditorium, details at http://www.microsoft.com/malaysia/events/communityheroes/

BizTalk WCF Oracle Dynamic Send Port

How to implement WCF Oracle Dynamic Send Port? There is an example in Microsoft BizTalk Adapter 3.0 for Oracle Database Documentation:-

Request2(BTS.OutboundTransportType)="WCF-Custom";
Request2(WCF.Action)="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/ACCOUNTACTIVITY/Select";
Request2(WCF.BindingType) = "oracleDBBinding";
Request2(WCF.UserName)="SCOTT";
Request2(WCF.Password)="TIGER";
SendPort(Microsoft.XLANGs.BaseTypes.Address)="oracledb://adapdoc/";

If you deployed the code may hit the error:-
Event Type: Error
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5754
Date: 7/3/2008
Time: 11:21:31 AM
User: N/A
Computer: AMSDC1-S-7538
Description:A message sent to adapter "Oracle" on send port "OracleDynamicSendPort_1.0.0.0_OracleDynamicSendPort.OracleSend_Port_DynamicOracle_beec5c3523602cdb" with URI "oracledb://TNSNAME/?PollingId=1" is suspended. Error details: Value cannot be null.Parameter name: s MessageId: {A7F0A852-C315-4521-9B63-E0BFE221F4AE} InstanceID: {FF53A23E-3F21-4098-A02B-8381E6C3D851}

The solution is to add additional line in the code above:-

SendPort(Microsoft.XLANGs.BaseTypes.TransportType) = "WCF-Custom";

Then this exception will be resolved.

NOTE: to use the WCF Oracle Dynamic Send Port, the Adapter's default send handler, i.e. the BizTalk hosts must be running.

New Web Site created

Just created a new website http://www.kuala-lumpur-guide.com to share what is Kuala Lumpur included dark side information

WebSphere MQ Samples

Sample code for accessing Webphere MQ in Microsoft C#, VB6 and VB.NET


http://www.capitalware.biz/mq_code_csharp.html

Project Server

I implemented a windows sharepoint services + SQL Server 2000 Reporting Services project in Australia last year, I used Smart Part http://www.microsoft.com/belux/nl/msdn/community/columns/u2u/smartpart.mspx developed for Sharepoint Web Parts, the result is good, when combining with SQL Reporting Services can become a impressive BI portal.

Anyway the new SQL Server 2005 has been released last year after end of my Project, everybody have to pick up the new skill asap to keep the competency :)

SQL Black Belt Advance Articles

Recently just involved in SQL Server 2000 Reporting Services Business Intelligence Development, found out a must read articles for any one who would like to master in SQL Server, the web site is at http://databasejournal.com/article.php/1459531 by William E. Pearson, III

MSDAORA Oracle upgrade connectivity Problem

Oracle client always have connectivity problem to the Oracle database server due to the improper configuration in C:\Oracle\Ora81\network\ADMIN\TNSNAMES.ORA (Assume you did not change the default install folder)

Sometimes you may experience all the configuration are fine, you also can use TNSPING and SQLPLUS to access the ORACLE server without problem, but your Application using MSDAORA provider is facing problem to access the Oracle Server, you may get the error like:-

Error while trying to retrieve text for error ORA-03121

This is usually happen after you upgraded your Oracle client, you have restarted your machine, all the TNSNAMES.ORA entries are correct, everything is fine except your application, either ASP, ASP.NET, VB.NET or any application that use MSDAORA provider.

Why the problem happen is, after you upgraded your Oracle client, your MSDAORA still pointing to the old Oracle version, that's why the problem occurs.

To resolve this problem, goto your machine MSDAORA home C:\Program Files\Common Files\System\Ole DB folder, you will see there are some .reg files:-

mtxoci81_winnt.reg

mtxoci81_win2k.reg

mtxoci7x_winnt.reg

mtxoci7x_win2k.reg

If your Oracle client is 8.1 with Windows 2000 and above, double click the mtxoci81_win2k.reg file to register the new registry for the newer client. Then restart your machine, the problem will be solved automatically.

The content of the mtxoci81_win2k.reg is

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]

"OracleXaLib"="oraclient8.dll"

"OracleSqlLib"="orasql8.dll"

"OracleOciLib"="oci.dll"

The nice thing about Oracle is, once the Oracle problem is resolved, you will enjoy with Oracle.

ADO filter with NULL value

NULL value in database means no storage in the cell, if you use ADO to query the data and you would like to filter the NULL value, it will be a challenge for a lot of programmers.

Let's see the following code:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ReportsTo FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
do while not rs.eof
rs.movenext
loop
rs.close

The code above is retreiving data from MS SQL Server Northwind database, it tries to get the employeeId and superior employee Id (reportsTo column).

Let's say the requirement is those employee without superior display in one panel and those employee with superior display in another panel.

I believe no one want to query the database two times by using SQL query "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NULL" and "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NOT NULL", so we will query one time from the database by re-using the above code.

You will add another code before the while loop:-
rs.filter = "ReportsTo IS NULL"

Immediately you will get this error:-
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

The reason being is because ADO Filter does not support "IS" and "NULL" statement, even you tried to use the filter as ISNULL(ReportsTo, 'N/A') = 'N/A' also get the same problem.

The only solution to this is back to the SQL query, change the query as
SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees

Then the rs.filter use ReportsTo = 'N/A' will solve the problem, the whole code is shown below:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
rs.Filter = "ReportsTo = 'N/A'"
do while not rs.eof
' -- Display Data in Superior Panel
rs.movenext
loop
rs.Filter = "ReportsTo <> 'N/A'"
do while not rs.eof
' -- Display Data in Subordinate Panel
rs.movenext
loop
rs.close



Cheers!