Rudolf Henning

One of the real limitations of BizTalk server (design issue) is the lack of details for when things go wrong - and lets face it there will always be things outside the control of the product, not even mentioning bugs in the product itself. For example, when dealing with plain files (flat files or xml) something that is critical to some people gets lost - the original filename. It is possible to retrieve it through the context of the message inside BizTalk but it has to be done one by one per message. This piece of information is not logged in the eventlog - which is really weird! Sometimes files fail in batches and then it becomes a real plain to retrieve filenames one by one. For that reason I created a simple tool to retrieve the filenames of suspended files in bulk.

The solution is a bit of a hack as it requires direct access to the BizTalk messagebox database. The tricky bit is to retrieve the filename from the message context as it is encoded in some propriety binary format. Through trial and error I decoded this context format and is now able to retrieve some of the properties inside it. Of course, there is no guarentee that MS will not change the format in the future.

Since suspended entries are stored by Host it is tricky to simply have one select statement to gather all suspended files at once. Therefore it has to be done host by host. This means at least 2 select statements have to be executed to retrieve the data.

The first step to retrieve the suspended queue (table) names are easy:

private StringCollection GetHostInstanceSuspendedQs()
        {
            string sql = "select name from sysobjects where name like '%[_]Suspended'";
            StringCollection queues = new StringCollection();
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmnd = new SqlCommand(sql, conn))
                {
                    SqlDataReader dr = cmnd.ExecuteReader();
                    while (dr.Read())
                    {
                        string queue = dr[0].ToString();
                        queues.Add(queue);
                    }
                }
            }
            return queues;
        }

Once you have a list of the suspended queues you can query the rest of the tables including the suspended queue in question:

private List<SuspendedEntry> GetSuspendedInstances(string hostInstanceSuspendedQ)
        {
            List<SuspendedEntry> list = new List<SuspendedEntry>();
            string hostName = hostInstanceSuspendedQ.Substring(0, hostInstanceSuspendedQ.Length - 11);
            string sql = "select b.nvcAdditionalInfo, s.imgContext, b.dtLastTouched, b.uidInstanceID, b.uidMessageID, " +
                    "s.PublishingServer, i.nvcErrorID, m.nvcName as [App], '%2' as [Host] " +
                    " from %1 b inner Join Spool s on b.uidMessageID = s.[uidMessageID ] inner join InstancesSuspended i on b.uidInstanceID = i.uidInstanceID inner join Services se on se.uidServiceID = b.uidServiceID inner join Modules m on m.nModuleID = se.nModuleID" +
                    " where (i.nState = 4) AND (i.nvcAdapter = 'FILE')";
            sql = sql.Replace("%1", hostInstanceSuspendedQ);
            sql = sql.Replace("%2", hostName);

            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmnd = new SqlCommand(sql, conn))
                {
                    cmnd.CommandTimeout = 300;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmnd))
                        da.Fill(ds);
                }
            }
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                SuspendedEntry entry = new SuspendedEntry();
                entry.InstanceId = row["uidInstanceID"].ToString();
                entry.MessageId = row["uidMessageID"].ToString();
                entry.LastTouchDate = DateTime.Parse(row["dtLastTouched"].ToString());

                byte[] bytes = (byte[])row["imgContext"];
                int receiveFileNameStart = SearchBytes(bytes, String2Bytes("ReceivedFileName"));
                receiveFileNameStart = receiveFileNameStart + 41;
                int receiveFileNameLen = (bytes[receiveFileNameStart] * 256) + bytes[receiveFileNameStart + 1];
                byte[] fileNameBytes = CopyBytes(bytes, receiveFileNameStart + 4, receiveFileNameLen);
                entry.FileName = Bytes2String(fileNameBytes);
                entry.AdditionalInfo = row["nvcAdditionalInfo"].ToString();
                entry.PublishingServer = row["PublishingServer"].ToString();
                entry.ErrorID = row["nvcErrorID"].ToString();
                entry.AppName = row["App"].ToString();
                entry.Host = row["Host"].ToString();
                list.Add(entry);
            }

            return list;
        }

 There are a couple of helper functions to do byte and string conversions. I'll include them in the attached zip file.

The result of all this is a nice list you can use to display in a tool or on a web page. This works only for files, a.k.a. messages that was received through the file adapter.

The ideal would have been that the filename was logged in the event log as soon as it was suspended. This is a crucial bit of information that is usually needed by the people monitoring the whole system (including BizTalk).