Watch out for that blank space

We have created a receive location with send port group as the subscriber and will send the message output using FILE adapter and FTP adapter.

So yesterday, I got an email from the engineering site, they said that they were not able to process the file from the FTP drop. 

Then they sent this output to me :

$ cat FILEA.txt cat: FILEA.txt: No such file or directory

 

But if they used this, the file is actually there :

$ more * :::::::::::::: FILEA.txt :::::::::::::: CONTENT FILE A

 

Another test :

$ more FILEA.txt* CONTENT FILE A

 

So from the test results above, you probably know what’s the cause right? :P

See that in the target file name properties, there is a blank space behind the “txt”.

After removed the blank space, life is good again :D

BizTalk Wrong Message Type / Schema Routing Problem?

This morning, we saw that numbers of orchestrations were suspended.
After some investigations, we were quite surprised that the messages were sent to wrong orchestrations after being polled by the receive locations.

Below is how the messages should be routed based on the message type / schema subscription.


But what just happened to us was :


The data was polled correctly with the Database Alpha but later the message was constructed with the wrong message type / schema, which caused it to be delivered to the wrong orchestration :(


I have experienced this in the past with my testing box, however it only happened 1 time only and I can't think of at least a way to reproduce the problem.

And BTW, the "Restart the host instance" magic solution saved the day as usual :P

Tell me my dear readers, what i should say to my boss if he asks me about this? ;)

BizTalk SOAP Adapter - Consuming Remote Web Services Bottleneck

This morning I was told by my colleague that he found hundreds of instances running in the BizTalk Server and from the look of it, most of them are the send port SOAP adapter instances.

We have an orchestration which consumes a web services, so the first thing that came to my mind is that it's gotta be the web services which can not handle the load.

I was thinking that there's gotta be hundreds of concurrent calls to the web services, then i tried to open up the Performance Counter -> ASP.NET Apps v1.1.4322 -> Requests Executing for the particular web services, i was surprised to see that there are only 2 requests executing concurrently :|

Then my other colleage told me that there's a settings in the machine.config file for this, to limit the numbers of call to particular connection.

<system.net>
<connectionManagement>
<add address="*" maxconnection="2">
<add address = "http://www.contoso.com" maxconnection = "5" />
<add address = "http://www.northwind.com" maxconnection = "2" />
<add address = "200.200.200.1" maxconnection="12">
</connectionManagement>
</system.net>

After changing this settings to a higher value, the instances were getting completed gradually and not building up anymore :)

You can find more about this at :
http://channel9.msdn.com/wiki/default.aspx/PerformanceWiki.HowToTuneASPNET
http://support.microsoft.com/kb/821268
http://msdn.microsoft.com/en-us/library/aa560822.aspx / BizTalk 2006 Server Documentation -> SOAP Adapter Configuration and Tuning Parameters
http://msdn.microsoft.com/en-us/library/1tkaca2y.aspx

However, in the BizTalk 2006 Server Documentation, it states that the default is 20 where .Net Framework Documentation states only 2.

Hope this helps anyone with the same issue ;)

Setup Local FTP Server for BizTalk 2006 FTP Adapter Development

Today, I was asked by my colleague to find out more about BizTalk 2006 FTP Adapter, because we may use that for future projects.

Setup my local PC with Windows XP Professional SP 2

1. Windows XP comes with built in FTP Site under the IIS Installation, you can install them through Add or Remove Programs -> Add/Remove Windows Component.
You can follow the steps from Mark Salloway's post and Mike D.'s article for FTP overview.

2. Open Computer Management -> System Tools -> Local Users & Groups -> Users

3. Create a new local user named FTPUser with the password

4. Create a folder named BizAppFTP and several subfolders :

• In -> Receive Location
• Out -> Send Port
• Log -> FTP Adapter Log Folder
• Sample -> Contain sample files for input

Right click on the BizAppFTP Folder -> Properties -> Security Tab -> Add FTPUser into the list and allow modify access.

5. Open Computer Management -> Services and Application -> Internet Information Services -> FTP Sites -> Default FTP Site

6. Create a virtual directory named BizApp and set the path to the previously created BizAppFTP folder. Check Read, Write, and Log visits options for the virtual directory.

7. Open BizTalk Server 2006 Administration Console

8. Create a one-way receive port named rp_TestFTP and a receive location rl_TestFTP and configure the adapter

• File Mask : *.xml
• Folder : BizApp/In
• Log : D:\Temp\BizAppFTP\Log\Receive.Log
• Password : (Fill this with FTPUser password)
• Server : (Your server name / IP Address)
• User Name : FTPUser

9. Create a send port named sp_TestFTP and configure the adapter

• Folder : BizApp/Out
• Log : D:\Temp\BizAppFTP\Log\Send.log
• Password : (Fill this with FTPUser password)
• Server : (Your server name / IP Address)
• User Name : FTPUser

10. Set the filter expression for the sp_TestFTP to subscribe the message from rp_TestFTP receive port.

Testing Time

1. Create a sample xml file in then sample folder with only inside.
2. Copy and paste the file into the "In" folder

3. Depends on the polling interval, it will pick up the file through rl_TestFTP and then send the file directly to the "Out" folder through sp_TestFTP.

4. The output file was generated and if you can see the Receive.Log and Send.Log for the FTP activities.
Similar to FILE adapter, the adapter will try to get the file and delete the file, this is why you will need to have a user who has privilege to delete the file and by using this built in FTP, the security can be configured through NTFS.

You may want to read more on the FTP Adapter in the BizTalk documentation, especially on the security part where it mentions "FTP is, by nature, not secure: The user name, password, and other credentials traverse the network in clear text".

I'll post more later when I have the project at hand.
Hope this helps :)

BizTalk 2006 Oracle Adapter (ODBC) - An alternative for Poll Statement with Function (Not Procedure)

In Oracle Adapter, there are several ways to poll the data from the database, in this post, I will be discussing on the polling data without triggers, just by using Poll SQL Statement and Post Poll SQL Statement.

Part 1 - The First Project
We have a table of data which will be polled by several orchestrations based on a status field in the table to process, see below screen for the details.

By using Poll SQL Statement to get the rows and Post SQL Statement to update the rows to other status is actually working fine for most of the cases, and it is important to note that they are both executed within a transaction of serializable isolation level.

In my first project, it is working fine, because the frequency is quite low, poll 1 row of data / 60 seconds. Life is good ;)

Part 2 - The Second Project
Ok, now in which kind of scenarios where it is not working as expected?

We have a new application which needs to poll rows of data in short interval, because the expected data to process will be huge, so it is not acceptable for us to poll 1 row / 60 seconds.
In short, we need to process 3.000 rows of data in 1 hour, so that leaves us with 50 rows / 60 seconds.

So what's the issue with that? just change the query to select top 50 and off you go? hhhmm... not as smooth as we thought :P

Reasons : As the processing got huge, it seems that the server and/or the adapter are affected as well. Because of the latency, it seems that the Post Poll SQL Statement is not executed in the expected time, this cause the subsequent polls may poll the same data as the previous one, because the Update the row status in the Post Poll SQL Statement hasn't been executed yet. This eventually lead to double processing of the data.

Part 3 - The Search for Alternatives
What is the ideal way for this? I would say that call a procedure to poll the data, because we can get the rows and update them in the same time. And btw, I don't really like the way Poll SQL Statement and Post SQL Statement work, we need to make sure that the conditions / where statements in the Poll SQL Statement and Post Poll SQL Statement are the same or we'll get nasty update results :P

Unfortunately, I have been trying for calling a procedure without any good results for quite a while now, and i have posted some questions about this in the web and they have been replicated to many places now :P links

Well, several people have mentioned that they are able to use oracle procedure to poll the data, however I haven't received more details about it until now.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2527819&SiteID=17
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.biztalk.general&tid=97564c53-15ee-4a8e-997c-72f8c2520241

The main problem with oracle procedure, it will need an output parameter to return a resultset, and I was not able to think a way to work with this parameter in the Poll SQL Statement.

So several days ago, i stumbled on a post (I forgot where from) but someone from the post mentioned about using function in the select statement like this below :

Select packagename.functionname from dual;

This gave me an idea, because it will just like a normal select query which returns row(s).

So the next thing to do is to have the DML statement to update the selected rows, I found a way to allow DML Statement in the function, is to use PRAGMA AUTONOMOUS_TRANSACTION which will summon its own transaction so it will not affect the caller transaction.

Part 4 - The Solutions

Solution 1 - Get one row
1. Create an oracle function to select and update the row then return the unique key

CREATE OR REPLACE FUNCTION pollonejob RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
JOBID NUMBER;
BEGIN
SELECT JOB_ID
INTO JOBID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM = 1 FOR UPDATE;

-- This is to test the lock only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID = JOBID;

COMMIT;

RETURN JOBID;
END;
/

2. Setup the Poll SQL Statement :
SELECT pollonejob AS JOBID FROM dual

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status FROM jobs WHERE job_id = pollonejob

Solution 2 - Get more rows
1. Create an oracle type as the container for the unique keys
CREATE OR REPLACE TYPE number_key_t is TABLE OF NUMBER

2. Create an oracle function returning the previously created type with a parameter to speficy how many rows to retrieve
CREATE OR REPLACE FUNCTION pollmultiplejob(noOfRows NUMBER) RETURN NUMBER_KEY_T IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_data NUMBER_KEY_T := NUMBER_KEY_T();
BEGIN
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM <= noOfRows FOR UPDATE;

-- This is to test the locking only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID IN (SELECT column_value FROM TABLE(CAST(l_data AS NUMBER_KEY_T)));

COMMIT;

RETURN l_data;
END;
/

3. Setup the Poll SQL Statement :
SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T))

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status
FROM jobs WHERE job_id IN (SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T)))

Notes :
1. You do not need the Post Poll SQL Statement anymore, since when the Poll SQL Statement is executed, the rows will be updated in the same time it polls
2. Avoid setting the poll interval less than the time to execute the polling query, for example if the polling takes 30 seconds to complete, it's better to set the poll interval 40 seconds or more, consider the future data growth
3. As the function will update the data when being called in the select query, please do handle that more carefully by giving more meaningful function name, so everyone will know that the function will not only retrieve the data but also update the rows in the same time.

I have tested this with one receive location for several days now and it seems that they are working fine as expected :)

I'm just glad that I have this workaround or you might say that this is a bit hack :P but it did solve my problem and work for Oracle Adapter with ODBC, and i hope it will be useful for you guys as well.

**Update** I have tried creating 5 receive locations calling the same function and there were no duplicate poll :D
However, there is a flaw, the FOR UPDATE statement may not work as expected when trying to poll the data sequentially where in oracle you will need to have a sub query like below :
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_ID IN (
SELECT JOB_ID
FROM (
SELECT JOB_ID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
ORDER BY JOB_ID
) A
WHERE ROWNUM <= 1
)
FOR UPDATE;
I experienced duplicate polls with this, so make sure you test your query first for any duplicate issue.

Feel free to drop any comments or questions ;)

Microsoft.ServiceModel.Channels.Common.ConnectionException: ORA-1017: invalid username/password; logon denied

This is the first problem I had with the new oracle adapter pack.
From the error message, it is quite obvious right. But I was quite sure that I have provided the right user name and password.

I found the answer here, the user name / password may be case sensitive. After providing the user name and password with the appropriate case sensitivity, it is working now. hopla ;)

Installation and setup files for BizTalk 2006 R2 with Oracle Adapter Pack 3 (Evaluation Edition)

I'm currently trying BizTalk 2006 R2 with the new BizTalk Oracle Adapter Pack 3.0 (ODP.Net) to resolve the locking issue we had with the previous Oracle Adapter version with ODBC.

You can grab the installation files from here :

1. Oracle Data Access Component (ODAC)
   
2. BizTalk Server 2006 R2 Evaluation Edition (Note: You will not be able to perform upgrade with the evaluation edition, you will need to uninstall the current BizTalk development edition first and then install the evaluation edition)
3. WCF LOB Adapter SDK
4. BizTalk Oracle Adapter Pack 3.0 (Oracle adapter included inside)
5. BizTalk Adapter Pack: Oracle Database Adapter Samples
   

FOR UPDATE statement may not work with BizTalk Server 2006 Oracle Adapter ODBC

I created a simple test console application today for testing FOR UPDATE statement using different types of component.

1. Using ODP.Net - Working
Note : The second execution will wait until the first execution to finish first.

2. .Net Oracle Client - Working
Note : A slight different between odp.net and .net oracle client is that the second will wait until the first program ends, so eventhough the first program has finished the execution, it was still waiting until i close the first program, i suspect there is a different transaction handling between them.

3. ODBC - Not working
Note : Quite a surprising result, but as we expected. They seems to ignore the FOR UPDATE statement.

Then I found these information at MSDN and oracle sites for ODBC :
http://msdn2.microsoft.com/en-us/library/ms711792(VS.85).aspx
http://msdn2.microsoft.com/en-us/library/ms713566(VS.85).aspx
http://forums.oracle.com/forums/thread.jspa?threadID=607056&tstart=60
http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/ODBCFAQ.pdf

It says that before the statement is passed to the driver, the FOR UPDATE clause will be removed first and this seems to apply not only for Oracle ODBC, but as a standard ODBC Programming reference.

My conclusion : As long as we're using Oracle Adapter with ODBC, we'll stuck with this unless we use the new Oracle WCF LOB Adapter with ODP.Net which requires BizTalk Server 2006 R2 and .Net Framework 3.0.

Update : Below is the query result which displays the executed queries in the oracle, see that the query for TestOracleLock.exe (2nd row) didn't have the FOR UPDATE statement which I specified in the code earlier.

You can use this query below to get the result above :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
Order By last_load_time DESC;

BizTalk Oracle Database Adapter First Load Performance

I found this performance problem with BizTalk Oracle DB Adapter a month ago when developing some orchestrations using this adapter.

When re-deploying my solution and restarting the host instances, i need to wait about 15 - 20 minutes, you can imagine how difficult to wait that long, especially after only changing one or two lines of code then re-deploying the codes for testing.

After doing some investigations and tracing the process, i found that actually BizTalk itself was loading very quickly, but it stopped at the send port to the Oracle DB. My orchestration receives messages from web services and inserts information to the oracle database.
Then i traced the database process and finally i found these results below :


SELECT /*+ RULE */ '', a.owner, decode (b.object_type, 'PACKAGE', CONCAT( CONCAT (b.object_name, '.'), a.object_name), b.object_name),decode(a.position, 0, 'RETURN_VALUE', a.argument_name), decode(a.position, 0, 5, decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)), 0, a.data_type, a.data_precision, a.data_length, a.data_scale, a.radix, 2, '' , '', 0, 0, '', a.position, '' FROM ALL_ARGUMENTS a, ALL_OBJECTS b WHERE ( b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION' ) AND b.object_id = a.object_id AND a.data_level = 0 AND a.OBJECT_NAME LIKE '[Procedure / Function name]' ESCAPE '\' AND b.OWNER = '[User Account]' ORDER BY 2,3, a.overload, 18

When loading the send port, BizTalk will try to load all the oracle procedures & functions schema information which are accessible by the specified database user account in the send port. The user account which i was using has access to all 2000+ procedures the current database where i'm actually only using less than 20 of them :(

Moral of the story :
Use or create a specific application user account which only has access to the required oracle procedures in the send port.

Below is the sql query and please note that you will need to have access to the v$sql to run this :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
AND Upper(MODULE) = 'RUNTIMEAGENT.EXE'
Order By last_load_time DESC;

BizTalk Oracle Adapter / ADO.Net Limitation (ORA-01460: unimplemented or unreasonable conversion requested)

System defined exception
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

Source: OracleDb
Error Code: 1460 (0x5b4)
Cause: OracleDb://exception=DBException (Unique ID )
42000 : [Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested


Exception data:
struct DBException =
WideString StatusCode = "42000"
WideString Description = "[Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested"

I'm using BizTalk 2006 with BizTalk Oracle Adapter to Oracle9i Database.

What i was trying to do here is to call an oracle procedure to insert a new row in a table and one of the field is CLOB/XMLType. This field is actually storing the current message which was passed in to the orchestration and to be used in future processing (other orchestration).

For simple / small size message, it was working fine as expected. Fortunately, I did try the scenario for huge incoming message (always test your code with the highest value of the assumptions (e.g. The user will be able to submit 1000 items in 1 transaction). Then I got this exception from BizTalk.

I tried to search through the net about this, it seems there is a limitation to pass in a parameter more than 32 KB size. (http://www.google.com/search?hl=en&q=32+kb+xmltype+parameter+procedure+limit&btnG=Search&meta=)

Some Tests which I did :
1. Use NativeSQL insert into table. - Not Working
XMLType column will be generated as base64binary type in the schema.
I have tried to pass the xml raw string but failed. Same if I tried to convert the xml into base64binary.
The error message which was returned :

System defined exception
Call to Insert@OracleDb://ORADEV/DBO/Tables/BIZTALKTEST
Implementation = OracleDb://ORADEV/DBO/Tables/BIZTALKTEST

Source: OracleDb
Error Code: 932 (0x3a4)
Cause: OracleDb://exception=DBException (Unique ID )
HY000 : [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected NUMBER got BINARY


Exception data:
struct DBException =
WideString StatusCode = "HY000"
WideString Description = "[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
"

Or (I tried some combinations for the data type : CLOB/XMLTYPE)

Error transmitting message: SOAP-ENV:ServerRequest ID: Unknown
Exception Type: A cross object system conversion error occurred
Exception Info:
Exception occurred:
E-CORE0053: Dimensions or bounds of source and target params do not match
Call to Insert@OracleDb://ORADEV/DBO/Tables/BIZTALKTEST
Implementation = OracleDb://ORADEV/DBO/Tables/BIZTALKTEST

SOAP://xmlns/schemas.microsoft.com/BIZTALKTEST/InsertRecord:Type/[]/TESTRAW ==> OracleDb://ORADEV/DBO/Tables/BIZTALKTEST/InsertRecord/VARCHAR2/WideString
(SOAP://xmlns/schemas.microsoft.com/BIZTALKTEST/Insert:Element/Rows ==> sequence<struct OracleDb://ORADEV/DBO/Tables/BIZTALKTEST/InsertRecord&>)
(Error occurred before call to foreign method)


2. Call oracle Procedure passing XMLTYPE parameter - Not Working
I tried to use XMLType as a parameter but there was an error similar to the 2nd error above.

Error transmitting message: SOAP-ENV:ServerRequest ID: Unknown
Exception Type: A cross object system conversion error occurred
Exception Info:
Exception occurred:
E-CORE0053: Dimensions or bounds of source and target params do not match
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

SOAP://xmlns/schemas.microsoft.com/TopLevel/RP_BIZTALKTEST:Element/V_TESTRAW ==> OracleDb://ORADEV/UNDEFINED_2000/WideString
(SOAP://xmlns/schemas.microsoft.com/TopLevel/RP_BIZTALKTEST:Element/V_TESTRAW ==> OracleDb://ORADEV/UNDEFINED_2000)
(Error occurred before call to foreign method)

3. Call oracle Procedure passing CLOB parameter and then convert into XMLTYPE when insert - Not Working
This is the one that actually has 32KB limitation. I actually created a small .net application using oracle client to test this and turned out that I got the same error when i tried to pass > 32 KB string into the parameter. There is a work around in this article, but i find it more complex than the 4th solution below (http://msdn2.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx)

System defined exception
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

Source: OracleDb
Error Code: 1460 (0x5b4)
Cause: OracleDb://exception=DBException (Unique ID )
42000 : [Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested


Exception data:
struct DBException =
WideString StatusCode = "42000"
WideString Description = "[Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested"

4. Use Oracle Data Provider for .NET 9.2.0.7.00 (ODP.Net) - Working
I found that this is actually the solution and the easiest way to do, passing a XMLTYPE is just as simple as passing other parameters, It has its OracleDb Type.XmlType. So far I have tested with huge messages more than 32 KB and they are working fine. Please also note on the ODP.Net version.

OracleParameter pContent = new OracleParameter("content", OracleDbType.XmlType, content.Length);

Before I decided to go with the ODP.Net, I have tried using :
1. ODBC Connection (System.Data.Odbc) : Limited with 4 KB size
2. .Net Oracle Data Provider (System.Data.OracleClient) : Limited with 32 KB size as well.