tag:blogger.com,1999:blog-54495824312022165852024-03-16T19:52:51.316+01:00Mitch VanheldenA blog on my professional experiences with IT technologiesMitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.comBlogger128125tag:blogger.com,1999:blog-5449582431202216585.post-73079743129871329672013-10-03T12:23:00.000+02:002013-10-03T12:23:02.622+02:00SQL query: hourly processed messages for BizTalk portsOne of my clients wanted to know how many messages were processed on a certain BizTalk port. <br />
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port.<br /><br />
Here's the SQL query I used:<br />
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">use</span> BizTalkDTAdb
<span class="kwrd">SELECT</span> <span class="kwrd">top</span> 200
a.[Event/Direction],
a.[Event/Port],
<span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>(10), a.[Event/<span class="kwrd">Timestamp</span>], 111) <span class="kwrd">as</span> [<span class="kwrd">date</span>],
DATEPART(<span class="kwrd">HOUR</span>,a.[Event/<span class="kwrd">Timestamp</span>]) <span class="kwrd">as</span> [<span class="kwrd">Hour</span>],
<span class="kwrd">COUNT</span>(*) <span class="kwrd">as</span> [<span class="kwrd">Count</span>]
<span class="kwrd">FROM</span> [dbo].[dtav_MessageFacts] a
<span class="kwrd">inner</span> <span class="kwrd">join</span> dbo.btsv_Tracking_Parts b <span class="kwrd">on</span> a.[MessageInstance/InstanceID] = b.uidMessageID
<span class="kwrd">WHERE</span> a.[Event/Port] <> <span class="str">'PortSqlMonitor'</span>
<span class="kwrd">AND</span> a.[Event/Port] = <span class="str">'BizTalkPort'</span>
<span class="kwrd">AND</span> a.[Event/Direction] = <span class="str">'Receive'</span>
<span class="kwrd">AND</span> a.[Event/<span class="kwrd">Timestamp</span>] > DATEADD(<span class="kwrd">day</span>, -3,GETDATE())
<span class="kwrd">GROUP</span> <span class="kwrd">by</span> a.[Event/Port], <span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>(10), a.[Event/<span class="kwrd">Timestamp</span>], 111), DATEPART(<span class="kwrd">HOUR</span>,a.[Event/<span class="kwrd">Timestamp</span>]), a.[Event/Direction]
<span class="kwrd">ORDER</span> <span class="kwrd">BY</span> <span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>(10), a.[Event/<span class="kwrd">Timestamp</span>], 111), DATEPART(<span class="kwrd">HOUR</span>,a.[Event/<span class="kwrd">Timestamp</span>])</pre>
<br /><br />
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com6tag:blogger.com,1999:blog-5449582431202216585.post-50243345624607684392013-09-17T13:06:00.000+02:002013-09-17T13:06:14.917+02:00Using Fiddler for viewing exchanged messages with BizTalkI was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.<br />
To do so, I used Fiddler2... available from <a href="http://fiddler2.com/">http://fiddler2.com/</a>.
<br /><br />
Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you'll need to configure a proxy to allow Fiddler to intercept the messages.<br />
<b>Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.</b>
<br /><br />
In the send port of BizTalk go to the Configuration settings. Open the tab 'Proxy' to configure the necessary settings. Now you should change following settings:
<ul>
<li>Server: 127.0.0.1</li>
<li>Port: 8888</li>
</ul>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivcXkQNymG2SxaiTDTL3n3eqPmOabBv4TA_-x5HVOffHypKeXZ-ESr8eQ8NKy-TYty9NrXn0Zb-n23YrzV_ykBDe-4shDq4nenuXcBWc34BcrUqmlJYBZfLjy6a6nYynhoilLMGCMRNR6c/s1600/ProxySettings.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivcXkQNymG2SxaiTDTL3n3eqPmOabBv4TA_-x5HVOffHypKeXZ-ESr8eQ8NKy-TYty9NrXn0Zb-n23YrzV_ykBDe-4shDq4nenuXcBWc34BcrUqmlJYBZfLjy6a6nYynhoilLMGCMRNR6c/s400/ProxySettings.jpg" /></a></div>
<br />
That's is all you need to do. Now open fiddler2 and process a message.
If all is configured correctly, you should see the exchanged messages coming through fiddler.
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifkIaOKJy_Z-Th7HJ-gog7UMJ_-bacLxMOq9EvZjb60Z6wjdIalVC6T1VorQzkNOEerB3G5nW_yUsbIv6CO_QmSoMAyuHGI1AryG1rYFo4uzfMn7OIxLwVoUAiIGXGaGmZcOl5akXyaN_f/s1600/Fiddler.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifkIaOKJy_Z-Th7HJ-gog7UMJ_-bacLxMOq9EvZjb60Z6wjdIalVC6T1VorQzkNOEerB3G5nW_yUsbIv6CO_QmSoMAyuHGI1AryG1rYFo4uzfMn7OIxLwVoUAiIGXGaGmZcOl5akXyaN_f/s400/Fiddler.jpg" /></a></div>
<br /><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-65053815076354060692013-07-19T22:39:00.001+02:002013-07-19T22:41:24.781+02:00BTUG.be is born<b>I would like to make a small announcement... today we publicly announced the birth of a brand new Belgian BizTalk User Group, called BTUG.be! We were planning something like this for quite some time now, and you even might have picked up some rumours about it in the past. But as of today it is official, Belgium now has its very own BizTalk User Group.</b>
<BR /><BR />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgE753iW7THoxU_IIRHz4rIRX5FeINU-87l1JXQ-HXBlzf0R9WdMBdTqb0hFFnYhiKisBKrb-3NrWIs1V0d9eW97bYuPiPic7zE1hxDACogB-7qmvTxXfwVFy5GY2hGIl-AWmBQTd834hSM/s1600/BTUG.be.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgE753iW7THoxU_IIRHz4rIRX5FeINU-87l1JXQ-HXBlzf0R9WdMBdTqb0hFFnYhiKisBKrb-3NrWIs1V0d9eW97bYuPiPic7zE1hxDACogB-7qmvTxXfwVFy5GY2hGIl-AWmBQTd834hSM/s1600/BTUG.be.png" /></a><BR /><BR />
I'm proud to say that I'm one of the co-founders of this BTUG.be. Together with <a href="http://www.linkedin.com/in/samvanhoutte">Sam Vanhoutte</a> we have started this initiative under excellent guidance of Lex Hegt, who shared his knowledge he gained with the Dutch BizTalk User Group.
<BR />And to start out, we wanted to make it a joined collaboration of the Belgian BizTalk Community. In this spirit we are glad that also i8c decided to support the initiative and <a href="http://www.linkedin.com/pub/sven-van-den-brande/1/b26/9a2">Sven Van den brande</a> agreed to fulfill our board of the BTUG.be. So that makes the all new Belgian BizTalk User Group will be worked out as a joined effort by Cnext, Codit and i8c with in the board at startup myself, Sam Vanhoutte and Sven Van den brande.
<BR />
I'm already looking forward on having a great time working together with these guys organising all upcoming events.
<BR /><BR />
A very first launch event is being planned to have place in november. For keeping up to date on all matters of this BTUG.be, I'll recommend you subscribe to the mailing list on the website <a href="http://www.btug.be">http://www.btug.be</a> and even keep a close eye to the website for all announcements. And certainly don't forget to follow our twitter account <a href="https://twitter.com/btugbe">@BTUGbe</a>.
<BR /><BR />
And last of all, I would like to call out for sponsors... As the entire organization will be driven on volunteer work. So to cover all the necessary costs we will need to make for hosting events, catering, travel expenses in case of international speakers, etc. we are on the lookout for sponsors to keep it fun and free for everyone. You can always contact us on any questions or to share your interest for sponsoring using <a href="http://www.btug.be/?page_id=582">this entry form on the website</a>.
<BR /><BR />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-8412007318599212732013-05-27T10:11:00.002+02:002013-05-27T10:11:59.087+02:00BizTalk blog posts 02/2013 - 05/2013The past time I haven't posted a lot on this blog, especially not much blog posts on BizTalk subjects. This doesn't mean that I haven't done any BizTalk related blog posts, I just haven't done it on my personal blog for a while... I've mostly posted on my companies blog, where I am the sole contributor and administrator.
<br /><br />
But for all of you loyal readers of my personal blog, I thought it would be nice to give an overview of all of my latest posts. I'm thinking of doing this every once in a while to keep all of you updated on ALL of my posts.
<br /><br />
So here goes for the period till May 2013...
<UL>
<LI><b><i><a href="http://blog.cnext.eu/2012/12/07/biztalk-error-the-message-found-multiple-request-response-subscriptions/" target="blank">BizTalk error: ‘The message found multiple request response subscriptions’</a></i></b>: solution to sending messages to multiple request-response send ports</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/02/05/biztalk-file-mask-difference-for-windows-7-and-windows-server-2008-r2/" target="blank">BizTalk file mask difference for windows 7 and windows server 2008 r2</a></b></i>: A difference in file mask usage between windows 7 and windows server 2008 r2</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/03/12/biztalk360-v6-0-new-features-explained/" target="blank">BizTalk360 v6.0: new features explained</a></b></i>: a quick view on some of the improvements introduced in the new BizTalk360 version 6.0</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/03/21/biztalk-server-2013-is-now-rtm/" target="blank">BizTalk Server 2013 is now RTM!</a></b></i>: the announcement of the new BizTalk version release on MSDN, with the new features listed</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/04/08/bts2013-features-explained-xslcompiledtransform/" target="blank">BTS2013 features explained: XslCompiledTransform</a></b></i>: I started a new series on viewing some of the new features of BizTalk 2013 in more detail, the first one was about the XslCompiledTransform</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/04/15/bts2013-features-explained-improvements-in-dynamic-send-ports/" target="blank">BTS2013 features explained: Improvements in dynamic send ports</a></b></i>: follow up post on a new BizTalk 2013 feature on the Improvements in dynamic send ports</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/05/14/bts2013-features-explained-dependencytracking/" target="blank">BTS2013 features explained: Dependency tracking</a></b></i>: an in depth view of the new dependency tracking feature of BizTalk 2013</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/04/11/first-cumulative-update-for-biztalk-2013-available-through-microsoft-update/" target="blank">First Cumulative Update for BizTalk 2013… available through Microsoft Update!</a></b></i>: the announcement that the very first cumulative update was released for BizTalk 2013, and that it even was available through Microsoft Update</LI>
<LI><i><b><a href="http://blog.cnext.eu/2013/05/24/bam-error-failed-to-list-permissions-for-bam-view-system-data-sqltypes-sqlnullvalueexception-data-is-null/" target="blank">BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null</a></b></i>: a solution to a BAM issue I encountered</LI>
</UL>
<br />
Hope you enjoy these blog posts!<br /><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-31605013381911514372013-04-17T17:17:00.001+02:002013-04-17T17:26:54.934+02:00Became an official BizTalk360 Product SpecialistYesterday, BizTalk360 introduced the whole new title "BizTalk360 Product Specialist" on <a href="http://blogs.biztalk360.com/introducing-biztalk360-product-specialist/">their blog</a>. Because of the constantly increasing popularity of the monitoring tool BizTalk360, to streamline the process the new title and badge where introduced. The idea is to honour individuals who have gained adequate knowledge in installing, configuring and implementing BizTalk360 solution at customer sites. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZnwMphFxua4vgLzH3RXqjTWQAUkVy1_Mwq8OT287Z6VBHFVPEuMbKTnbsZ3Un-_xEdxALj_8VDx1FcDqgj6MIxpY1pTPFIJC5Yu7rlUE5_Zs53VGd-Tzb_IFFG57HXYSVxZkGr-HW02G/s1600/Badge_W250_thumb.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" dua="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZnwMphFxua4vgLzH3RXqjTWQAUkVy1_Mwq8OT287Z6VBHFVPEuMbKTnbsZ3Un-_xEdxALj_8VDx1FcDqgj6MIxpY1pTPFIJC5Yu7rlUE5_Zs53VGd-Tzb_IFFG57HXYSVxZkGr-HW02G/s1600/Badge_W250_thumb.png" /></a></div>
<br />
<br />
The only way of obtaining this title is by receiving the confirmation from the BizTalk360 team itself. I myself filed a request immediately... And I am proud to announce my request got approved. Now I have the honour to officially call myself a "BizTalk360 Product Specialist"!<br />
<br />
As you might have seen, I already placed the official badge at my own blog on the column on the right. I also updated my LinkedIn profile accordingly.<br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com3tag:blogger.com,1999:blog-5449582431202216585.post-87940108982005566592013-04-11T15:50:00.005+02:002013-04-11T15:54:43.817+02:00Guide to a BizTalk High Availability Server Environment I just discovered a series of blog post that I just had to share with all of you... It's a complete guide to set up a High available BizTalk environment. <br />
A perfect walthrough from start to finish to obtain a BizTalk High availability environment.<br />
<br />
The guide covers 7 blog posts in total: <br />
<ul>
<li><a href="http://blog.brauwers.nl/2011/03/30/12/" target="_blank">Part 1: Preparations</a></li>
<li><a href="http://blog.brauwers.nl/2011/04/02/part-2-biztalk-high-availability-server-environmentdomain-controller-installation/" target="_blank">Part 2: Domain Controller Installation</a></li>
<li><a href="http://blog.brauwers.nl/2011/04/04/part-3-biztalk-high-availability-server-environment-sql-biztalk-active-directory-accounts/" target="_blank">Part 3: SQL & BizTalk Active Directory Accounts</a></li>
<li><a href="http://blog.brauwers.nl/2011/04/18/part-4-biztalk-high-availability-server-environment-prepping-our-sql-biztalk-failover-clusters/" target="_blank">Part 4: Prepping our SQL & BizTalk Failover Clusters</a></li>
<li><a href="http://blog.brauwers.nl/2011/05/01/part-5-biztalk-high-availability-server-environment-sql-server-2008r2-failover-cluster/" target="_blank">Part 5: SQL Server 2008r2 Failover Cluster</a></li>
<li><a href="http://blog.brauwers.nl/2011/05/14/part-6-biztalk-high-availability-server-environmentbiztalk-2010-failover-cluster-creation/" target="_blank">Part 6: BizTalk 2010 Failover Cluster Creation</a></li>
<li><a href="http://blog.brauwers.nl/2011/05/31/part-7-biztalk-high-availability-server-environment-biztalk-2010-installation-configuration-and-clustering/" target="_blank">Part 7: BizTalk 2010 Installation, Configuration and Clustering</a></li>
</ul>
<br /><br />
Congrats to René Brauwers on the great guide for setting up a High available BizTalk environment.<br /><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com8tag:blogger.com,1999:blog-5449582431202216585.post-28657381603816147532013-04-11T15:11:00.004+02:002013-04-11T15:19:46.553+02:00CU1 for BizTalk 2013... using Microsoft Update!<b>The Microsoft Team has just announced the release of the very first Cumulative Update For BizTalk 2013.</b> <br />
<br />
But an even more surprising news is that it is available using Microsoft Update!... This is the very first time a BizTalk Update can be obtained with Microsoft Update, as before it had to be done by downloading it manually through the hotfix download. An amazing improvement if you ask me, making it a whole lot easier for administrators. <br />
<br />
The update will still be available through the hotfix download though.<br />
<br />
<br />
<h2>
The Content of the Cumulative Update</h2>
<br />
The very first CU for BizTalk 2013 brings 3 fixes: <br />
<ul>
<li>User cannot perform certain database-related operations in BizTalk Server 2013</li>
<li>BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment</li>
<li>The vertical scroll bar on the target schema does not work correctly when you use Visual Studio to design a BizTalk Server 2013 map </li>
</ul>
<br />
<br />
<h2>
How to install using Microsoft Update</h2>
<br />
The following steps are applicable if you have Microsoft Update enabled in the machine where BizTalk Server is installed. <br />
<ol>
<li>Log into a server where BizTalk Server is already installed, and Microsoft Update is enabled</li>
<li>Check for windows update</li>
<li>Notice available fixes for BizTalk Server under optional updates</li>
<li>Select the update and install</li>
</ol>
(Perform the above operations in each of the BizTalk nodes) <br />
<br />
The original announcement can be found <a href="http://blogs.msdn.com/b/biztalkcrt/archive/2013/04/10/biztalk-server-2013-updates-are-now-available-on-microsoft-update.aspx" target="_blank">here</a>. <br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-16302001721065235022013-03-21T21:56:00.000+01:002013-03-21T21:57:32.099+01:00Microsoft BizTalk Server 2013 available on MSDNIt took a while, but here it is... BizTalk Server 2013 is now in RTM (Manufacturing)! as of now you can download BizTalk 2013 from MSDN, and it will be available for purchase in April.<br />
<br />
Besides all kinds of new features, the new version of BizTalk also brings us a whole new licensing model. At least it is new in terms of BizTalk pricings... it will be a per-core licensing model, just like the SQL Server licensing.<br />
<br />
A more detailed info can be found on <a href="http://blog.cnext.eu/2013/03/21/biztalk-server-2013-is-now-rtm/" target="_blank">my companies blog post</a>.<br />
the official announcement of the BizTalk team, can be found in <a href="http://blogs.msdn.com/b/biztalk_server_team_blog/archive/2013/03/21/biztalk-server-2013-is-now-rtm.aspx" target="_blank">this post</a> on their blog.
<br /><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-26535864778591566302013-03-08T10:44:00.000+01:002013-03-08T10:44:21.006+01:00Error on Calendar/Contacts sharing in OutlookI 've encountered a problem sharing Contacts from within Outlook. We were trying to share a contactgroup where both parties are from the same company, so on the same Office365 license.<br />
However, on trying to send out a share request we stumbled upon following error:<br />
<br />
<i>"Problem: Your organization's policy does not allow you to share with this recipient."</i><br />
<i><br /></i>
At first it seemed that there should be anything wrong with the organizations settings made in office365. But nothing strange was configured there... Setting the permissions to the other mail addres did seem to work. What got me questioning there would be anyhting wrong in the configurational settings.<br />
<br />
It turned out there was a difference when trying to share the contact or setting the permission. And after realising this, even sharing the contact worked just fine... without changing any of the configuration settings!<br />
<br />
<h2>
Solution</h2>
<div>
<br />
What turned out to be the problem than?</div>
<div>
<br /></div>
<div>
when the user started typing the recipient name in the "To" box of the "Share invitation" dialog, the recipient was picked up by autocompleteion. However, the autocompletion picked up the recipient from the Contacts instead of from the Global Address List. So the name of the recipient appeared as "User Name <user@company.com>", where it had to be "<span style="text-decoration: underline;">User Name</span>" when you choose from the Global Adress list.</div>
<div>
<br /></div>
<div>
So on sending the email invitation to share the contact list, Microsoft Exchange considered the recipient to be outside the organization. Which resulted in the above error message of course.</div>
<div>
<br /></div>
<div>
<br /></div>
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com51tag:blogger.com,1999:blog-5449582431202216585.post-6829724255543156102013-02-27T14:47:00.003+01:002013-02-27T14:48:33.934+01:00Change SQL backup locationDuring the installation of a SQL instance, you'll have the ability to define some folders<br />
These locations can be defined:<br />
<ul>
<li>User database directory</li>
<li>User database lof directory</li>
<li>Temp DB directory</li>
<li>Temp DB log directory</li>
<li>Backup directory</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjegrntR59fgMEDILgwDTbpAy73nIyG_9i172oaefSMH9_FJgakZ4Wa7Azt4DGiEAWIJjE4PVhvTCJQ_ZEl64gAukxJCrl1YQNurzNItw4VgU7ZvSD0x6uEPJVgTC3XKJRcTvEeltKAhOeb/s1600/install.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" gsa="true" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjegrntR59fgMEDILgwDTbpAy73nIyG_9i172oaefSMH9_FJgakZ4Wa7Azt4DGiEAWIJjE4PVhvTCJQ_ZEl64gAukxJCrl1YQNurzNItw4VgU7ZvSD0x6uEPJVgTC3XKJRcTvEeltKAhOeb/s400/install.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div align="left">
Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.</div>
<div align="left">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaZbfhtPE4j5uFYy5sA4wgPwuMqrryJ1Wuj1NQ7DHGUg2iF9KT3CfFp-clWsKN6-oYLbYta47yEwHwktuzIenOoo7z3_rUFKmXBGyvF6Zx0FVwOLqHxqOzzuQnDE3kTyiGCW-AFzyP_0iP/s1600/install.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" gsa="true" height="355" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaZbfhtPE4j5uFYy5sA4wgPwuMqrryJ1Wuj1NQ7DHGUg2iF9KT3CfFp-clWsKN6-oYLbYta47yEwHwktuzIenOoo7z3_rUFKmXBGyvF6Zx0FVwOLqHxqOzzuQnDE3kTyiGCW-AFzyP_0iP/s400/install.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div align="left">
<br /></div>
But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory. To find this we need to look in the registry.<br />
<br />
Open the registry tool REGEDIT and navigate to following key:<br />
<em><strong>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer</strong> </em><br />
<br />
Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you'll need to change to set another default Backup Directory. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvVLL0mL7jBAUqO6MEsn5-vXc9A5yz9Ob6zZ4DheLi-G-XLEWc-2oN4Bh82mPuh6lccxmoSImZBS9fVGGBKLTnJfAqaOXtWj755GPKKmuQcxcu8KC1l6KuL-_0obVj6Pt-0O-o1faAF7SI/s1600/registr.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" gsa="true" height="207" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvVLL0mL7jBAUqO6MEsn5-vXc9A5yz9Ob6zZ4DheLi-G-XLEWc-2oN4Bh82mPuh6lccxmoSImZBS9fVGGBKLTnJfAqaOXtWj755GPKKmuQcxcu8KC1l6KuL-_0obVj6Pt-0O-o1faAF7SI/s400/registr.png" width="400" /></a></div>
<div align="left">
</div>
<div align="left">
Changing the registry can also be done with a T-SQL query command. To do so, you'll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.</div>
<div align="left">
<br /></div>
Reading the falue in registry can be done by using this command:<br />
<br />
<span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;">DECLARE </span><span style="color: #434343;">@BackupDirectory </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">) </span></span></span><br />
<span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;">EXEC </span><span style="color: black;">master..</span><span style="color: darkred;">xp_regread </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@key</span><span style="color: blue;">=</span><span style="color: red;">'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@value_name</span><span style="color: blue;">=</span><span style="color: red;">'BackupDirectory'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@BackupDirectory</span><span style="color: blue;">=</span><span style="color: #434343;">@BackupDirectory </span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;">OUTPUT </span><span style="color: blue;">SELECT </span><span style="color: #434343;">@BackupDirectory</span></span></span> <br />
<br />
This will result in something similar as this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcQu_Plr6lI1V4TkGMQ060RQd9AgjWwyDQcvdbC5WUSGC-Kl2Hx8Fo_LN1hpKyjmf2IkbAiTQW1VcsOtVHamkTmVyd78TXtnnm1jo_mwVW1mpd-X-l5hwq5Mwso8sRCb6UXsVF5gKYM3SS/s1600/setting.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" gsa="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcQu_Plr6lI1V4TkGMQ060RQd9AgjWwyDQcvdbC5WUSGC-Kl2Hx8Fo_LN1hpKyjmf2IkbAiTQW1VcsOtVHamkTmVyd78TXtnnm1jo_mwVW1mpd-X-l5hwq5Mwso8sRCb6UXsVF5gKYM3SS/s1600/setting.png" /></a></div>
<br />
<br />
Changing the default folder can be done by using the following command<br />
<br />
<span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;">EXEC </span><span style="color: black;">master..</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: darkred;">xp_regwrite <br /> </span><span style="color: #434343;">@rootkey</span><span style="color: blue;">=</span><span style="color: red;">'HKEY_LOCAL_MACHINE'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@key</span><span style="color: blue;">=</span><span style="color: red;">'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@value_name</span><span style="color: blue;">=</span><span style="color: red;">'BackupDirectory'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@type</span><span style="color: blue;">=</span><span style="color: red;">'REG_SZ'</span></span></span><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: grey;">, <br /> </span><span style="color: #434343;">@value</span><span style="color: blue;">=</span><span style="color: red;">'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup'</span></span></span> <br />
<br />
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-39034058767082205302013-02-27T14:15:00.000+01:002013-02-27T16:34:09.007+01:00key file authentication with the BizTalk SFTP adapter<strong>Authentication on an SFTP location can be done with simple username/password credentials… However another way to authenticate the sender is by using a Key Authentication file. But how can this last authentication method be used inside the BizTalk SFTP adapter?</strong><br />
<br />
the BizTalk SFTP adapter we used to achieve this is the freely available <a href="http://sftpadapter.codeplex.com/" target="_blank">SFTP adapter on Codeplex</a>. Just download the adapter and install it. And don’t forget to add the adapter in the BizTalk admin console of course.<br />
<br />
Now then let’s set up the port in BizTalk. In my example I ‘ve configured a receive location, but obviously the same goes for the send port configuration.<br />
<br />
First of all choose the created adapter in the biztalk admin console, as shown below.<br />
<br />
<a href="http://cnext.files.wordpress.com/2013/02/first.png"><img alt="Port configuration" class="aligncenter size-medium wp-image-330" height="318" originalh="239" originalw="300" scale="1.5" src-orig="http://cnext.files.wordpress.com/2013/02/first.png?w=300&h=239" src="http://cnext.files.wordpress.com/2013/02/first.png?w=450&h=359" width="400" /></a><br />
<br />
To set up the proper configuration for the SFTP port, just press the ‘Configure’-button.<br />
<br />
<a href="http://cnext.files.wordpress.com/2013/02/configure.png"><img alt="Configure" class="aligncenter size-full wp-image-331" height="400" originalw="640" scale="1.5" src="http://cnext.files.wordpress.com/2013/02/configure.png?w=640" width="359" /></a><br />
<br />
These parameters need to be set:<br />
<ul>
<li>Schedule: define a timely schedule how often you want to poll the SFTP location (receive location only)</li>
<li>File Mask: set the correct file mask</li>
<li>SSH Host: the host address of the SFTP location</li>
<li>SSH Identyfile: Select the Key authentication file on disk</li>
<li>SSH Identyfile Passphrase: the password of the selected key authentication file</li>
<li>SSH Remote Path: the path on on the SFTP location</li>
<li>SSH User: the user name to authenticate on the SFTP location</li>
</ul>
Seems pretty straight forward, doesn’t it?<br />
Well there are some things you ‘ll need to take into account to make sure the connection can be made…<br />
<br />
<h2>
Make sure the identyfile type is supported</h2>
You’ll need to make sure the identyfile type is supported by the library. The supported versions can be found <a href="http://www.tamirgal.com/blog/page/SharpSSH.aspx" target="_blank">here</a>. As this is the library which is used by the bizTalk SFTP adapter.<br />
<br />
<h2>
Make sure the identyfile can be recognized</h2>
In my case for example, I got an *.ppk file as authentication file. But as it turned out, this wasn’t recognized.<br />
The error message you git is this:<br />
<dd><em>invalid privatekey: D:\tempkey.ppk</em></dd><br />
Which in the end seemed a bit misleading, as the problem wasn’t the private key for the authentication file. It was just the *.ppk file that wasn’t supported.<br />
To make the key authentication file working, I had to convert the file to an OpenSSH key file. This can be done by using PuttyGen for example, and exporting the authentication file as an OpenSSH key file.<br />
<br />
<h2>
Don’t use both password parameters</h2>
Also make sure you only use the intended parameter SSH Identyfile Passphrase, and leave the SSH password blank.<br />
In case both password fields are filled, the SFTP adapter will try to authenticate by the username/password credentials stated. So it won’t offer the specified identyfile to authenticate.<br />
<br />
<br />
<br />
<br />
I orignally posted this on my companies blog: <a href="http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/">http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/</a><br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-38950751834702457312013-02-01T14:30:00.000+01:002013-02-01T14:30:23.492+01:00SQL Server Compact 4.0 Tooling in Visual Studio 2010I developed a program which uses SQL Server Compact. Initially it was developed creating SQL Server COmpact 4.0 databases. Now after reinstalling my laptop, I tried to open the database, but it appaered I was missing the necessary tooling for SQL Server Compact 4.0, I could only open/create SQL Server Compact 3.5 databases.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwzBgi9drT4HNkTfVXNLaGEeuYKxFrSVSTXUtobLH_cMTVoSsezHWRXN1EmaZX7LHEHW3KF6rP_X8efCZVcVVooZOD0VojsoPZvrmT0tbL1HAQYXZ2ofrbG8FgG-mCAqoRAHB3ZCFXBCh2/s1600/Pre.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwzBgi9drT4HNkTfVXNLaGEeuYKxFrSVSTXUtobLH_cMTVoSsezHWRXN1EmaZX7LHEHW3KF6rP_X8efCZVcVVooZOD0VojsoPZvrmT0tbL1HAQYXZ2ofrbG8FgG-mCAqoRAHB3ZCFXBCh2/s400/Pre.png" width="400" /></a></div>
<br />
<br />
I've immediatly installed the SQL Server Compact 4.0 runtime, which can be found <a href="http://www.microsoft.com/en-us/download/details.aspx?id=17876" target="_blank">here</a>.<br />
But this however didn't seem to be enough... I still couldn't open my created SQL compact database.<br />
<br />
What I needed to do, was install some additional software using <a href="http://www.microsoft.com/web/gallery/install.aspx?appid=webmatrix" target="_blank">Microsoft Web Platform Installer 3.0</a>.<br />
Make sure you install these packages:<br />
<ul>
<li>Microsoft Visual Studio 2010 SP1</li>
<li>Microsoft Visual Studion 2010 SP1 Tools for SQL Server Compact 4.0</li>
</ul>
<a href="http://www.microsoft.com/web/gallery/install.aspx?appid=webmatrix"><span style="color: blue; font-family: Calibri; font-size: x-small;"></span></a><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYb5e1qZa-WTkU6j7smaX0cfdktey5gJnxTenD1hAty3nk7d-yXddQx8YS01DDalXzMhOPDUetNiHe4dnOr7Ottpus7uJ1CnEutdPgPV0qsfeuGoN3fPoqS21TFqs1SvwFHX4yJ9ej8VP1/s1600/Web+Matrix.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="435" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYb5e1qZa-WTkU6j7smaX0cfdktey5gJnxTenD1hAty3nk7d-yXddQx8YS01DDalXzMhOPDUetNiHe4dnOr7Ottpus7uJ1CnEutdPgPV0qsfeuGoN3fPoqS21TFqs1SvwFHX4yJ9ej8VP1/s640/Web+Matrix.png" width="640" /></a></div>
<br />
Note that selecting and installing the Visual Studio 2010 SP1 or the Visual Web Developer Express 2010 SP1 also installs the SQL Server Compact 4.0 components that are needed for the tooling support. These components are called as Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 and SQL Server Compact 4.0. The Web Platform Installer automatically detects whether these components are already installed on the machine, and if they are not installed, the Web Platform Installer will install them. There is no need to separately select the SQL Server Compact 4.0 components in the Web Platform Installer.<br />
<br />
The installer for visual studio SP1 can also be found <a href="http://www.microsoft.com/en-us/download/details.aspx?id=23691" target="_blank">here</a>.<br />
<br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-51814739296620945882013-01-07T11:05:00.001+01:002013-01-07T12:41:22.609+01:00Error: The self-extracting zip file is part of a multidisk zip file I tried to install the Cumulative update package 14 for SQL Server 2008 R2.<br />
<br />
After requesting the update on <a href="http://support.microsoft.com/kb/2703280">http://support.microsoft.com/kb/2703280</a>, I got an email containing a link to download the file 449383_intl_x64_zip.exe. After downloading the file an trying to run the self extracting zip file, I got following error:<br />
<br />
<em>This self-extracting zip file is part of a multidisk zip file. Please insert the last disk of the set.</em><br />
<br /><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqGtExbjRIX6rZS5JGp9vYjxtGfWRPGlTFDtCO0KOnvK9UwtezAcOpXenlvj9lCQlGrPoYlnBF2Pr6sZviGvmhKgPjdHW_iiDpHIXPUs1lk5317AFgW_4P2I583KzShpJAIg2uRU9awN0s/s1600/bad.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="136" width="322" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqGtExbjRIX6rZS5JGp9vYjxtGfWRPGlTFDtCO0KOnvK9UwtezAcOpXenlvj9lCQlGrPoYlnBF2Pr6sZviGvmhKgPjdHW_iiDpHIXPUs1lk5317AFgW_4P2I583KzShpJAIg2uRU9awN0s/s400/bad.png" /></a></div>
<br />
There is no other option than pressing the OK-button.And after pressing the OK-button 3 times, I got following error message:<br />
<br /><br />
<em>An error occured while unzipping. One or more files were not succesfully unzipped. The error code is 110.</em><br />
<br /><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilzojfzzQMGjzkdgt7etyhs9ekFyBjYJ0CSSgbpoQluKGev81Gevdt_uot7fZyoz_4GDt5lPwiB3HDSWsRnTP_RmOa6Yayy1ASBIJ1q6_d7x-IFMowAyxm9Y539w8i185RZ7O_rHF3QHRE/s1600/Bad2.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="130" width="323" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilzojfzzQMGjzkdgt7etyhs9ekFyBjYJ0CSSgbpoQluKGev81Gevdt_uot7fZyoz_4GDt5lPwiB3HDSWsRnTP_RmOa6Yayy1ASBIJ1q6_d7x-IFMowAyxm9Y539w8i185RZ7O_rHF3QHRE/s400/Bad2.png" /></a></div>
<br />
The solution for this seemed to be to just download the file from the given link in my email again.<br />
After this I could succesfully run the self-extracting zip file an install my cumulative update package.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNIJGHy1PKrUantqTTd6Awz-MuiSKlemSSzdi9wVhMYRGc4_3OiChazjDi9ZKfHLsTl__O5IPFHX81RiuY3hJ1oZne1m5fnYdeUh-A_AyNd7zoYYp-3IVLgZ00wqFVkDwgJ5eXAtuqqgqC/s1600/good.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="132" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNIJGHy1PKrUantqTTd6Awz-MuiSKlemSSzdi9wVhMYRGc4_3OiChazjDi9ZKfHLsTl__O5IPFHX81RiuY3hJ1oZne1m5fnYdeUh-A_AyNd7zoYYp-3IVLgZ00wqFVkDwgJ5eXAtuqqgqC/s400/good.png" /></a></div>
<br />
<br />
<br />
<em></em><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com18tag:blogger.com,1999:blog-5449582431202216585.post-37499191811868272362012-12-18T13:26:00.001+01:002013-01-09T10:45:27.446+01:00BizTalk deployment - How do YOU do it?<br />
<br />
<div class="MsoNormal">
<b><i>For the deployment of BizTalk applications we use a custom made tool within my company Cnext. Now I was curious to have some feedback from the community on the deployment of BizTalk applications. How do you handle the biztalk deployments at your company?</i></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Our deployment manager tool is based on the idea to store all used objects in a database model (BizTalk artifacts, but also stuff like MSMQ, file locations, SQL objects, etc. …). Most of these objects are added using auto discovery of the BizTalk databases, so manually adding of objects is reduced to a minimum. Above all, BizTalk artifacts can have a different configuration (binding) defined per environment (test, dev, prod, …).</div>
<div class="MsoNormal">
It also allows you to define all dependencies. Again most of these dependencies will be defined by the tool automatically.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This way of working makes it possible for the tool to define which actions need to be taken to deploy a certain application (or just a part of the application or only some objects). The deployment manager tool will define which objects need to be removed and redeployed (also unenlisting/disabling en starting/enabling artifacts will be done by itself). As a result, using the tool will allow us to deploy much faster, because the objects to redeploy are reduced to an absolute minimum and no complete redeploy is needed (like BizTalk Deployment framework does for example).</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Another very useful and much used functionality is the possibility to define complete business/functional flows, including some generic components. This makes sure you can also deploy or redeploy a complete (new) flow (like an order flow for example) by itself, including all necessary objects (as well receive as send ports, file locations, etc. …).</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The deployment can be done cross BizTalk applications. So the separation in applications is no longer deployment dependent.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The most important part is to set the database model correct and keep it this way… this will guarantee a much easier and faster deployment, where each environment has its own version of the current deployed objects.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Please answer following questions:</div>
<div class="MsoNormal">
<br /></div>
<ol>
<li><span style="mso-bidi-font-family: Calibri; mso-fareast-font-family: Calibri;"><span style="mso-list: Ignore;"> <span style="font: 7pt 'Times New Roman';"></span></span></span>Is your BizTalk deployment automated (BizTalk deployment framework, custom scripting with MSBuild or BTSTask, powershell scripts, etc. …), or do you just do manual deployment using MSI and binding files?</li>
<li><span style="mso-bidi-font-family: Calibri; mso-fareast-font-family: Calibri;"><span style="mso-list: Ignore;"><span style="font: 7pt 'Times New Roman';"> </span></span></span>Which deployment tools or scripts are you using, or have you used before? And what are your thoughts of these tools (benefits and complaints)?</li>
<li><span style="mso-bidi-font-family: Calibri; mso-fareast-font-family: Calibri;"><span style="mso-list: Ignore;"><span style="font: 7pt 'Times New Roman';"> </span></span></span>What do you think of a tool like our custom deployment manager tool (using autodiscovery, etc. …)?</li>
</ol>
<br />
<br />
Thanks for your replies.<br />
When there is enough feedback, I'll devote a new post on my analysis.<br />
<br />
<br />
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<br /></div>
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com2tag:blogger.com,1999:blog-5449582431202216585.post-8641269294595157242012-11-28T11:11:00.002+01:002012-11-28T11:11:41.200+01:00SQL Server Configuration Manager - Error 0x80041010When opening the SQL Server Configuration manager (for SQL Server 2008 R2 in my case), I kept getting following error:<br />
<br />
<em>Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]</em><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1ILDC3FsT-eVkvhuPeA-1c0OAf_iz_cAUF2nIFO-PXYKWwd4jZNoAMsVOAK2EZm3TIT56v0yLDVibAb1lhnhLrAHcpdtf2VN_j2ZBYDJTlpBIu6tSzeWkriBIH0blNtXFu85yaOfW7YXc/s1600/8877.09112708CannotconnecttoWMIproviderSQLServerConfigurationManagerInvalidclassmofcompMOF_34778BA2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="160" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1ILDC3FsT-eVkvhuPeA-1c0OAf_iz_cAUF2nIFO-PXYKWwd4jZNoAMsVOAK2EZm3TIT56v0yLDVibAb1lhnhLrAHcpdtf2VN_j2ZBYDJTlpBIu6tSzeWkriBIH0blNtXFu85yaOfW7YXc/s400/8877.09112708CannotconnecttoWMIproviderSQLServerConfigurationManagerInvalidclassmofcompMOF_34778BA2.jpg" tea="true" width="400" /></a></div>
<br />
The solution to resolve this turned out to be quite simple. Just open a command prompt and execute following command:<br />
<br />
<em>C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"</em><br />
<br />
Depending on the SQL version installed the path to the file could also be something like C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof<br />
<br />
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-32174180622354773732012-11-14T13:44:00.000+01:002012-11-14T13:48:38.908+01:00Blog admin of my companies blogFirst of all, I would like to thank all of my regular reader and hope you've al enjoyed my blog posts for almost 2 years now. I already announced in one of my previous posts some time ago that I would join the contributors of the biztalk admin blog at <a href="http://www.biztalkadminsblogging.com/">http://www.biztalkadminsblogging.com/</a> .<br />
<br />
Today, I'm proud to announce that there is another brand new blog I will be contributing to on BizTalk and Azure related news, problem solvement, etc.... My companies (<a href="http://www.cnext.eu/" target="_blank">Cnext</a>) blog (<a href="http://blog.cnext.eu/">http://blog.cnext.eu</a>) . Well actually, the blog already existed quite some time (even longer than my own blog :) ), but there wasn't any new posts for over 2 years. Most recently I became the new administrator of the Cnext blog and had the task to revive it. After 1 month, 3 completely new posts, and still some blog posts in the making, it is ready to be shared with all of you... see for yourself at the <a href="http://blog.cnext.eu/" target="_blank">Cnexts revived blog</a>.<br />
<br />
The first thing I did is restyle the entire blog to match the current branding of Cnext. And after publishing some new material, I can say that it is ready to be read!<br />
<br />
Now go and check it out.. and keep following all of my blog posts.<br />
<br />
<br />
<br />
<br />
More info on cnext can be found on <a href="http://www.cnext.eu/">http://www.cnext.eu</a>. And as mentioned the blog will be situated at <a href="http://blog.cnext.eu/">http://blog.cnext.eu</a>... keep an eye out for all new material to be added soon!<br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-66140653428749184672012-11-07T12:32:00.000+01:002012-11-07T12:32:15.995+01:00Biztalk 2013 Beta releasedMicrosoft finally released a beta version of the new biztalk, wich can be found <a href="http://www.microsoft.com/en-us/download/details.aspx?id=35553" target="_parent">here</a>.<br />
The first thing that should be noticed is that Microsoft decided to change the next versions name from Biztalk 2010R2 to Biztalk 2013. This has some very important consequences… It indicates the next version to be a major release, which implies a much longer support from Microsoft.<br /><a href="http://support.microsoft.com/gp/lifepolicy?wa=wsignin1.0" target="_parent">Microsoft’s support lifecycle policy</a> says that products will have 5+5 years (mainstream+extended) support. However, that applies to major versions. If the product would still be BizTalk Server 2010 R2, it will not get an extended support lifecycle end date.<br /><br />So in conclusion, having the next Biztalk version being a full release is an important sign to the market, it implies a much longer support lifecycle for the product.<br />
<br />
<br />
Biztalk 2013 will have following new features:<br /><ul><br />
<li><strong>Integration with Cloud Services</strong>: messages using different relay endpoints hosted on Azure.</li>
<br />
<li><strong>RESTful services</strong>: provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.</li>
<br />
<li><strong>Enhanced SharePoint adapter</strong>: the need for dependency on SharePoint farms has been removed, while still providing backward compatibility.</li>
<br />
<li><strong>SFTP adapter</strong>: added an SFTP adapter that will enable sending and receiving messages from an SFTP server.</li>
<br />
<li><strong>ESB Toolkit integration</strong>: ESB Toolkit is now fully integrated with BizTalk Server. Also, the ESB Toolkit configuration experience is vastly simplified to enable a quick setup.</li>
<br />
<li><strong>Dependency tracking</strong>: dependencies between artifacts can now be viewed and navigated in Admin console.</li>
<br />
<li><strong>Improvements in dynamic send ports</strong>: the ability to set host handler per adapter, instead of always using the default send handler of the adapters.</li>
</ul>
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-13916314069768735532012-10-24T17:20:00.000+02:002012-10-24T17:25:43.416+02:00Exception handling in BizTalk orchestrations explainedWhen creating BizTalk orchestrations, we also have some building blocks available to do exception handling. In this blog I'll try to give a very brief explanation of the available building blocks for your orchestration. I won't describe everything in detail, because this would lead us to far (and would give one massive blog post). <br />
<br />
For exception handling in orchestration there are 3 shapes available: <br />
<ul>
<li>Throw Exception</li>
<li>Scope</li>
<li>Compensate</li>
</ul>
<br />
I want go in much details for the Scope and Compensate shapes. If you need more detailed description on how Exception handling can be used with these 2 shapes I would suggest reading <a href="http://blogs.msdn.com/b/richardbpi/archive/2006/12/06/transactions-and-compensation-using-biztalk-server.aspx">this blog from Richard Seroter</a>. And if this still doens't answers all of your questions, let me know... than I can devote another post with a more detailed explanation on some aspects of the exception handling in orchestrations. <br />
<br />
<b>Throw Exception</b><br />
<br />
Firstly we have the 'Throw Exception'-shape. this one is the simplest of all shapes for exception handling. <br />
Frankly, it does nothing more than it's name suggests... It just throws an Exception, nothing more fancy. To achieve this you'll need to follow these three simple steps: <br />
<ol>
<li>Define a variable with the type set to any Exception class you prefer. In below example I've created an exception variable of type Microsoft.Biztalk.Message.Interop.BTSException</li>
<br /><br /><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhilPaQHxpLKWsWKVbHW0_vIyeg_8OCEyZwtSWQkSwul61m2qSBEukBv3i7qZTxm2Qz5Mx_D7mkki6gdHlkL2FYN9oTAHazri8mtBT0lLITatC0uutrxqXbBVu0rGnm8pIG-OyKdWB-90An/s1600/ExceptionVar.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="316" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhilPaQHxpLKWsWKVbHW0_vIyeg_8OCEyZwtSWQkSwul61m2qSBEukBv3i7qZTxm2Qz5Mx_D7mkki6gdHlkL2FYN9oTAHazri8mtBT0lLITatC0uutrxqXbBVu0rGnm8pIG-OyKdWB-90An/s400/ExceptionVar.png" width="400" /></a></div>
<i>Note: The Report To Analyst property in the screenshot exists on many shapes. The property however has no influence on the behavior of your orchestration. I merely is a property used to integrate withe the Orchestration Designer for Business Analysts (ODBA) Visio plugin. If set to false, that shape will be considered as a low level detail that won't be visible in the Visio Designer.</i><br /><br />
<li>Instantiate the variable and set any values according to the occured error</li>
<li>Now use the Throw Exception shape and configure it ro use the Exception object you've created</li>
<br /><br /><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRZ_-dvqYOdZsSNSzMISQglJH37iyuQZOB76iog3E_96hyphenhyphenTYpYf4eltJCuIbbIhNy267bR2nXStubMWrgdr6kn0yoIEInnI9JhAn5OS3hMonrpMyQfw7vW4ELgdQBB3j9IuJksG7VWbvkp/s1600/ExceptionThrow.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="98" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRZ_-dvqYOdZsSNSzMISQglJH37iyuQZOB76iog3E_96hyphenhyphenTYpYf4eltJCuIbbIhNy267bR2nXStubMWrgdr6kn0yoIEInnI9JhAn5OS3hMonrpMyQfw7vW4ELgdQBB3j9IuJksG7VWbvkp/s400/ExceptionThrow.png" width="400" /></a></div>
</ol>
<br />
<b>Scope</b><br />
<br />
Scopes are used for three reasons <br />
<ol>
<li>to configure transaction (long running or atomic)</li>
<li>to handle exceptions</li>
<li>to trigger compensating logic</li>
</ol>
For more info on the transaction types, I'll refer to <a href="http://sandroaspbiztalkblog.wordpress.com/2009/08/15/what-are-the-different-types-of-transactions-available-for-orchestration/">this blog from Sandro Pereira</a>. <br />
<br />
A scope shape can have one or more Exception handling blocks and/or one Compensation Block. The Transaction type you've chosen for your scope will define which of these blocks can be added. An atomic scope can only have a compensation block, and a scope configured with the transaction type non can only have an Exception block. While a long running scope can have both blocks. <br />
For an overview of the possibilities, check the below screenshot. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRZfMrBSoYPKfNTHwY45mqZRAojVE2jo_rjgXQG2EN0dLADgEPvB1L-OgDD0t2aJWwPGwsIc_dQvFXsx5WMmVOkV_OqjcAXvu9fjQvB_ebhvBbc4IhLKdnT9c05YZd2uL0CJk6qSZ2Vy-A/s1600/Scopes.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRZfMrBSoYPKfNTHwY45mqZRAojVE2jo_rjgXQG2EN0dLADgEPvB1L-OgDD0t2aJWwPGwsIc_dQvFXsx5WMmVOkV_OqjcAXvu9fjQvB_ebhvBbc4IhLKdnT9c05YZd2uL0CJk6qSZ2Vy-A/s400/Scopes.png" width="400" /></a></div>
<i>Note that there could be more than one Exception block, but only one Compensation block added to a Scope</i><br />
<br />
<u>Exception handling block</u><br />
<br />
The Exception handling block is used to catch exceptions that occur inside the scope the block belongs to. The Exception Object Type in the properties of the exception block will define which Exceptions types will be handled by this Exception block. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigU0roSAmmlsowN9ThBV5I_LDVEZ8tA_XXySlfnLg3XJaFkNSXV_xVQXc2_MMlXuGRK3eMlL2VB_ViMvpJFcO-yCM70p-VUutA1or_UdMeEjs2JC9invKuTXN5OdZtmoSDfT5ydIJ9eF2C/s1600/prop.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigU0roSAmmlsowN9ThBV5I_LDVEZ8tA_XXySlfnLg3XJaFkNSXV_xVQXc2_MMlXuGRK3eMlL2VB_ViMvpJFcO-yCM70p-VUutA1or_UdMeEjs2JC9invKuTXN5OdZtmoSDfT5ydIJ9eF2C/s400/prop.png" width="400" /></a></div>
<br />
When an exception occurs, the engine will check for the closest exception block that can handle this type of exception. The consideration of which exception block will be called is determined sequentially, just like in .NET. If no suitable exception handler is found, the default exception handles is triggered, and this means that the compensation blocks for all nested scopes will be called, after which the exception is rethrowed and the orchestration will become suspended. <br />
<br />
<u>Compensation block</u><br />
<br />
Compensation is a way to correct or undo logical piece of work that have previously committed. An exception can occur in the orchestration afther succesfully executing all logic in the scope. In this case, the process might be in a state where it is logically incorrect. So the action already performed in a transactional scope need to be compensated... this can be achieved using a compensation block<br />
If no compensation block is added, the default compensation will be performed. this means calling all compensation blocks of the nested scopes, starting with the most recently completed ones, just as with the default exception handler. <br />
<br />
<b>Compensate</b><br />
<br />
The compensate shape can only be used inside of an exception handler. The explicit use of this compensate shape, allows us to specify the order of compensation execution ourselfs. So this can be used in cases the default order (starting from te most recently completed ones and working its way back) doesn't fit. <br />
1 compensate shape can only trigger 1 compensation block of a transaction. So the order must be defined using multiple compensate shapes, calling the compensation blocks in the desired order.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQ-lD75RgnuEmC7fPIZBO8SKpni6aOHzCMvrhcbeYOoRMvIm8KrE29XkBOdt7FlM0JxFBm5wOoHNBUHratyy2NNmiGyS-aHGdslFydBi6ljy-qPwer3zXuHuDU2CkiYeN3xBkHaJeuNG0J/s1600/comp.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQ-lD75RgnuEmC7fPIZBO8SKpni6aOHzCMvrhcbeYOoRMvIm8KrE29XkBOdt7FlM0JxFBm5wOoHNBUHratyy2NNmiGyS-aHGdslFydBi6ljy-qPwer3zXuHuDU2CkiYeN3xBkHaJeuNG0J/s400/comp.png" width="385" /></a></div>
<br />
<br />
<b>Remarks</b><br />
<br />
<ul>
<li>Keep in mind that the default Exception handler, if triggered, will initiate the compensation by calling all compensation blocks for the nested scopes. Compensation blocks of the nested scopes will be called starting with the most recently completed scope and work its way back. <br /><br />But whenever you catch an exception using the Exception handler block, then you must explicitly do the compensation. </li>
<br /><br />
<li>If no custom compensation block has been added, the default compensation will be performed. Just like with the exception handler, this will call the compensation blocks of any nested scopes starting with the most recently completed scope.</li>
<br /><br />
<li>A Compensate shape can only be used inside an Exception handling block or another Comensation Block. When calling the compensate of the current scope, the default compensatino handler will be triggered instead of the compensation code defined by the compensation block.</li>
</ul>
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com3tag:blogger.com,1999:blog-5449582431202216585.post-73207115257506462322012-10-17T17:18:00.002+02:002012-10-17T17:18:57.503+02:00Error on attaching DBWhen trying to reattach a database I kept getting following error:
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9iGkn9OBjf9KlWuH6hunuWxVD3UfY2HuXsrhKSLGBcI2PKEIkgAIw6Aq7FLC4IshBz8SkBTarozRoBiLjDyoTjr4rkP_PvDUtbmitXTVa5TCEhX3zGQsO6VY-ACVquoD-7SuJKb4S3eBj/s1600/error.png" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="144" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9iGkn9OBjf9KlWuH6hunuWxVD3UfY2HuXsrhKSLGBcI2PKEIkgAIw6Aq7FLC4IshBz8SkBTarozRoBiLjDyoTjr4rkP_PvDUtbmitXTVa5TCEhX3zGQsO6VY-ACVquoD-7SuJKb4S3eBj/s400/error.png" /></a></div>
<br />In the SQL log I could find below error. The same error was given when trying to attach the DB with a T-SQL statement.
<br /><br />
<I>Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSISDEV1\MSSQL\DATA\BizTalkMsgBoxDb.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
</I><br /><br />
After several attempts on how to solve this I finally found the solution.<br />
Turned out, all I needed to do was run the SQL Server Management Studio as an Administrator.
<br />
How can it be that simple, yet has me looking for a solution for over an hour :).
<br /><br />
Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-2585003307512192402012-10-09T10:41:00.003+02:002012-10-09T10:41:48.086+02:00BizTalk: using macros in file namesBizTalk allows you to use several macros for defining a file name in a biztalk send port. <br />
<br />
The following table lists the supported macros and describes how the File send handler replaces them.<br /><br />
<table border="1"><tbody>
<tr><th>Macro name </th><th>Substitute value </th></tr>
<tr><td>%datetime%</td><td>Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).</td></tr>
<tr><td>%datetime_bts2000%</td><td>UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).</td></tr>
<tr><td>%datetime.tz%</td><td>Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).</td></tr>
<tr><td>%DestinationParty%</td><td>Name of the destination party. The value comes from the message context property <strong>BTS.DestinationParty</strong>.</td></tr>
<tr><td>%DestinationPartyQualifier%</td><td>Qualifier of the destination party. The value comes from the message context property <strong>BTS.DestinationPartyQualifier</strong>.</td></tr>
<tr><td>%MessageID%</td><td>Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property <strong>BTS.MessageID</strong>.</td></tr>
<tr><td>%SourceFileName%</td><td>Name of the file from which the File adapter read the message. The file name includes the extension and excludes the file path, for example, Sample.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the <strong>FILE.ReceivedFileName</strong> context property. If the context property does not have a value—for example, if a message was received on an adapter other than the File adapter—the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%). <br />
<div class="alert">
<div class="mtps-table" xmlns="http://www.w3.org/1999/xhtml">
<div class="mtps-row">
<span class="mtps-th"><img alt="Aa578022.note(en-us,BTS.20).gif" class="cl_IC101471" id="note" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" title="Aa578022.note(en-us,BTS.20).gif" xmlns="" />Note </span></div>
<div class="mtps-row">
<span class="mtps-cell">Correct implementation of this macro requires that the output message is the same message as the received message.</span></div>
</div>
</div>
</td></tr>
<tr><td>%SourceParty%</td><td>Name of the source party from which the File adapter received the message.<br />
<div class="alert">
<div class="mtps-table" xmlns="http://www.w3.org/1999/xhtml">
<div class="mtps-row">
<span class="mtps-th"><img alt="Aa578022.note(en-us,BTS.20).gif" class="cl_IC101471" id="note" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" title="Aa578022.note(en-us,BTS.20).gif" xmlns="" />Note </span></div>
<div class="mtps-row">
<span class="mtps-cell">Correct implementation of this macro requires that the output message is the same message as the received message.</span></div>
</div>
</div>
</td></tr>
<tr><td>%SourcePartyQualifier%</td><td>Qualifier of the source party from which the File adapter received the message.<br />
<div class="alert">
<div class="mtps-table" xmlns="http://www.w3.org/1999/xhtml">
<div class="mtps-row">
<span class="mtps-th"><img alt="Aa578022.note(en-us,BTS.20).gif" class="cl_IC101471" id="note" src="http://i.msdn.microsoft.com/areas/global/content/clear.gif" title="Aa578022.note(en-us,BTS.20).gif" xmlns="" />Note </span></div>
<div class="mtps-row">
<span class="mtps-cell">Correct implementation of this macro requires that the output message is the same message as the received message.</span></div>
</div>
</div>
</td></tr>
<tr><td>%time%</td><td>UTC time in the format hhmmss.</td></tr>
<tr><td>%time.tz%</td><td>Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).</td></tr>
</tbody></table>
<br />
<h2>
<b>NOTE</b></h2>
The File send handler does not replace the macros with a value if any of the following are true: <br />
<ul>
<li>The corresponding system property is not set.</li>
<li>The macro is misspelled.</li>
<li>The value for the macro contains symbols that are not valid in the file name.</li>
</ul>
<br />
<h1>
<b>Using Macros in SMTP Headers</b></h1>
There are however some restrictions when using the macros in SMTP headers. <br />
See <a href="http://msdn.microsoft.com/en-us/library/aa578490(v=bts.10).aspx">this post</a> for more details on this matter.
<br />
<br />
Below is a short overview of the use of macros in SMTP headers. <br />
<br />
<table border="1"><tbody>
<tr><th>Macro </th><th>Description </th><th>For use with To </th><th>For use with CC </th><th>For use with Subject </th></tr>
<tr><td>%MessageID%</td><td>Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property <strong>BTS.MessageID</strong>.</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%datetime_bts2000%</td><td>UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%datetime%</td><td>UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%datetime.tz%</td><td>Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%time%</td><td>UTC time in the format hhmmss.</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%time.tz%</td><td>Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%SourceParty%</td><td>Name of the source party from which the File adapter received the message.</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%SourcePartyQualifier%</td><td>Qualifier of the source party from which the File adapter received the message.</td><td>No</td><td>No</td><td>Yes</td></tr>
<tr><td>%DestinationParty%</td><td>Name of the destination party. The value comes from the message context property <strong>BTS.DestinationParty</strong>.</td><td>Yes</td><td>Yes</td><td>Yes</td></tr>
<tr><td>%DestinationPartyQualifier%</td><td>Qualifier of the destination party. The value comes from the message context property <strong>BTS.DestinationPartyQualifier</strong>.</td><td>No</td><td>No</td><td>Yes</td></tr>
</tbody></table>
<br />
<br />
Source: <a href="http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx">http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx</a>Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com1tag:blogger.com,1999:blog-5449582431202216585.post-67701759029333687152012-09-19T11:46:00.001+02:002012-09-19T11:46:08.922+02:00Optimize your SQL statementsIt is always good practise to optimize your SQL query statements for a better performance. Just making sure your query does what it needs to, isn't enough. You should consider the performance impact of your code and try to optimize it for the best performance. <br />
<br />
Below is a short list of steps to take when optimizing your query statements.<br />
More details on the steps can be found <a href="http://www.sqlservercentral.com/articles/Performance+Tuning/70647/">in this blog</a>. <br />
<br />
<ol>
<li>Check if you're missing any required table joins.<br /><em>This could result in a Cartesian join.</em><br /><br /></li>
<li>Check if you're missing any required WHERE clause to prevent retrieving too much data.<br /><br /></li>
<li>Check if statistics are being created & updated automatically.<br /><br /></li>
<li>Check if the statistics are up-to-date.<br /><em>The </em><a href="http://msdn.microsoft.com/en-us/library/ms174384.aspx"><em>DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled</em></a><em>. </em><br /><em>Outdated statistics can be renewed using the </em><a href="http://msdn.microsoft.com/en-us/library/ms173804.aspx"><em>sp_updatestats</em></a><em> stored procedure, or By using the </em><a href="http://msdn.microsoft.com/en-us/library/ms187348.aspx"><em>FULLSCAN</em></a><em> option to update <u>all</u> statistics of a table.</em><br /><br /></li>
<li>Check for any missing table or Index scans by using the execution plans.<br /><em>you can use these DMV's to check for missing indexes: </em><a href="http://msdn.microsoft.com/en-us/library/ms345434.aspx"><em>sys.dm_db_missing_index_details</em></a><em>, </em><a href="http://msdn.microsoft.com/en-us/library/ms345421.aspx"><em>sys.dm_db_missing_index_group_stats</em></a><em>, </em><a href="http://msdn.microsoft.com/en-us/library/ms345407.aspx"><em>sys.dm_db_missing_index_groups</em></a><em>.</em><br /><br /></li>
<li>Check for <a href="http://technet.microsoft.com/en-us/library/ms190696.aspx">RID Lookups</a>, also by using the execution plans.<br /><em>These cannot always be eliminated, but by making use of </em><a href="http://msdn.microsoft.com/en-us/library/aa964133(SQL.90).aspx"><em>covering indexes</em></a><em>, RID Lookups can be reduced. </em><br /><br /></li>
<li>Check for any sort operator, again by using the execution plans.<br /><em>There are 3 options:</em>
<ul>
<li><em>Modify the underlining tables to create a CLUSTERED index on the required sort columns. It could be worth trying out creating the CLUSTERED index on another column which is not the Primary Key.</em></li>
<li><em>Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.</em></li>
<li><em>Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.</em></li>
</ul><br /><br />
</li>
<li>Check for excessive index fragmentation.<br /><em>The DMV </em><a href="http://msdn.microsoft.com/en-us/library/ms188917.aspx"><em>sys.dm_db_index_physical_stats</em></a><em> can be used for this matter.</em><br /><br /></li>
<li>Check table locks.<br /><em>To prevent locking problems, follow these guidelines:</em>
<ul>
<li><em>Keep transactions as short as possible.</em></li>
<li><em>Review the </em><a href="http://technet.microsoft.com/es-es/library/ms173763.aspx"><em>transaction isolation level</em></a><em>, and consider minimizing locking contention, thus increasing concurrency by changing to 'Read Committed using row versioning' or 'Snapshot'.</em></li>
<li><em>Specify table hints such as </em><a href="http://technet.microsoft.com/en-us/library/ms187373.aspx"><em>READUNCOMMITTED</em></a><em> or </em><a href="http://technet.microsoft.com/en-us/library/ms187373.aspx"><em>READPAST</em></a><em> on the select statements. Although both of these table hints do increase concurrency, both have disadvantages such as 'dirty reads' when using the READUNCOMMITTED or returning an incomplete data set when using the READPAST and therefore they may not be acceptable to use in all circumstances.</em></li>
</ul>
</li>
</ol>
<br />
<br />
Source: <a href="http://www.sqlservercentral.com/articles/Performance+Tuning/70647/">http://www.sqlservercentral.com/articles/Performance+Tuning/70647/</a><br />
<br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-79685240807509029622012-09-19T11:35:00.000+02:002012-09-19T11:35:17.727+02:00Speeding Up Database AccessI found a complete serie on speeding up acces to your SQL Database.<br />
<br />
So far, there are only 4 parts published of an 8 part series of articles on speeding up access to a SQL Server Database.<br />
Here's an overview of the content of all 8 parts (including links of already published parts):<br />
<br />
<ul>
<li><a href="http://www.sqlservercentral.com/articles/Indexing/74510/" target="blank">Part 1 Pinpointing missing indexes and expensive queries</a><br /><ul>
<li>Find expensive queries (with trace log)</li>
<li>Locate missing indexes with the query optimizer (or with Tuning Advisor)</li>
<li>Find unused indexes</li>
</ul>
</li>
<li><a href="http://www.sqlservercentral.com/articles/Performance/74512/" target="blank">Part 2 Pinpointing other bottlenecks</a><br /><ul>
<li>Find Locks and Latches (via counters in perfmon)</li>
<li>Execution plan reusability (using counters or dynamic management views (DMV))</li>
<li>Fragmentation of data and indexes</li>
<li>Memory check (using counters to verify if there’s a lack of memory)</li>
<li>Disk usage (locate possible disk bottlenecks using counters)</li>
<li>CPU (use counters to see if the CPU is stressed)</li>
</ul>
</li>
<li><a href="http://www.sqlservercentral.com/articles/Indexing/74513/" target="blank">Part 3 Fixing missing indexes</a><br /><ul>
<li>Indexes explained (clustered and nonclustered indexes)</li>
<li>Index usage (when / when not)</li>
<li>Maintaining indexes</li>
</ul>
</li>
<li><a href="http://www.sqlservercentral.com/articles/Performance/75152/" target="_blank">Part 4 Fixing expensive queries</a><br /><ul>
<li>Cache aggregation queries</li>
<li>Keep records short</li>
<li>Considering Denormalization</li>
<li>Be careful with triggers</li>
<li>Use table variables for small temporary result sets</li>
<li>Use Full Text Search instead of LIKE</li>
<li>Replacing cursors with set based code</li>
<li>Minimise traffic from SQL Server to Web Server</li>
<li>Object Naming</li>
<li>Use SET NOCOUNT ON</li>
<li>Use FILESTREAM for values over 1MB</li>
<li>Avoid functions on columns in WHERE clauses</li>
<li>Use UNION ALL instead of UNION</li>
<li>Use EXISTS instead of COUNT to find existence of records</li>
<li>Combine SELECT and UPDATE</li>
</ul>
</li>
<li>Part 5 Fixing locking issues</li>
<li>Part 6 Fixing execution plan reuse</li>
<li>Part 7 Fixing fragmentation</li>
<li>Part 8 Fixing memory, disk and CPU issues</li>
</ul>
<br />
I'll update this post when new parts are published.Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-47338109862159313042012-09-05T10:29:00.001+02:002012-09-05T10:29:25.615+02:00Formatting code to HTMLI like to share a website I frequently use to format any code (C#, XML or T-SQL) to HTML for using on my blog. Other possibilities are to format VB, HTML or msh, but I haven't used these myself yet. <br />
You can also add things like line numbers, or use alternating backgrounds. And to modify the colors of the output, you'll just need to change the css style sheet.
<br /><br />
Also because everytime I need it, I'll have to search the website again on google. So this way I'll save some time for me too :).
<br /><br />
Here's the link: <a href="http://www.manoli.net/csharpformat/">http://www.manoli.net/csharpformat/</a>Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-61242938597067735712012-09-05T10:02:00.001+02:002012-09-05T14:53:39.961+02:00Delete BizTalk backup filesThe SQL Server agent job "Backup BizTalk Server" will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.
<br /><br />
Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:
<br />
<OL>
<LI>Open SQL Server management Studio</LI>
<LI>Open a new query window and connect to the BizTalkMgmtDb database</LI>
<LI>Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles<br/>
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">USE</span> [BizTalkMgmtDb]
<span class="kwrd">GO</span>
<span class="kwrd">SET</span> ANSI_NULLS <span class="kwrd">ON</span>
<span class="kwrd">GO</span>
<span class="kwrd">SET</span> QUOTED_IDENTIFIER <span class="kwrd">ON</span>
<span class="kwrd">GO</span>
<span class="kwrd">ALTER</span> <span class="kwrd">PROCEDURE</span> [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep <span class="kwrd">smallint</span> = <span class="kwrd">null</span>, @UseLocalTime <span class="kwrd">bit</span> = 0
<span class="kwrd">AS</span>
<span class="kwrd">BEGIN</span>
<span class="kwrd">set</span> nocount <span class="kwrd">on</span>
<span class="kwrd">IF</span> @DaysToKeep <span class="kwrd">IS</span> <span class="kwrd">NULL</span> <span class="kwrd">OR</span> @DaysToKeep <= 0
<span class="kwrd">RETURN</span>
/*
<span class="kwrd">Only</span> <span class="kwrd">delete</span> <span class="kwrd">full</span> <span class="kwrd">sets</span>
<span class="kwrd">If</span> a <span class="kwrd">set</span> spans a <span class="kwrd">day</span> such that <span class="kwrd">some</span> items fall <span class="kwrd">into</span> the deleted <span class="kwrd">group</span> <span class="kwrd">and</span> the other don<span class="str">'t don'</span>t <span class="kwrd">delete</span> the <span class="kwrd">set</span>
<span class="kwrd">Delete</span> history <span class="kwrd">only</span> <span class="kwrd">if</span> history <span class="kwrd">of</span> <span class="kwrd">full</span> <span class="kwrd">Backup</span> <span class="kwrd">exists</span> <span class="kwrd">at</span> a later point <span class="kwrd">of</span> <span class="kwrd">time</span>
why: history <span class="kwrd">of</span> <span class="kwrd">full</span> <span class="kwrd">backup</span> <span class="kwrd">is</span> used <span class="kwrd">in</span> sp_BackupAllFull_Schedule <span class="kwrd">to</span> <span class="kwrd">check</span> <span class="kwrd">if</span> <span class="kwrd">full</span> <span class="kwrd">backup</span> <span class="kwrd">of</span> databases <span class="kwrd">is</span> required <span class="kwrd">or</span> <span class="kwrd">not</span>.
<span class="kwrd">If</span> history <span class="kwrd">of</span> <span class="kwrd">full</span> <span class="kwrd">backup</span> <span class="kwrd">is</span> <span class="kwrd">not</span> present, job will take a <span class="kwrd">full</span> <span class="kwrd">backup</span> irrespective <span class="kwrd">of</span> other options (frequency, <span class="kwrd">Backup</span> <span class="kwrd">hour</span>)
*/
<span class="kwrd">declare</span> @PurgeDateTime datetime
<span class="kwrd">if</span> (@UseLocalTime = 0)
<span class="kwrd">set</span> @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
<span class="kwrd">else</span>
<span class="kwrd">set</span> @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
<span class="kwrd">DECLARE</span> DeleteBackupFiles <span class="kwrd">CURSOR</span>
<span class="kwrd">FOR</span> <span class="kwrd">SELECT</span> <span class="str">'del "'</span> + [BackupFileLocation] + <span class="kwrd">case</span> <span class="kwrd">right</span>(BackupFileLocation,1) <span class="kwrd">when</span> <span class="str">'\'</span> <span class="kwrd">then</span> <span class="str">''</span> <span class="kwrd">else</span> <span class="str">'\'</span> <span class="kwrd">end</span> + [BackupFileName] + <span class="str">'"'</span> <span class="kwrd">FROM</span> [adm_BackupHistory] [h1]
<span class="kwrd">WHERE</span> [BackupDateTime] < @PurgeDateTime
<span class="kwrd">AND</span> [BackupSetId] <span class="kwrd">NOT</span> <span class="kwrd">IN</span> ( <span class="kwrd">SELECT</span> [BackupSetId] <span class="kwrd">FROM</span> [dbo].[adm_BackupHistory] [h2] <span class="kwrd">WHERE</span> [h2].[BackupSetId] = [h1].[BackupSetId] <span class="kwrd">AND</span> [h2].[BackupDateTime] >= @PurgeDateTime)
<span class="kwrd">AND</span> <span class="kwrd">EXISTS</span>( <span class="kwrd">SELECT</span> <span class="kwrd">TOP</span> 1 1 <span class="kwrd">FROM</span> [dbo].[adm_BackupHistory] [h2] <span class="kwrd">WHERE</span> [h2].[BackupSetId] > [h1].[BackupSetId] <span class="kwrd">AND</span> [h2].[BackupType] = <span class="str">'db'</span>)
<span class="kwrd">DECLARE</span> @cmd <span class="kwrd">varchar</span>(400)
<span class="kwrd">OPEN</span> DeleteBackupFiles
<span class="kwrd">FETCH</span> <span class="kwrd">NEXT</span> <span class="kwrd">FROM</span> DeleteBackupFiles <span class="kwrd">INTO</span> @cmd
<span class="kwrd">WHILE</span> (@@fetch_status <> -1)
<span class="kwrd">BEGIN</span>
<span class="kwrd">IF</span> (@@fetch_status <> -2)
<span class="kwrd">BEGIN</span>
<span class="kwrd">EXEC</span> master.dbo.xp_cmdshell @cmd, NO_OUTPUT
<span class="kwrd">delete</span> <span class="kwrd">from</span> [adm_BackupHistory] <span class="kwrd">WHERE</span> <span class="kwrd">CURRENT</span> <span class="kwrd">OF</span> DeleteBackupFiles
<span class="kwrd">print</span> @cmd
<span class="kwrd">END</span>
<span class="kwrd">FETCH</span> <span class="kwrd">NEXT</span> <span class="kwrd">FROM</span> DeleteBackupFiles <span class="kwrd">INTO</span> @cmd
<span class="kwrd">END</span>
<span class="kwrd">CLOSE</span> DeleteBackupFiles
<span class="kwrd">DEALLOCATE</span> DeleteBackupFiles
<span class="kwrd">END</span>
</pre></LI>
<LI>5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory</LI>
<LI>Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script: <br />
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">EXEC</span> master.dbo.sp_configure <span class="str">'show advanced options'</span>, 1
<span class="kwrd">RECONFIGURE</span>
<span class="kwrd">EXEC</span> master.dbo.sp_configure <span class="str">'xp_cmdshell'</span>, 1
<span class="kwrd">RECONFIGURE</span>
</pre></LI>
</OL>
<br /><br />
This stored procedure is made for BizTalk Server 2010. <br />
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.<br /><br />
For older versions of BizTalk you should use this script instead:
<br />
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">CREATE</span> <span class="kwrd">PROCEDURE</span> [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep <span class="kwrd">smallint</span> = <span class="kwrd">null</span>
<span class="kwrd">AS</span>
<span class="kwrd">BEGIN</span>
<span class="kwrd">set</span> nocount <span class="kwrd">on</span>
<span class="kwrd">IF</span> @DaysToKeep <span class="kwrd">IS</span> <span class="kwrd">NULL</span> <span class="kwrd">OR</span> @DaysToKeep <= 0
<span class="kwrd">RETURN</span>
/*
<span class="kwrd">Only</span> <span class="kwrd">delete</span> <span class="kwrd">full</span> <span class="kwrd">sets</span>
<span class="kwrd">If</span> a <span class="kwrd">set</span> spans a <span class="kwrd">day</span> such that <span class="kwrd">some</span> items fall <span class="kwrd">into</span> the deleted <span class="kwrd">group</span> <span class="kwrd">and</span> the other doesn<span class="str">'t, do not delete the set
*/
DECLARE DeleteBackupFiles CURSOR
FOR SELECT '</span>del "<span class="str">' + [BackupFileLocation] + '</span>\<span class="str">' + [BackupFileName] + '</span>"' <span class="kwrd">FROM</span> [adm_BackupHistory]
<span class="kwrd">WHERE</span> datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
<span class="kwrd">AND</span> [BackupSetId] <span class="kwrd">NOT</span> <span class="kwrd">IN</span> ( <span class="kwrd">SELECT</span> [BackupSetId] <span class="kwrd">FROM</span> [dbo].[adm_BackupHistory] [h2] <span class="kwrd">WHERE</span> [h2].[BackupSetId] = [BackupSetId] <span class="kwrd">AND</span> datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
<span class="kwrd">DECLARE</span> @cmd <span class="kwrd">varchar</span>(400)
<span class="kwrd">OPEN</span> DeleteBackupFiles
<span class="kwrd">FETCH</span> <span class="kwrd">NEXT</span> <span class="kwrd">FROM</span> DeleteBackupFiles <span class="kwrd">INTO</span> @cmd
<span class="kwrd">WHILE</span> (@@fetch_status <> -1)
<span class="kwrd">BEGIN</span>
<span class="kwrd">IF</span> (@@fetch_status <> -2)
<span class="kwrd">BEGIN</span>
<span class="kwrd">EXEC</span> master.dbo.xp_cmdshell @cmd, NO_OUTPUT
<span class="kwrd">delete</span> <span class="kwrd">from</span> [adm_BackupHistory] <span class="kwrd">WHERE</span> <span class="kwrd">CURRENT</span> <span class="kwrd">OF</span> DeleteBackupFiles
<span class="kwrd">print</span> @cmd
<span class="kwrd">END</span>
<span class="kwrd">FETCH</span> <span class="kwrd">NEXT</span> <span class="kwrd">FROM</span> DeleteBackupFiles <span class="kwrd">INTO</span> @cmd
<span class="kwrd">END</span>
<span class="kwrd">CLOSE</span> DeleteBackupFiles
<span class="kwrd">DEALLOCATE</span> DeleteBackupFiles
<span class="kwrd">END</span>
<span class="kwrd">GO</span>
</pre>
<br /><br />
Source: <a href="http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx" target="blank">http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx</a>Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0tag:blogger.com,1999:blog-5449582431202216585.post-52218210709948714762012-08-31T16:19:00.000+02:002012-08-31T16:19:24.641+02:00Using System Colors in C#When choosing a color in the properties window in visual studio, there is the possibility to choose from so called System Colors. An example for this is the color 'Window', which is the default for a textbox.
<br />
Now, I tried to set the BackColor of a Textbox programmatically back to this system color 'Window'. Turned out not to be as easy as I thought it would be.
<br /><br />
I tried things like Color.? ... I hoped to find the color in this namespace, or even a System subsection for the systemcolors. Nothing to be found however.<br />
Nor was there any namespace like System.Color to be found.
<br /><br />
<u><STRONG>But how can these system colors be set from C# code than?</STRONG></u>
<br /><br />
Well, one way turned to be using this command:
<br /><br />
<I><b>System.Drawing.Color.FromKnownColor(KnownColor.Window)</b></I>
<br /><br />
And, as you'd expect, the KnownColor enumeration contains all of the other user-definable colors.
<br /><br />
Or even an easier method to achieve this is
<br /><br />
<I><b>System.Drawing.SystemColors.Window</b></I>
<br /><br />Mitch Vanheldenhttp://www.blogger.com/profile/12284718728803540716noreply@blogger.com0