<code>
' This script can be used to automate the creation of Visual Studio .NET 2003/2005 External Tools.
' Scott Colestock - www.traceofthought.net

Option Explicit
On Error Resume Next

Dim wshShell
Set wshShell = WScript.CreateObject("WScript.Shell")

dim nantLocation,buildFile
nantLocation = "%ProgramFiles%nantbinnant.exe"
buildFile = "/f:""$(SolutionDir)$(SolutionFileName).deploy.build"" "

Dim toolKey

toolKey = "HKEY_CURRENT_USERSoftwareMicrosoftVisualStudio8.0External Tools"

call Common
call AddTool(wshShell,"BT - &amp;HAT","%ProgramFiles%Microsoft  BizTalk  Server 2006BTSHatApp.exe","",17)
call AddTool(wshShell,"&amp;Gac This","%ProgramFiles%Microsoft Visual Studio 8SDKv2.0Bingacutil.exe","/i $(TargetPath) /f",26)

MsgBox "External tools have  been added for BizTalk.   Enjoy!"

sub Common()

  call AddTool(wshShell,"BT - B&amp;izTalk Deploy",nantLocation,buildFile+"debugDeploy",26)
  call AddTool(wshShell,"BT - BizTalk &amp;UnDeploy",nantLocation,buildFile+"debugUndeploy",26)
  call AddTool(wshShell,"BT - Update Orchs/Comps/SSO",nantLocation,buildFile+"updateOrchestration",26)
call AddTool(wshShell,"BT - Update SSO",nantLocation,"-D:debugDeploy=true "+buildFile+"deploySSO",26)
  call AddTool(wshShell,"BT - &amp;NAnt Current Target",nantLocation,"-buildfile:$(ItemPath) $(CurText)",26)
  call AddTool(wshShell,"BT - Bounce BizTalk",nantLocation,buildFile+"bounceBizTalk",26)

end sub

Sub AddTool(wshShell, toolTitle, toolCmd, toolArg, toolOpt)

	Dim toolCount
	toolCount = wshShell.RegRead(toolKey + "ToolNumKeys")
	call wshShell.RegWrite(toolKey + "ToolNumKeys",toolCount+1,"REG_DWORD")
	call wshShell.RegWrite(toolKey + "ToolTitle" + CStr(toolCount),toolTitle)
	call wshShell.RegWrite(toolKey + "ToolCmd" + CStr(toolCount),toolCmd)
	call wshShell.RegWrite(toolKey + "ToolArg" + CStr(toolCount),toolArg)
	call wshShell.RegWrite(toolKey + "ToolDir" + CStr(toolCount),"$(SolutionDir)")
	call wshShell.RegWrite(toolKey + "ToolSourceKey" + CStr(toolCount),"")
	call wshShell.RegWrite(toolKey + "ToolOpt" + CStr(toolCount),toolOpt,"REG_DWORD")

End Sub

If Err &lt;&gt; 0 Then
	Wscript.echo "Error: " + Err.Description
	Wscript.quit 1
End If

Wscript.quit 0

</code>

In the following example, ExceptionLog table has an xml column called TMXML. This contains a canonical xml message. The SQL can “peer” into the xml column, and pull out selected fields.

<code>
SELECT
  eventDateTime,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Source"][1]/requestType[1]',
  'varchar(24)') AS RequestType,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Itinerary"][1]/currentStepName[1]',
  'varchar(24)') AS CurrentStepName,
  TMXML.value('/*[local-name()="TMXML"][1]/*[local-name()="Header"][1]/*[local-name()="Itinerary"][1]/currentProcessCode[1]',
  'varchar(24)') AS CurrentProcessCode
FROM ExceptionLog

</code>

A nifty idea is to put the above SQL in a view, and hide the xquery statements from the common user. Then the user can simply do

<code>
Select * from ViewExceptionLog
</code>

For further info: http://msdn.microsoft.com/en-us/library/ms178030.aspx

ESB Links

http://www.infoq.com/news/2009/02/ESBVendors
– An ESB Vendors Evaluation by Forrester Research


ESB vs Biztalk Debate Gets Heated in Barcelona
– While giving a presentation at the Enterprise Architect Summit in Barcelona, on It turned into a pretty heated debate over core architectural fundamentals, which we hammered out in front of an audience of about 75 unsuspecting conference-goers….

More coming as we find them…

DECLARE @Jobname Varchar(50)
DECLARE @Jobid binary(16)

Set @Jobname = N’ESB_LaSalle-Loan-Removal-Monthly’
SELECT @Jobid = job_id FROM msdb.dbo.sysjobs WHERE (name = @Jobname)
select ‘job1’, @jobid, @jobname
IF (@jobId IS NOT NULL)
exec sp_delete_job @job_name=@Jobname;

“Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.” +”originating_server_id”

I got this error when doing the following. I did right-click ‘Create Job as” then “Create to” a new query window. I was scripting 3 SQL agent jobs, then tried to combine them into one script.

I was trying to build my script without any “GO” commands, so I could have some variables at the top used throughout as parameters.

It was critical to reset these variables before each call to sp_add_job:
SET @ReturnCode = null
SET @jobID = null

The other “hits” I found when googling were totally off target of my solution above. I’m using SQL 2005, which seems to have an “originating_server_id” instead of an “originating_server” in the sysjobs table.

Hope this helps someone!

Neal

OSLO links

Bill Gates – Future of Software Development – Oslo – WMV Video

BizTalkGurus.com – Sample videos of Quadrant, Intelipad, and “M”

Information Week article – Oct 10, 2008

OS News – Oct 28, 2008

Oslo’s Modeling Language Promises (CIO Oct 28, 2008)

ZDNet – More Microsoft ?Oslo? modeling details fall into place

MSDN – The “Oslo” Modeling Platform

James Clark’s Random Thoughts

SDTimes – Microsoft details Oslo’s modeling language, tools

Screencast: Microsoft BizTalk Server 2006

BizTalk on the VMWare Lab Manager Environment
A Video should appear below:


Download or read more information on CodePlex: http://www.vmware.com/products/labmanager/

Schema Reuse

In the Complex XPath example I just posted, I built a Books schema, which includes a “record” which then points to a second schema called “Book”.

We do this all the time at my various clients.

I decided to take some quick screen shots and show how it works here:

First, build the schema to be included (book.xsd):

Then, build the schema that uses it (books.xsd):

After adding the “Books” element to the “Books.xsd” schema, I clicked the word <Schema> above the root element. From there, I went to the properties window, and clicked the … on Imports. Then leaving the “Import New Schema as” value to the default of “XSD Import”, I cliked the “Add” button, and picked the “book.xsd” schema from the selection list. After that, I overtyped the default prefix value of “ns0” to “bk”.

When this schema was built, I added a “child record” under books. Then, click the properties for that “record” and changed the “Data Structure:

To see the resulting XML and a lesson on Complex XPath,
see this page on Complex XPath.

Complex XPath

Complex XPath with BizTalk Schema/Data

http://www.w3schools.com/Xpath/xpath_examples.asp does a pretty good job of showing elementary XPath.
But in the “Real World” with BizTalk, Xpath can be more challenging.
This is a follow-up to my January 3, 2005 blog entitled “Common XPATH Examples, Questions, Issues with Biztalk”
http://nealwalters.blogspot.com/2005/01/common-xpath-examples-questions-issues.html

When BizTalk creates a sample instance, it creates it in this structure (except below, more realistic data has been entered).

<books>
   <book>
     <title>A Tale of Two Cities</title>
     <authorLastname>Dickens</authorLastname>
     <authorFirstname>Charles</authorFirstname>
     <category>fiction</category>
   </book>
   <book>
     <title>Operating Manual for Spaceship Earth</title>
     <authorLastname>Fuller</authorLastname>
     <authorFirstname>Buckminster</authorFirstname>
     <category>science</category>
   </book>
   <book>
     <title>The Singularity Is Near: When Humans Transcend Biology</title>
     <authorLastname>Kurzweil </authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </book>
   <book>
     <title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
     <authorLastname>Kurzweil </authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </book>
</books>

/books/book/title – returns all titles

/books/book/category[.=’science’] – returns all categories, where the text of that category = ‘science’

/books/book[category=’science’]/title – returns all titles, where the text of that category = ‘science’

/books/book[category=’science’ and authorFirstname=’Ray’]/title – return all titles where Ray is the author and category = ‘science’

When BizTalk creates a sample instance, it creates it in this structure (except below, more realistic data has been entered).
Each “book” record has it’ s own name space. This data was created with a ‘books’ schema, which in turn imported a “book” schema.
The book record was defined by adding a new “record”, and settings its property “Data Structure (Type):

<ns0:books xmlns:ns0=”http://biztalk-training.com/books”>
   <ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
     <title>A Tale of Two Cities</title>
     <authorLastname>Dickens</authorLastname>
     <authorFirstname>Charles</authorFirstname>
     <category>fiction</category>
   </ns1:book>
   <ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
     <title>Operating Manual for Spaceship Earth</title>
     <authorLastname>Fuller</authorLastname>
     <authorFirstname>Buckminster</authorFirstname>
     <category>science</category>
   </ns1:book>
   <ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
     <title>The Singularity Is Near: When Humans Transcend Biology</title>
     <authorLastname>Kurzweil</authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </ns1:book>
   <ns1:book xmlns:ns1=”http://biztalk-training.com/book”>
     <title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
     <authorLastname>Kurzweil</authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </ns1:book>
</ns0:books>

When BizTalk creates a file from the mapping utility, it combines the namespaces onto the root element, and uses a different prefix, as shown below:

<ns0:books xmlns:ns0=”http://biztalk-training.com/books” xmlns:bk=”http://biztalk-training.com/book”>
   <bk:book>
     <title>A Tale of Two Cities</title>
     <authorLastname>Dickens</authorLastname>
     <authorFirstname>Charles</authorFirstname>
     <category>fiction</category>
   </bk:book>
   <bk:book>
     <title>Operating Manual for Spaceship Earth</title>
     <authorLastname>Fuller</authorLastname>
     <authorFirstname>Buckminster</authorFirstname>
     <category>science</category>
   </bk:book>
   <bk:book>
     <title>The Singularity Is Near: When Humans Transcend Biology</title>
     <authorLastname>Kurzweil</authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </bk:book>
   <bk:book>
     <title>The Age of Spiritual Machines: When Computers Exceed Human Intelligence</title>
     <authorLastname>Kurzweil</authorLastname>
     <authorFirstname>Ray</authorFirstname>
     <category>science</category>
   </bk:book>
</ns0:books>

/*[local-name()=’books’ and namespace-uri()=’http://biztalk-training.com/books’]/*[local-name()=’book’ and namespace-uri()=’http://biztalk-training.com/book’]/title

/*[local-name()=’books’]/*[local-name()=’book’]/title

/*[local-name()=’books’]/*[local-name()=’book’][category[.=’science’]]/title

If for some reason, you had a namespace on category, the same query could be done like this:
/*[local-name()=’books’]/*[local-name()=’book’][*[local-name()=’category’][.=’science’]]/title

/*[local-name()=’books’]/*[local-name()=’book’][category=’science’ and authorFirstname=’Ray’]/title

Thanks to the support team at “StylusStudio.com” for helping with these concepts in their forums. Click here for –>
My discussion thread on these issues
The trick they revealed is that the [] expressions can be nested. Here’s another post, where we talked about selecting a higher node:
link.

That’s the end of the XPath lesson, if you want to see how the “Books” schema was created, see here… BizTalk Schema Imports.