Use of SQL temp table to show 24hours as data set

Query:
declare @t as int
select @t = 0
declare @developers table(t varchar(12))
while (@t < 24)
begin
if (@t < 10)
begin
insert into @developers (t) values ('0' + CONVERT(varchar, @t) + ':00:00:000')
end
if (@t >= 10)
begin
insert into @developers (t) values (CONVERT(varchar, @t) + ':00:00:000')
end
select @t = @t +1;
end
select @t = @t -1;
insert into @developers (t) values (CONVERT(varchar, @t) + ':59:59:999')
select t as 'TimeValue' from @developers

Output:

TimeValue
------------
00:00:00:000
01:00:00:000
02:00:00:000
03:00:00:000
04:00:00:000
05:00:00:000
06:00:00:000
07:00:00:000
08:00:00:000
09:00:00:000
10:00:00:000
11:00:00:000
12:00:00:000
13:00:00:000
14:00:00:000
15:00:00:000
16:00:00:000
17:00:00:000
18:00:00:000
19:00:00:000
20:00:00:000
21:00:00:000
22:00:00:000
23:00:00:000
23:59:59:999
(25 row(s) affected)

How to Change BizTalk Service Accounts and Passwords

Here are the useful links for changing your BizTalk service account password.

http://msdn.microsoft.com/en-us/library/aa561505.aspx
http://msdn.microsoft.com/en-us/library/aa559192.aspx
http://msdn.microsoft.com/en-us/library/aa560589.aspx

OWC11 Causing SQL Installation Failure

If you have not properly installed Office Web Components - OWC11, it will cause catastrophic failure in your SQL Server 2005 installation. You can download and install OWC11 from
http://www.microsoft.com/downloads/thankyou.aspx?familyId=7287252c-402e-4f72-97a5-e0fd290d4b76&displayLang=en Then only you start your SQL installation.

See the other disscussions at
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282227

Performance tuning with BizTalk 2006

Here is a useful link for BizTalk Performance Tuning.

http://zeetalks.wordpress.com/2008/06/24/biztalk-from-a-hardware-and-software-performance-perspective/

SSODB Configuration Store for BizTalk Orchestration

Came accross this very usefull posting. I am adding this link for my referecne.
Thanks a lot Bembeng Arifin!!!

http://bembengarifin-tech.blogspot.com/2008/01/ssodb-configuration-store-for-biztalk.html

There is one more posting from Richard Seroter (MVP) which is also useful:

http://seroter.wordpress.com/2007/09/21/biztalk-sso-configuration-data-storage-tool/

This tool can be dowloaded and code can obtained. Dont know why it not compiling, but you can get the class SSOHelper.cs class which can be compied to your helper class. Remember this class references "C:\Program Files\Common Files\Enterprise Single Sign-On\ Microsoft.BizTalk.Interop.SSOClient.dll"
Once you compile your class, you can used the SSOHelper.Read(..) method to read your SSO DB keys.

HTTP with SSL certificates using BizTalk - Complete Scenario

HTTP posting from a BT HTTP send port may fail due to 3 reasons:

1. Wrong or incomplete configuration at HTTP port
2. Wrong certificate location, or
3. Wrong certificate installation.

PART#1
Configuration steps at BizTalk server HTTP Send port: (outgoing SSL Encryption)

Note: If the Destination Web Server (the Trading partner server) wants to enforce SSL certificate on your HTTP posting, then only you need to install Certificates in your box and configure the BizTalk HTTP send port with the Thumbprint of the certificate. This means, you are Encrypting the outgoing message with SSL using a client side certificate.

Open BizTalk Admin Console and open the HTTP Send port. Or open the same from BizTalk Server Explorer. And do the following configurations:

Step1:
Transport Type : HTTP and Click the Configure Button.

Step2:

In the General Tab of HTTP Transport Properties dialog box:Type the URL you are going to post. (Other information like, Request Timeout etc. are as per your requirements).

Step3:

In the Proxy Tab, put the Proxy server IP or name (if you are using a proxy server) and password for this. This setting will override the handler proxy settings. If you handler is already configured with a proxy server, then select “Use Handler’s default proxy config” option.

Step4:

In the Authentication Tab: Select the correct authentication type along with Credentials if your trading partner is enforcing login user ID and password. Leave it blank if your trading partner is not enforcing any user id and password.

For internal sites, If you have SSO to handle user access, select “Use SSO option”. And moreover, NTLM authentication is not going to work for Internet transmissions with your trading partner. Its only meant for internal EAI or A2A purpose.

Step5:

In the Thumbnail field, Copy-paste the thumbnail of the certificate that you installed from your trading partner. (see below section for how to install certificates). This is only required if you trading partner is enforcing you use the certificate. Otherwise, it will work with user name and password.


PART#2
How to install Public Certificates from Trading partner (ie. Client side certificate):

To Send http post (encrypt) with SSL, you need to have the Trading partner’s public certificate installed in the Other People folder in MMC-Certificate store:

Before you Install Cert in MMC, here are the steps to open the MMC:
a. Start Run > MMC
b. File Add remove snap-in
c. Click the Add button and choose Certificates from the snap-in list
d. Click Add and then Computer Account from the next prompt
e. Click Finish

Now open the MMC for Certificates as below:

Note: You need to log in to the server using the BizTalk Service Acc (the acc under which BT runs) to install the certificate both own private certificates into Personal folder or trading partner public certificates into Other People folder.

All the information is available on MSDN, here is the coolest link.http://msdn2.microsoft.com/en-us/library/aa953654.aspx

How to install Trading partner Public Certificates in server:

Step1:

Open the trading partner URL (eg. https://staging.myappro.com/access/Main.asp ) in IE and it may ask for UserName and Password if login credential is imposed by your trading partner.

Step2:

Once the page opens go to File menu -> Properties ->Click Certificate Button

You can get the Thubmprint of the certificate there at the Detail tab

Step3:

Click Install Certificate button. It will open the cert installation wizard.

Step4:

Use default settings and install it. It will install the certificate in the Other People folder (this is the default location for public certificates from your trading partner).

You can also change the folder location of the certificate to install in a diff folder.

NOTE: If your trading partner’s certificate is from a non-trusted vendor, you probably need to install the root certificate from you trading partner into the Trusted Root Certification Authority folder. It will prompt for warning, accept that.

The thumbprint needs to be put in the HTTP send port configuration while configuring the BT HTTP send port.

Other way:

If your trading partner has provided you a certificate file, you can install it another way (as mentioned here)

Step1:

Open IE, got to Tools menu --> Internet Options --> Content Tab

Step2:

Click certificate button to open certificate store: you can see the all certificate stores as tabs

Step3:

Select the appropriate tab (certificate store) and click Import button to open certificate import wizard and rest you know. If your partner is providing you the root certificate, then place in the trusted root certificate authorities store.

You probably also need to install the same certificate using MMC – Certificate console.


PART#3
How to install Own Private Certificate:

You need to have your own certificate from a certificate authority and copy the certificate file to your local hard disk. Open MMC-Certificate snap-in, the right click on Personal folder and Import the certificate.

To receive http post (decrypt) with SSL you need to have your own Private certificate installed in your Local Machine’s Personal folder under Certificate snap-in in MMC.

It will Pop-up the certificate import wizard.
Browse the certificate file and import / install the certificate.

Note: HTTP Receive locations need not be configured using Certificates as HTTP receive is handles at the IIS level using BizTalk Isolated host. All inbound HTTP messages can be decrypted at IIS level by proper certificate configuration at IIS.



PART#4
Configuration steps at BizTalk Server to Sign all Send Messages: (using own Private Cert)

For BizTalk Server 2004, configure the server with the certificate thumbprint.

Start the BizTalk Administration console. Then, configure the signing certificate thumbprint on the General tab in the Microsoft BizTalk Server 2004 (Local) Properties dialog box:

For BizTalk Server 2006, configure the server with the certificate name and thumbprint.

Start the BizTalk Administration Console, Right click the BizTalk Server Group, select Properties. It will pop-up Properties Dialog Box. Go to Certificate section on the left pane and browse to select the certificate.
The Certificate you are going to select here, are stored in the Personal Folder of the Certificate store. These are your own Private certificates that you want to sign all your outgoing messages from that particular BizTalk group.

Another Excellent piece of article is available in the web at
http://seroter.wordpress.com/2007/03/05/building-a-complete-certificate-scenario-with-biztalk-server-2006/
Thanks to this gentleman for making our life easy.

Ceating a Windows Service

Adding this link for my reference.
I have found this link to be very usefull in creating Windows Service.
http://www.developer.com/net/csharp/article.php/10918_2173801_1