I have a SQL/Trace, and it’s a requirement to each night delete all rows in the Trace over x days old. This is typically called a purge program, or mass delete. If you don’t limit the data, then you can knock the lights out of SQL.

Below is a modification to code I found here: http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact. That blog does a good job of explaining why we need to break the deletes into smaller chunks.

I added the “Wait” statement, which is optional. If you have other jobs running that might be impacted by your delete, you don’t want to block them. So you simply pause, give them a chance to get in and out, then continue with the deletes.

-- Technique to not cause SQL slowness by deleting large number of rows (such as millions of rows) 
-- at one time (without freeing locks) 
-- from http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact


declare @DaysToKeep int = 5
declare @RowsDeleted int = 1  -- must set > 0 so the loop will be entered the first time through 
declare @TotalRowsDeleted int = 0 
declare @StartDateTime datetime = DATEADD(d, -@DaysToKeep, getdate())

WHILE @RowsDeleted > 0
  BEGIN
         DELETE TOP (1000) FROM Trace where  trcDateTime <= @StartDateTime 
         set @RowsDeleted = @@ROWCOUNT 
         print @RowsDeleted
         set @TotalRowsDeleted = @TotalRowsDeleted + @RowsDeleted 
         WAITFOR DELAY '00:00:01';  -- Give SQL a little break 
  END 
SELECT @StartDateTime as StartDateTimeOfDelete, 
       @TotalRowsDeleted as RowsDeleted, (select COUNT(*) from Trace) as RemainingRows, 
       MIN(trcDateTime) as Min, MAX(trcDateTime) as Max from Trace  

--select count(*) from trace where trcDateTime >= DATEADD(d, -15, getdate())
--declare @DaysToKeep int = 20
--select DATEADD(d, -@DaysToKeep, getdate())

I was working with an old third party tool that only supported VBScript for programming.  I needed to email a file if the file had more than one row.  All the CSV (Comma Separated Value) files created had the CSV headers on the first line. Basically, this was an error/exception report, so I only wanted to send an email if there was more than one line in the file. The following basically counts the number of Carriage-Return/Line-Feed characters.

set fso=CreateObject("Scripting.FileSystemObject")

fa = "E:\MESSAGES\ACER\PROD\Batch\Out\database_failed_POs_20150916_140035.csv"
countRows = CountRowsInFile(fa)
msgbox "File with Error(s) Only countRows=" &amp; countRows 


fa = "E:\MESSAGES\ACER\PROD\Batch\Out\database_failed_POs_20150916_150009.csv"
countRows = CountRowsInFile(fa)
msgbox "Header Only countRows=" &amp; countRows 

Function CountRowsInFile(filename)
	Dim oFso, oReg, sData, lCount
	Const ForReading = 1
	Set oReg = New RegExp
	sData = Fso.OpenTextFile(filename, ForReading).ReadAll
	With oReg
	    .Global = True
	    .Pattern = "\r\n" 'vbCrLf
	    '.Pattern = "\n" ' vbLf, UTF-8 encoded text file?
	    lCount = .Execute(sData).Count 
	End With
	Set oReg = Nothing
	CountRowsInFile = lCount 
End Function 

 

Above was modeled after post on StackOverflow (basically turned it into a Function).

Below is just a code fragment that creates a date useful for putting on a filename. It’s not particular elegant, and there are other ways to do the same thing (for example using the PAD function). But it works and it’s easy to read and change.

dtYear  = year(date) 
dtMonth = Right(String(2,"0") &amp; Month(date), 2)
dtDay   = Right(String(2,"0") &amp; Day(date), 2)
dtHour  = Right(String(2,"0") &amp; Hour(now()), 2)
dtMin   = Right(String(2,"0") &amp; Minute(now()), 2)
dtSec   = Right(String(2,"0") &amp; Second(now()), 2)
dtYYYYMMDDHHMM = dtYear &amp; dtMonth &amp; dtDay &amp; dtHour &amp; dtMin 
dtYYYYMMDDHHMM2 = dtYear &amp; "_" &amp; dtMonth &amp; "_" &amp; dtDay &amp; "_" &amp; dtHour &amp; "_" &amp;  dtMin 
dtYYYYMMDDHHMMSS = dtYear &amp; dtMonth &amp; dtDay &amp; dtHour &amp; dtMin &amp; dtSec
dtYYYYMMDDHHMMSS2 = dtYear &amp; dtMonth &amp; dtDay &amp; "_" &amp; dtHour &amp; dtMin &amp; dtSec

'example of how the above can be used to insert a date in a filename.
DTSOutFilenameWithDate = replace(DTSOutFilename, "_.csv", "_" &amp; dtYYYYMMDDHHMM  &amp; ".csv")

See also related VBScript blog: Deleting files in a folder over x days old (in VBScript)

Yes, it’s the year 2015, and I still find myself creating VBScripts from time to time. My client has an old server that uses a third party tool to do database extracts, and it supports only VBScript. So we have scripts that email CSV (Comma Separated Value) extracts, and/or copy them to various folders on different servers (or to FTP sites).

I wanted to make sure a script cleaned-up old files that were created, including Trace files. Normally you want a retention period of x days. So I created a function that takes three parms:

  1. Days to delete (if older than)
  2. Folder Path
  3. File Mask (only delete files containing this string)
'
'
' Neal Walters  09/17/2015 
'
'

countFilesDeleted = DeleteFiles(7,"E:\MESSAGES\Folder1","PlanITROI_Hourly_Order_Status_") 
WScript.Echo("Count of Files Deleted = " &amp; countFilesDeleted) 
countFilesDelested = DeleteFiles(21,"E:\MESSAGES\Folder2\VBScriptsTrace","Trace") 
WScript.Echo("Count of Files Deleted = " &amp; countFilesDeleted) 

Function DeleteFiles(Days,FolderName,FileMask) 
   set fso2 = CreateObject("Scripting.FileSystemObject")
   set f = fso2.GetFolder(FolderName) 
   set fc = f.Files 
   countDeletes = 0 
   for each f1 in fc 
      if DateDiff("d", f1.DateCreated, Date) &gt; Days Then
	     if instr(f1.Name,FileMask) &gt; 0 then 
			WScript.Echo "Deleting File: " &amp; f1.Name 
            fso2.DeleteFile(f1)
			countDeletes = countDeletes + 1 
		 end if 
      end if
	next
    Set fso2 = Nothing 	
	DeleteFiles = countDeletes 
End Function 

 

Compare to Powershell cleanup/delete files script.

We all hate to use Atomic Scopes in BizTalk orchestrations when not needed.  I have a static C# Trace routine that normally takes passing an enum of System.Diagnostics.TraceLevel (for Error, Warning, Info, etc…).   In the current project, our web services can return “soft errors” in a common field called “Result”.

So I could have used a Decide shape, but since the code repeats on many webservices, I wanted to us an if/else statement in an expression shape called “GetResults” (in picture below).

if (msgResponse.RESULT == "Ok")
  {
     vTraceLevel = System.Diagnostics.TraceLevel.Info;
  }
else   
  {
     vTraceLevel = System.Diagnostics.TraceLevel.Error;
  }
if (msgResponse.RESULT != "Ok")
{
  strSoapFaultMessage = "2510:msgResponse:" + msgResponse.Message;
  throw new System.ApplicationException(strSoapFaultMessage); 
}

The reason I coded like this is that I had a large orchestration with about 8 web service calls, and I wanted a common error handler for all of them. That Catches a System.ApplicationException, writes to EventLog, and sends an email with the full Request and Response XML of the call/response to/from the web service.

Now all this happens to be in a debatching loop. When I ran it, and had multiple errors, the first one had a blank response in the email, and the second and third ones worked. It seems very inconsistent, but here’s what a colleague and I figured out.

In the picture below, my original shapes are on the left, and the revision is on the right. Scope_C_NonSerializable_TraceLevel was an Atomic Transaction to support the fact that TraceLevel was non-serializeable. So if the orchestration blows up in this Scope, it would have to restart at the beginning of the scope.

My mistake was to include the “Throw” in that scope. Apparently the “Throw” sometimes acts as sort of a Rollback, and clears some or all variables set in that Scope.

In the shape called “TRC 2510”, I had the following, along with a call to my C# trace routine.

xmlDocResp = msgAsurionOrderRoutingResponse; 

So when the Throw happens, at least on the first time through the loop, the xmlDocResp had no value. Simply moving the “Throw” and “Trc 2511” outside the loop seemed to solve the issue.

Orchestration_Scope_Throw_Issue

The Catch you see above is the Captch of the BTS.soap_envelope_1__1.Fault.  It also sets xmlDocResp to the value of the SoapFault, and throws an ApplicationExcpetion so that same error handler can work for both Soap Faults and the “soft” web service errors.

The common error handling had the following in a message assignment shape:

tempStrBuilder.AppendLine("===========================");
tempStrBuilder.AppendLine("Request Message:");
tempStrBuilder.AppendLine(System.Convert.ToString(xmlDocReq.OuterXml));
tempStrBuilder.AppendLine("===========================");
tempStrBuilder.AppendLine("Response Message:");
tempStrBuilder.AppendLine(System.Convert.ToString(xmlDocResp.OuterXml));
tempStrBuilder.AppendLine("===========================");

I’m considering adding a C# formatter to do a “pretty print” on the XML (done) to make the error email look even nicer, probably based on the code on this Stackoverflow page.

Here are some helps for this error that I got today:
Error: a non transactional scope 'unnamed' may not contain a transactional scope 'Transaction_3"

When you click on the error in the Visual Studio Error list, it should take you to one of the scopes involved in the error.

In my case, I had a Scope’s Transaction Type set to “None”, and I needed to change it to “Long Running”.  Inside that Scope, I had a small Atomic scope, that had to be used to have an instances of a class that was not serializeable.

One thing you can do is to make debugging easier is to label your scopes, and manually set the “Transaction Identifier”.

For example: Scope_A_Call_WebService_MyMethod1, then set the “Transaction Identifier” to Transaction_A.
Then when you get the error, you can easily tell which Transaction ties to which Scope.

The scope will be “unnamed” if you set “Transaction Type” to “None”.

You can use the Orchestration Viewer to fairly easily see how the Scopes are nested.

 

 

 

I created this code to generate a unique Nonce code. This relates back to this post about BizTalk calling a webservice with Basic Authenticiation.

Message Assignment is prior blog was updated to call C# business component:

xmlDocSoapHeader.LoadXml(
   "<headers>" + 
   "<wsse:Security mustUnderstand=\"1\" xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\" xmlns:wsu=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd\">" + 
   "<wsse:UsernameToken wsu:Id=\"UsernameToken-E4E3CB1F68472DCF2914369675811859\">" +   
   "  <wsse:Username>" + strServiceBenchUserID + "</wsse:Username>" + 
   "  <wsse:Password Type=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText\">" + strServiceBenchPasswordFromSSO + "</wsse:Password>" + 
   "  <wsse:Nonce EncodingType=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary\">" + Asurion.BusinessCompnents.getNonce() + "</wsse:Nonce>" +
   "  <wsu:Created>" + System.DateTime.UtcNow.ToString("s") + "Z" + "</wsu:Created>" + 
   "</wsse:UsernameToken>" +
   "</wsse:Security>" + 
   "</headers>"  
);

msgListNewCallRequest(WCF.OutboundCustomHeaders) = xmlDocSoapHeader.OuterXml;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Threading;</code>

namespace Asurion
{
public static class BusinessCompnents
{

public static string getNonce()
{
// http://stackoverflow.com/questions/896901/wcf-adding-nonce-to-usernametoken
// Nonce explained here: http://weblog.west-wind.com/posts/2012/Nov/24/WCF-WSSecurity-and-WSE-Nonce-Authentication
// quote: Why is there a nonce? My first thought here was WTF? The username and password are there in clear text, what does the Nonce accomplish?
// The Nonce and created keys are are part of WSE Security specification and are meant to allow the server to detect and prevent replay attacks.
// The hashed nonce should be unique per request which the server can store and check for before running another request thus ensuring that
// a request is not replayed with exactly the same values.
//Random r = new Random();
Random r = RandomProvider.GetThreadRandom(); // better to get unique random number if called mulitple times
DateTime created = DateTime.Now;
string nonce = Convert.ToBase64String(Encoding.ASCII.GetBytes(SHA1Encrypt(created + r.Next().ToString())));
return nonce;
}

public static String SHA1Encrypt(String phrase)
{
UTF8Encoding encoder = new UTF8Encoding();
SHA1CryptoServiceProvider sha1Hasher = new SHA1CryptoServiceProvider();
byte[] hashedDataBytes = sha1Hasher.ComputeHash(encoder.GetBytes(phrase));
return ByteArrayToString(hashedDataBytes);
}

public static String ByteArrayToString(byte[] inputArray)
{
StringBuilder output = new StringBuilder("");
for (int i = 0; i &lt; inputArray.Length; i++)
{
output.Append(inputArray[i].ToString("X2"));
}
return output.ToString();
}

} // end class

public static class RandomProvider
{
// http://csharpindepth.com/Articles/Chapter12/Random.aspx
private static int seed = Environment.TickCount;

private static ThreadLocal randomWrapper = new ThreadLocal(() =&gt;
new Random(Interlocked.Increment(ref seed))
);

public static Random GetThreadRandom()
{
return randomWrapper.Value;
}

} // end class
}

Have you been looking for how to find the string text specified in the BizTalk Terminate Shape?  I hadn’t used this shape in a long long time, and had to check it out again this week.  This blog show you you where the Terminate Shape text goes.

After your orchestration has hit a Terminate statement, you can do the following two steps:

1. In BizTalk Admin, go to the group hub, and click on “Terminated Instances”.

Terminate_TerminatedInstances

2. Find the terminated instance of your orchestration.

Terminate_TerminateMessageText

The text does NOT appear in the application EventLog. You would have to do a System.Diagnostics.Write.EventLog in an “Expression Shape” to do that.

For example:


System.Diagnostics.EventLog.WriteEntry(
vEventSource,
vMessageStr,
System.Diagnostics.EventLogEntryType.Error);

The orchestration is terminated, not suspended, so there is nothing to be seen in the “Suspended” query.

In this blog by Yossi Dahan, he talks about how there should perhaps be an “End” shape that doesn’t imply a termination/error.

By the way, even thow there is a “Throw” shape in BizTalk Orchestrations, there’s nothing to stop you from doing a .NET “Throw” in an “Expression Shape”, for example:


if (vCO_HeaderUpdateResult != "Ok")
{
strSoapFaultMessage = "2240:msgCO_Header_Update_Response:" + vCO_HeaderUpdateMessage;
throw new System.ApplicationException(strSoapFaultMessage);
}

In the program above, I caught a “soft error”, i.e. the I called a web service that completed without a hard Soap error, but it set an error in the variables that it returned. Thus, I raised an error via the “Throw” keyword, then caught the error in the Exception Handler of a scope. This allowed me to have one common error handling routine for the 10 different web services that I was calling in the one orchestration. That common routine wrote to the EventLog, Sends and Email via an email send port, writes to a Trace, and then does ends the orchestration, probably via the Terminate statement (still need to discuss with my colleagues).