BizTalk: using macros in file names

BizTalk allows you to use several macros for defining a file name in a biztalk send port.

The following table lists the supported macros and describes how the File send handler replaces them.

Macro name	Substitute value
%datetime%	Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).
%datetime_bts2000%	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).
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
%SourceFileName%	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 FILE.ReceivedFileName 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%). Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourceParty%	Name of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%time%	UTC time in the format hhmmss.
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

NOTE

The File send handler does not replace the macros with a value if any of the following are true:

• The corresponding system property is not set.
• The macro is misspelled.
• The value for the macro contains symbols that are not valid in the file name.

Using Macros in SMTP Headers

There are however some restrictions when using the macros in SMTP headers.
See this post for more details on this matter.

Below is a short overview of the use of macros in SMTP headers.

Macro	Description	For use with To	For use with CC	For use with Subject
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property BTS.MessageID.	No	No	Yes
%datetime_bts2000%	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).	No	No	Yes
%datetime%	UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).	No	No	Yes
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).	No	No	Yes
%time%	UTC time in the format hhmmss.	No	No	Yes
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).	No	No	Yes
%SourceParty%	Name of the source party from which the File adapter received the message.	No	No	Yes
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message.	No	No	Yes
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.	Yes	Yes	Yes
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.	No	No	Yes

Source: http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx

Optimize your SQL statements

It 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.

Below is a short list of steps to take when optimizing your query statements.
More details on the steps can be found in this blog.

1. Check if you're missing any required table joins.
   This could result in a Cartesian join.
   
   
2. Check if you're missing any required WHERE clause to prevent retrieving too much data.
   
   
3. Check if statistics are being created & updated automatically.
   
   
4. Check if the statistics are up-to-date.
   The DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled.
   Outdated statistics can be renewed using  the sp_updatestats stored procedure, or By using the FULLSCAN option to update all statistics of a table.
   
   
5. Check for any missing table or Index scans by using the execution plans.
   you can use these DMV's to check for missing indexes: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups.
   
   
6. Check for RID Lookups, also by using the execution plans.
   These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced.
   
   
7. Check for any sort operator, again by using the execution plans.
   There are 3 options:
   • 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.
   • Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.
   • Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.
   
   
   
8. Check for excessive index fragmentation.
   The DMV sys.dm_db_index_physical_stats can be used for this matter.
   
   
9. Check table locks.
   To prevent locking problems, follow these guidelines:
   • Keep transactions as short as possible.
   • Review the transaction isolation level, and consider minimizing locking contention, thus increasing concurrency by changing to 'Read Committed using row versioning' or 'Snapshot'.
   • Specify table hints such as READUNCOMMITTED or READPAST 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.

Source: http://www.sqlservercentral.com/articles/Performance+Tuning/70647/

Speeding Up Database Access

I found a complete serie on speeding up acces to your SQL Database.

So far, there are only 4 parts published of an 8 part series of articles on speeding up access to a SQL Server Database.
Here's an overview of the content of all 8 parts (including links of already published parts):

• Part 1 Pinpointing missing indexes and expensive queries
  
  • Find expensive queries (with trace log)
  • Locate missing indexes with the query optimizer (or with Tuning Advisor)
  • Find unused indexes
• Part 2 Pinpointing other bottlenecks
  
  • Find Locks and Latches (via counters in perfmon)
  • Execution plan reusability (using counters or dynamic management views (DMV))
  • Fragmentation of data and indexes
  • Memory check (using counters to verify if there’s a lack of memory)
  • Disk usage (locate possible disk bottlenecks using counters)
  • CPU (use counters to see if the CPU is stressed)
• Part 3 Fixing missing indexes
  
  • Indexes explained (clustered and nonclustered indexes)
  • Index usage (when / when not)
  • Maintaining indexes
• Part 4 Fixing expensive queries
  
  • Cache aggregation queries
  • Keep records short
  • Considering Denormalization
  • Be careful with triggers
  • Use table variables for small temporary result sets
  • Use Full Text Search instead of LIKE
  • Replacing cursors with set based code
  • Minimise traffic from SQL Server to Web Server
  • Object Naming
  • Use SET NOCOUNT ON
  • Use FILESTREAM for values over 1MB
  • Avoid functions on columns in WHERE clauses
  • Use UNION ALL instead of UNION
  • Use EXISTS instead of COUNT to find existence of records
  • Combine SELECT and UPDATE
• Part 5 Fixing locking issues
• Part 6 Fixing execution plan reuse
• Part 7 Fixing fragmentation
• Part 8 Fixing memory, disk and CPU issues

I'll update this post when new parts are published.

Formatting code to HTML

I 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.
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.

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 :).

Here's the link: http://www.manoli.net/csharpformat/

Delete BizTalk backup files

The 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.

Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:

1. Open SQL Server management Studio
2. Open a new query window and connect to the BizTalkMgmtDb database
3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
   

   USE [BizTalkMgmtDb]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
   
   AS
    BEGIN
   
    set nocount on
   
     IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
     RETURN
   
        /*
           Only delete full sets
           If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
   
           Delete history only if history of full Backup exists at a later point of time
           why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
           If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
       
       */
           
     declare @PurgeDateTime datetime
   
     if (@UseLocalTime = 0)
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
     else
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
   
     DECLARE DeleteBackupFiles CURSOR
   
     FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
       WHERE     [BackupDateTime] < @PurgeDateTime
       AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
       AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
   
     DECLARE @cmd varchar(400)
   
     OPEN DeleteBackupFiles
   
      FETCH NEXT FROM DeleteBackupFiles INTO @cmd
   
     WHILE (@@fetch_status <> -1)
     BEGIN
               IF (@@fetch_status <> -2)
               BEGIN
   
                           EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
   
                           delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
   
                           print @cmd
               END
   
               FETCH NEXT FROM DeleteBackupFiles INTO @cmd
     END
   
     CLOSE DeleteBackupFiles
   
     DEALLOCATE DeleteBackupFiles
   
     END
   

4. 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
5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
   

   EXEC master.dbo.sp_configure 'show advanced options', 1
   
   RECONFIGURE
   
   EXEC master.dbo.sp_configure 'xp_cmdshell', 1
   
   RECONFIGURE
   
   

This stored procedure is made for BizTalk Server 2010.
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.

For older versions of BizTalk you should use this script instead:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO

Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx

Using 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.
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.

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.
Nor was there any namespace like System.Color to be found.

But how can these system colors be set from C# code than?

Well, one way turned to be using this command:

System.Drawing.Color.FromKnownColor(KnownColor.Window)

And, as you'd expect, the KnownColor enumeration contains all of the other user-definable colors.

Or even an easier method to achieve this is

System.Drawing.SystemColors.Window

SQL Fragmentation explained

Found an interesting article on fragmentation in SQL.

It explains the two types of fragmentation that can occur, namely internal and external fragmentation.
Internal fragmentation means that the pages are not completely full. While external fragmentation refers to the lack of correlation between the logical sequence of an index and its physical sequence.

For more details I'll refer to this excellent blog post.

Source: Stairway to SQL Server Indexes: Level 11, Index Fragmentation - SQLServerCentral