BizTalk Adapter Pack 2.0 – OracleDBBinding adapter

I have been trying to connecting to Oracle Database Table to fetch the data using BizTalk Adapter Pack 2.0 and converted in form of XML.

This is an very basic and simple task, but I did not find any useful article to explain step by step process of extracting schema and Configuration part in BizTalk side while consume Oracle Table/View/Stored Proc. Apparently BizTalk receive location configuration.

After three days of trail I could achieve this simple task. Hope this can give you a clear picture of how to extract a Table data using simple SQL Statement in BizTalk.

Make sure you install BizTalk Adapter Pack 2.0 and Oracle Client before you start this example.
To install BizTalk Adapter Pack 2.0 :
http://www.microsoft.com/biztalk/en/us/adapter-pack.aspx
Basic configuration settings for Oracle Database :
http://rajwebjunky.blogspot.com/2010/07/biztalk-adapter-pack-working-with.html
Apparently configure the Oracle Host & Host Instance at BizTalk Admin Console.

Task: I am going to perform a retrieve data from LoanMaster and result to be converted into XML file and drop into my Local Folder, program flow described in below flow chart.

Step1: First you need to generate Table/View/Stored Proc schema in BizTalk application, This is an standard procedure which we follow for SQL Server or any other database.

However for Oracle database it has additional configuration settings to be done and you should generate a schema for POLLINGSTMT operation. Otherwise you will end up with many errors.

Right click your project and select “Add Generated Items” and select “Consume Adapter Service” from Left pane and select “Consume Adapter Service” from Right pane and Click OK
Key things to follow while Generating the Schema for POLLINGSTMT
1. Polling ID = 23 (can be any number but same number should mention in
ReceiveLocation configuration settings)
2. PollingStatement = Select * from LoanMaster
3. Select the contract type as Service (Inbound operation)
4. Click Root (/) and select POLLINGSTMT operation in right list boxall the above steps shown in below screenshots

Step2:Finally you can find a Schema to be generated in you Project and that will have a POLLINGSTMT Node along with you will also find POLLINGSTMTRECORD, ArrayOfPOLLINGSTMTRECORD nodes. I am not going to discuss about other two nodes in this article.

while Creating a corresponding message at Orchestration view (Message_1) assign the POLLINGSTMT node as your Incoming Message.

Step3: Orchestration looks like below

Step4: Receive Location1
At BizTalk Receive Location side you need to perform same configuration settings which you have mentioned in Step-1

Polling ID =23 ( what ever number you mentioned in Step1- same Number should use here)PollingStatement = Select * from LoanMasterNow Build and test your application. Hope you might get some warning messages, ignore it and check your Local Folder for Output.

Reach me if you need any clarifications @ raj.webjunky@yahoo.com

BizTalk Adapter Pack - Working with Oracled Database Adapter

I am trying to connect to ORACLE database directly using BizTalk Adapter Pack and when I try click Connect at Add generated schema It was throwing the below error
Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

You can fix the above issue by installing the Oracle client ( correct version) and bit of Configuration at your Box.

Step:1 Install the BizTalk Adapter from below location http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html
and download the ODAC1110720.zip 188 MB Zip file

Note: Different version of Oracle Client will remains to throw same error at BizTalk side

Step2: Once you installed the Oracle Client then find out the TNSNAMES.ORA file from below location

\app\product\11.1.0\client_1\Network\Admin
if you don not find them no worries.. you can copy the file from \Samples Folder and modify the File with your Server IP/Name and Port number and Database Name.

Example :
OracleTest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Dev_OracleServer)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Dev_OracleTestDB) ) )
NOw you can try to do a OracleBinding adapter, hope you will win...

Reach me if you still find an issue raj.webjunky@yahoo.com

BizTalk 2009 Configuration Error- Failed to connect to the SQL database 'SSODB' on SQL Server

I am trying to install BizTalk 2009 [Visual Studio 2010] on Windows XP SP3 Operating System today and I end up with error Failed to connect to the SQL database 'SSODB' on SQL Server while configuring BizTalk 2009.

I fixed this issue by registering the SSOSQL.dll file in my system
Steps:
Open your VisualStudio Command Prompt and type
regasm "C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll"


Reach me @ raj.webjunky@yahoo.com

Configure BizTalk Server in Multi-server Environment

Configure BizTalk Server in Multi-server Environment
I got many request from online friends who visiting my blog for install and Configuring BizTalk in Multi server Environment.

Hope this can help others to understand the process of installation of BizTalk in Multi Server Environment.

Make sure your OS is updated with latest Service pack and SQL Server Database also updated with Latest Service Pack. Also make sure you have network connectivity to connect any other network system.

Now we will understand the Installation of Biztalk in step by step process.
First you need to Install your Biztalk Server in your PC. After installation process completed it will prompt you to configure the Biztalk. Here you normally select Basic Install option will allow you to configure BizTalk configuration without any hassle.
As part of Multi server configuration you need to select the Custom configuration option.

Step-1

Step-2
In the next screen you need to select and Configure the SSO ( Single Sing On).
Under Windows service type Domain\
for the Enterprise Single Sign-On Service service account type Domain\SSO Administrators
for the SSO Administrator(s) Windows group type Domain\SSO Affiliate Administrators
for the SSO Affiliate Administrator(s) Windows group.

Apparently click Enterprise SSO Secret Backup.
In the Secret backup password field, type a password and password reminder (see username s/s). In the Backup file location field, enter a file location. Make sure this information in a secure location.

Step-3
Next screen you enable to Group Option and mention the details as shown in below

Step-4
In the BizTalk Server Configuration Manager , select BizTalk Runtime, and then select
Register the BizTalk Server runtime components.

Select Create In-Process Host and Instance, Select “Trusted”
Select Create Isolated Host and Instance, Select “Trusted”
In the BizTalk Host Instance Account field type Domain\
In the BizTalk Isolated Host Instance Account field type Domain\
In the Windows Group for the BizTalk Host User Group field, type Domain\BizTalk Application Users.
In the Windows Group for the BizTalk Isolated Host User Group field type Domain\BizTalk Isolated Host Users.


It will process to install the necessary database and configuration.

After completion of the Installation process you can see the Enterprise Single Sign-On Service installed on System. If you have chosen to deploy BizTalk host instances during configuration then you will see “BizTalk Service BizTalk Group : BizTalkServerApplication” service on the system.


Now we need to observe the couple of tables in SQL Server which is configured for BizTalk.
These databases and Tables has created as part of above Configuration.
Open a BizTalkMgmtDb Database and open adm_Group Table, If you observe the table rows you can find only one row of data.

At this stage the Table “adm_Server” has only One Node (1 BizTalk Server) configured.

Message Box that is used by PAS model in BizTalk has a table called adm_MessageBox that has information of Group, Database Server name and MessageBox database name etc.

Configuring Secondary BizTalk Server
To open a Configuration window

Go to Start/Programs/Microsoft BizTalk Server 2006/ BizTalk Server Configuration.

In the Main Configuration screen, select “custom Configuration’, then enter the BizTalk SQL Server Name (use SQL Cluster Name if appropriate) and enter in account details of the BizTalk Host Instance Account (Domain\). Then Click ‘Configure’

Follow the Step1- mentioned in above.
On Step-2 you need to select the option “Join an existing SSO system” and mention the server details.

Rest all the steps are same. If you check the tables once again you can find an two entries in a table.

Finally you can see the Configured server detals in BizTalk admin Consoloe

Drop a comment if this article helped you to solve your problem
If you need any more clarifications you can reach me @ raj.webjunky@yahoo.com

Standalone version Installation of MOSS 2007 – Object Model Execute - Access denied issue

Standalone version Installation of MOSS 2007 – Object Model Execute - Access denied issue
Access is Denied (Exception from HRESULT:0x80070005(E_ACCESSDENIED))
One of my friend has installed the MOSS 2007 on Virtual PC. And configuration is
Windows 2003 Server with SP2
MOSS 2007 – Single Server

He has written a simple program which performs an action of apply a Theme for my Team Site. The applied theme should affect for all sub-sites and pages too.
Below is my Simple code

Whenever he executes the above query he got the Access denied error. He asked me to resolve this issue.
Initially I thought it could be some permissions issue with MOSS users. But later I found that the need a workaround with AD and DNS configuration.
Here I will explain you what are the necessary steps I did to resolve the issue.
First cross check the IPconfig at Command prompt looks no IP has obtained and DNS not configured properly.

Go to ControlPanel ->NetworkConnections -> Local Area Connections à properties
This looks no configuration done for DNS and its taking dynamic Ip address.
Now lets change into static IP-address as mentioned in below screenshot

Now go to ControlPanel-> Administrative Tools -->DNS

Delete the existing Forward Lookup Zones ( delte wss.com and delete from root level)

Now right click My server -> new Zone and follow the steps mentioned below.

Now finally Goto C:\Windows\System32\Drivers\etc and open the hosts file in notepad
And add your Server under localhost

Restart the DNS
Restart IIS
Restart the OS
After doing all these activity issue has been resolved.
You can reach me @ raj.webjunky@yahoo.com

3 Easy Steps to Create Custome Theme- MOSS 2007

How to Create and Reuse of Custom Themes in Sharepoint?
SharePoint site theme basically consists of theme.inf, theme.css, and image files.
Theme.inf file simply represents the title of the theme. Theme.css is a stylesheet file that defines colors, header images and layouts of a site and image files can be referenced here to display on the page
This can be achieved in 3 steps
Step-1.Copy any theme folder in " C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES " and paste in same location and repalce with your own name. for example "Raj.webJunkyTheme" and do your changes.

Step-2. Othe SPTHEMS.XML from below location and add your Theme Template
C:\Program Files\Common Files\Microsoft Shared\web server extensions\
12\TEMPLATE\LAYOUTS\1033

Step-3. For preview of your Theme you need to take a screen short of your theme and save it as Gif file at below location
C:\Program Files\Common Files\Microsoft Shared\web server extensions
\12\TEMPLATE\IMAGES

For any clarifications You can reach me @ Raj.webjunky@yahoo.com

Biztalk HostInstance ThreshHold -Error nID int datatype

Can any one guess what would be maximum number of the messages can pass through the BizTalk Receive / Send Host?

I asked same question to many of my friends and every one given different answer like
Can pass “N” number of message
No Limit
Could be 10 lacks of instances

Which one is correct from your point of view? I know you are going to give me a new number. Any way let me try to answer this question also will explain you why should we consider this number? Is this really a big impact for your application?

Well, I am working for Retail application and we normally receive a 200 – 1000 messages per sec. Also we need to send each message to 10 different locations. Hence the count on average is 500*10 = 5000 message instances will send through “SendHost”.

It has been working quite well and I never faced any issue with the BizTalk SendHot. On one Friday suddenly the SendHost got Stopped and I tried a lot to start the Host but no luck. It was throwing the below error.


Job Name : MessageBox_Message_Cleanup_BizTalkMsgBoxDb
Error Description :
Arithmetic overflow error converting expression to data type int.
[SQLSTATE 22003] (Error 8115) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

This issue is due to threshold of Sendhost has crossed the Int value.
If I quickly check at the threshold of the SendHost I could find that it already reached Int value.

If you look at the Store procedures which has supported for the above job, It cleared said the data type used for nID as INT in stored procedure. But actually in table it declared as BigInt.

StoredProc Name: int_PurgeMessageZeroSumTable

CREATE PROCEDURE [dbo].[int_PurgeMessageZeroSumTable]
AS
declare @count int, @tmp int, @fUseTempTable bit,
@fContinue int,
@tnActiveTable tinyint,
@retVal int
create table #PurgeJobMsgIDs (nID int NOT NULL, uidMessageID uniqueidentifier NOT NULL)
create clustered index [CIX_PurgeJobMsgIDs] on [#PurgeJobMsgIDs] ([uidMessageID])
set @fContinue = 99
while (@fContinue >= 99)

This I have changed to BigINT as suggested by Microsoft.