How to Write a Microsoft Press Release

If you read the SimpleTalk blog (aka RedGate Software), you’ll probably have read Phil Factor’s latest entry ‘Microsoft Boy announces his School Homework‘ which is a piss-take on the standard Microsoft marketing blurb, attempting to imagine how Microsoft Marketing people relate to their fellow men outside work, by giving a glimpse of ‘Microsoft Boy’ during a History Lesson at school:

“The past week has been an amazing time for the me as I geared up to announce the delivery of my essay.. The response to my announcement from friends and parents has been overwhelmingly positive – in fact, even my aunt Edith wants to read it. What is catching users’ eyes? Legibility, correctness, conciseness….the list goes on and on. Simply put, this history essay is a significant release for me – one that builds on all of the great things that I was able to deliver last year in the Lower fifth. I see it as a critical step forward for my academic life here, and the foundation of the broader vision for my school career. Based on what we are hearing from people who have seen the current version of my essay, it seems that everyone agrees.”

How amusing to then read this blog post from the Data Platform Insider, talking about the Microsoft SQL Server 2008 Roadmap Clarification – here are the first two paragraphs:

The past few months have been an amazing time for the SQL Server team as we gear up for the start of the global launch wave on February 27. The response to SQL Server 2008 has been overwhelmingly positive – in fact, we now have more 100,000 downloads of our CTPs. What is catching users’ eyes? Scalability improvements, Resource Governor, Filestream, spatial data support, data compression, policy-based management…the list goes on and on.

Simply put, SQL Server 2008 is a significant release for us – one that builds on all of the great things that we were able to deliver in SQL Server 2005. We see it as a critical step forward for our data platform and the foundation of our broader vision for business intelligence. Based on what we are hearing from customers, as well as the results of the latest benchmarks, it seems the industry agrees.

Notice any similarities? Oh how I laughed.

ps. I passed my PADI Advanced Open Water and Dry Suit qualifications at the weekend in an old disused quarry near Leicester – sorry, the British National Diving Centre – the water was a barmy 4 degrees Celsius! Photos of me in a gimp suit to follow shortly ;-)

Advertisements

An Easier Way to do Complex FOR XML EXPLICIT

I’ve got to confess that I’m not all that good with the FOR XML EXPLICIT syntax to generate Xml straight out of SQL Server – although there are a few good resources on the technology, I don’t spend enough time writing it and just get hopelessly muddled when I have to write anything remotely complex.

So, after posting a plea for help to the SQL Server Xml MSDN Forum I was pleasantly surprised to learn about the new PATH Mode for generating Xml that is available in SQL Server 2005. To quote the MSDN website:

“…the PATH mode provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a rowset, but the PATH mode provides a simpler alternative to the potentially cumbersome EXPLICIT mode queries. PATH mode, together with the ability to write nested FOR XML queries and the TYPE directive to return xml type instances, allows you to write queries with less complexity.”

So, using PATH mode, you can generate Xml along the lines of:

PATH T-SQL Xml OutputWith T-SQL as simple as this:

Xml PATH SQL T-SQLMichael Rys has a couple of good blog posts on the subject here and here – well worth a read.

Further BizTalk & MSDTC Issues

Update 22nd June 2009: I’ve just encountered yet another issue trying to get MSDTC working. The culprit this time was a rogue entry in the hosts file (C:WINDOWSsystem32driversetchosts) which had an incorrect IP address specified for the server I was trying to ‘DTC Ping’.

I encountered yet another MSDTC issue when talking to a remote SQL Server database following a BizTalk 2006 development environment upgrade from Windows Server 2003 Ent. Ed. to Service Pack 1.

The usual ‘Mutual authentication required’ / ‘No Authentication Required’ problem reared its head, and although this needed to be resolved, I was still receiving the ‘New transaction cannot enlist in the specified transaction coordinator‘ error when BizTalk attempted to do anything involving a distributed transaction. Even with a fresh installation and reconfiguration of MSDTC, I still couldn’t successfully run the DTCPing tool – the RPC test ran fine, but the Binding test failed every time:

Unsuccessful DTCPing Test

As discussed in this blog entry by Romualdas MSDTC additionally requires ‘NetBIOS over TCP/IP’ functionality enabled on the network adapter/s participating in the DTC transaction – this setting is disabled by default when using static IP addresses:

Enable NetBIOS over TCP/IP

Because MSDTC uses NetBIOS to resolve remote machine names (rather than DNS Fully Qualified Names which is a little odd reading this O’Reilly article), the DTCPing Binding test was unable to resolve the machine initiating the call and hence the failure of the test. Setting the network adapter to enable NetBIOS over TCP/IP resolved the issue (no restart was required).

Successful DTCPing Test

Generating Microsoft Word Documents Natively using BizTalk 2006

In this post I’ll discuss how to generate Word 2007 documents natively from BizTalk 2006 using the Office Open Xml System.IO.Packaging API recently released by the Microsoft Office Team under .Net 3.0.

Background

Unless you’ve lived under a rock during the last year, you’ll know that the Office Open XML (OOXML) format is the new Xml format for the Office 2007 suite, namely Word, Excel and Powerpoint. OOXML uses a file package conforming to the Open Packaging Convention and contains a number of individual files that form the basis of the document; the package is then zipped to reduce the overall size of the resulting file (either a .docx, .xlsx or .pptx).

Generating Word Documents – Overview

Generating a Word document is relatively simple and only requires a custom send pipeline component that generates our OOXML package.

In this post I will be using a Sales Report scenario, generating a Word document from the output of a fictional ERP system; to that extent, I’ll also be mapping from a fictional sales summary Xml message to the required OOXML format before generating the final .docx. The final document will look something like the following (note that the areas in red will be replaced with content from our ERP sales summary message – click on the image for a larger version):

Proposed Sales Summary Document - SmallBefore we start, I need to present a quick crash-course in the structure of OOXML packages. A minimal OOXML WordprocessingML document contains three parts: a part that defines the main document body, usually called document.xml; a part detailing the Content Types (which indicates to the consumer what type of content can be expected in the package); and a Relationships part (which ties the document parts and Content Types together). When using the System.IO.Packaging API we only need to concern ourselves with the main document body – the API takes care of creating the Content Types and Relationship parts. Its this feature of the API that allows us to create Word documents in BizTalk – all we need to do is create the Xml for the main document and squirt it at a custom pipeline component which does the packaging stuff for us using the API.

Note that the structure of an OOXML document is outside of the scope of this post (but a good understanding is fundamental when working with these documents) and I would recommend that you read the excellent Open Xml Markup Explained by Wouter van Vugt.

Generating Word Documents – The ‘Main’ Document

The main document body (i.e. document.xml) is the only part that is generated in the BizTalk solution. We don’t actually create a file called document.xml – the packaging API does this for us – instead we simply create a message that conforms to the OOXML schema and pass this into the custom Send pipeline.

In our scenario, we are generating a Sales Report document for distribution to the finance department – we will receive an Xml sales summary document from our fictional ERP system that resembles the following:

<?xml version="1.0" encoding="utf-8"?>
<ns0:SalesReport xmlns:ns0="http://schemas.modhul.com/erp/salesreport-1.0">
    <Author>Nick Heppleston</Author>
    <Email>nick@modhul.com</Email>
    <SalesStart>10th January 2008</SalesStart>
    <SalesEnd>17th January 2008</SalesEnd>
    <SalesSummary>100,48.00</SalesSummary>
</ns0:SalesReport>

which needs to be mapped into our OOXML main document body message (I think the layout of the OOXML message is pretty self explanatory, however I would point you at Open Xml Markup Explained if you’re after a more detailed explanation):

<?xml version="1.0″ encoding="utf-8″ ?>
<w:document xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
    <w:body>
        <w:p>
            <w:r>
                <w:rPr>
                    <w:b />
                    <w:sz w:val="52/>"
                        <w:rFonts w:ascii="Cambria" />
                    </w:rPr>
                <w:t xml:space="preserve">Sales Summary for: </w:t>
                <w:t>Nick Heppleston</w:t>
            </w:r>
        </w:p>
        <w:p>
            <w:r>
                <w:rPr>
                    <w:i />
                    <w:sz w:val="52/>"
                        <w:rFonts w:ascii="Cambria" />
                        <w:spacing w:val="15/>"
                        <w:color w:val="48FDB2/>"
                </w:rPr>
                <w:t xml:space="preserve">Sales from: </w:t>
                <w:t>10th January 2008</w:t>
                <w:t xml:space="preserve"> to </w:t>
                <w:t>17th January 2008</w:t>
                <w:t xml:space="preserve"> - </w:t>
                <w:t>£100,48.00</w:t>
            </w:r>
        </w:p>
        <w:p>
            <w:r>
                <w:t xml:space="preserve">Contact: </w:t>
                <w:t>Nick Heppleston</w:t>
                <w:t xml:space="preserve"> | </w:t>
                <w:t>nick@modhul.com</w:t>
            </w:r>
        </w:p>
    </w:body>
</w:document>

This transformation can be performed anywhere: in the sample solution I’ve put the map on the Receive Port. Also, because I can’t think of any way to generate this type of message using a standard BizTalk Map – how do I graphically say ‘map from this source node to this destination node’ when all of the destination nodes simply repeat themselves – I am using custom XSLT to drive the map.

Note: I’ve yet to find a satisfactory XSD for the WordprocessingML markup so the solution contains a OOXML schema that was automagically generated from the above destination format. I’m working on sourcing the schema – I have a number of ‘feelers’ out with the Office Team and I hope to be able to provide a reference in the next couple of days.

With our Sales Summary message now mapped and in the necessary OOXML format, we can send it to the custom pipeline / pipeline component for it to do its work and generate our .docx package.

Generating Word Documents – The Custom Pipeline Component

The custom pipeline component is relatively simple. It uses the System.IO.Packaging API introduced in .Net 3.0 which can be found in windowsbase.dll (C:Program FilesReference AssembliesMicrosoftFrameworkv3.0windowsbase.dll); full documentation regarding this namespace can be found online at MSDN. The API is invoked in the pipeline component Execute() method as follows:

   1:  public IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
   2:  {
   3:      XmlDocument InputXmlDocument = new XmlDocument();
   4:      InputXmlDocument.XmlResolver = null;
   5:  
   6:      // Define bodypart instances
   7:      IBaseMessagePart bodyPart = inmsg.BodyPart;
   8:  
   9:      // Define stream instances
  10:      Stream originalStream = null;
  11:      MemoryStream odfStream = new MemoryStream();
  12:  
  13:      string docContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml";
  14:      string docRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";
  15:  
  16:      if (null != bodyPart)
  17:      {
  18:          // Get a *copy* of the original stream
  19:          originalStream = bodyPart.Data;
  20:  
  21:          // Check that the original stream is not null
  22:          if (null != originalStream)
  23:          {
  24:              // Load the original message stream into our input xml document 
  25:              // to be used as the basis of the OOXML document.
  26:              InputXmlDocument.Load(originalStream);
  27:  
  28:              try
  29:              {
  30:                  // Create a new OOXML package
  31:                  Package pkg = Package.Open(odfStream, FileMode.Create, FileAccess.ReadWrite);
  32:  
  33:                  // Create a Uri for the document part
  34:                  Uri docPartUri = new Uri("/word/document.xml", UriKind.Relative);
  35:  
  36:                  // Create the document part
  37:                  PackagePart mainPart = pkg.CreatePart(docPartUri, docContentType);
  38:  
  39:                  // Add the data from the Xml Document to the document part
  40:                  Stream partStream = mainPart.GetStream(FileMode.Create, FileAccess.Write);
  41:                  InputXmlDocument.Save(partStream);
  42:                  partStream.Close();
  43:                  pkg.Flush();
  44:  
  45:                  // Create the relationship between the part and the package.
  46:                  PackageRelationship pkgRelationship = pkg.CreateRelationship(docPartUri, TargetMode.Internal, docRelationshipType, "rId1");
  47:  
  48:                  // Flush the changes then close the package
  49:                  pkg.Flush();
  50:                  pkg.Close();
  51:              }
  52:              catch (Exception Ex)
  53:              {
  54:                  EventLog.WriteEntry("BizTalk 2006 - Build ODF Package", "Error encountered building the package: " + Ex.Message, EventLogEntryType.Error);
  55:              }
  56:  
  57:              try
  58:              {
  59:                  // Rewind the new OOXML stream
  60:                  odfStream.Seek(0, System.IO.SeekOrigin.Begin);
  61:              }
  62:              catch (Exception Ex)
  63:              {
  64:                  EventLog.WriteEntry("BizTalk 2006 - Build ODF Package", "Error encountered rewinding the stream: " + Ex.Message, EventLogEntryType.Error);
  65:              }
  66:              finally
  67:              {
  68:                  // Add the new OOXML stream into the return message.
  69:                  bodyPart.Data = odfStream;
  70:                  pc.ResourceTracker.AddResource(odfStream);
  71:              }
  72:          }
  73:      }
  74:  
  75:      return inmsg;
  76:  }

A quick overview of the code is as follows:

  • Line 26: We load a copy of the original message data part stream into an XmlDocument to use as the main document body (the document.xml) when building the package.
  • Line 31: Create a new OOXML package in a new MemoryStream.
  • Line 34: Create a URI to the main document body (calling it document.xml).
  • Line 37: Create the main document body part (docPartUri and docContentType.
  • Lines 40 – 43: Save the contents of our BizTalk message to the main document body part (the message we created in the BizTalk map).
  • Line 46: Create a package relationship for the main document body part.
  • Line 60 & 69 – 70: Rewind the the MemoryStream and overwrite the original message with our new OOXML package.
  • Line 75: We return the message containing the OOXML package.

The final message is sent via the FILE adapter and written to the file system. The end result looks like this (click on the image for a larger version):

Finished Sales Summary Document - SmallThe complete solution – containing the pipeline component and a BizTalk proof of concept project – is available to download and can be found archived in the downloads area of this blog. Grab a copy, try it out for yourself; comments and suggestions are welcome.

Conclusion

In this post I hope I’ve shown you the tools necessary to generate Word 2007 documents natively using BizTalk 2006. The example I presented is extremely simple and does not include styles, themes, images, headers and footers, font tables etc. that would exist in a real-life document, but I hope it has presented a starting-point for your own custom development.

These same techniques can also be applied to create Excel spreadsheets or PowerPoint presentations – in fact, while writing this post I have had a number of ideas for enhancements to the pipeline component and will endeavour to create a CodePlex project if I can find the time.

Disclaimer

This work is licensed under a Creative Commons Attribution 2.5 License – you can use commercially and modify as necessary, but you must give the original author credit. Furthermore, sample projects and code are provided “AS IS” with no warranty.Click the image below to view further detail of the licence.

Creative Commons License

Renaming a SQL Server Instance

I know that this script is ubiquitous across the ‘net, but whenever I google for it I come up with elaborate stored proc’s that are overkill for my needs – so here are the commands necessary to rename a SQL Server instance, for posterity:

— Get the current name of the SQL Server instance for later comparison.
SELECT @@servername

— Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]’

— Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]’, ‘local’

— Get the new name of the SQL Server instance for comparison.
SELECT @@servername

And a few notes relating to their usage:

  • sp_dropserver: 1. this stored procedure can remove both remote and linked servers; 2. using the droplogins parameter indicates that related remote and linked server logins for [SERVER NAME] must also be removed. More info.
  • sp_addserver: 1. to define a linked server, use sp_addlinkedserver (as this functionality will be depreciated in version above SQL Server 2005); 2. sp_addserver cannot be used inside a user-defined transaction. More info.

I would also recommend the following when renaming a SQL Server:

  • If you are performing this task as part of a machine rename, rename the machine first then rename the SQL Server.
  • Once you have renamed the SQL Server, do a full restart of SQLServer, including any dependant services.
Reblog this post [with Zemanta]

Truncating the BizTalk 2004 Tracking Database

This post relates to truncating the BizTalk 2004 Tracking Databases, for BizTalk 2006, see http://www.modhul.com/2008/04/10/truncating-the-biztalk-2006-tracking-database/

Update: I’ve had a few questions as to why the views are dropped: these views use the SCHEMABINDING option – creating a view with SCHEMABINDING locks the tables being referred by the view and prevents any changes that may change the table schema, it also appears to stop the tables from being truncated. Hence the views are dropped, the tables truncated and the views re-created. Note: This functionality only appears to be the case in SQL Server 2000, 2005 does not require the views to be dropped.

I’ve noticed that the link I posted to Mike Holdorf’s blog back on my
Host Tracking and the BizTalkMsgBoxDb post no longer works – it would appear that Mikes domain no longer exists.

For posteriety (and because I’ve just had a customer ask me how to do this and I couldn’t remember), here is the script in full:

use BizTalkDTADb
go

— Drop the Views (before you perform this, ensure you take copies of these views!)
Drop View dtav_ServiceFacts
Go
Drop View dtav_MessageFacts
Go
Drop View dtav_FindMessageFacts
Go

— Truncate the necessary Tables
Truncate Table dta_ServiceInstances
Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_DebugTrace
Truncate Table dta_CallChain
Truncate Table dta_MessageInstances
Truncate Table dta_MessageInOutEvents
Truncate Table dta_MessageFieldValues
Truncate Table dta_MessageFields

— Recreate the dropped views from your own environment.

Reblog this post [with Zemanta]