BizTalk Musings

Collecting EventLogs with Powershell

Norman Gillis — Tue, 04 Dec 2012 08:29:00 GMT

Hi all,

I often have to go onsite and go through a customers' eventlogs looking for errors. While the eventvwr does provide for nice filtering it doesn't allow me to sort. I like to use Excel for sorting and making a nice table format. In order to do this however I need to be able to format the eventlogs output in a nice delimited format. Powershell provides the ability (ConvertTo-CSV & Export_CSV) to pipe data out to a CSV file however it 'keeps' the carriage returns and line feeds so that ultimately my delimited csv is a mess when I import it into Excel.

I created the following Powershell script to help me collect x number of days worth of data and have it exported nicely to delimited format.

Required data

$logPath : which folder to write the collected data to, may also be in the format "\\fileshare"

$computers : semi-colon delimited list of computers to retreive logs for

$days : how many days worth of logs to collect

$logPath = "C:\Temp" # "\\fileshare"
$log = "Application;System"
$computers = "CNTSQL"
$days = 5

$date = get-date -format M.d.yyyy
$now = get-date
$subtractDays = New-Object System.TimeSpan $days,0,0,0,0
$then = $Now.Subtract($subtractDays)
# Error,Information,FailureAudit,SuccessAudit,Warning
[string] $logName = ""
[string] $eventID = ""
[string] $MachineName = ""
[string] $entryTime = ""
[string] $message = ""
[string] $source = ""
[string] $timeGenerated = ""
[string] $fullentry = ""

Function ExecutePerComputer
{ param ([string] $computers)
# a list of servers for the eventlog collection in semi-colon delimited list
#$compArray
$compArray = $computers.Split(";")
[string] $c = ""
foreach ($c in $compArray)
{
# call GetLog with the servername and the logs to get
Write-Host $c
GetLog $c $log
}
}
Function GetLog
{ param ([string] $server, [string] $logs)
# a list of the logs to return
#$logArray
$logArray = $logs.Split(";")
[string] $l = ""
foreach ($l in $logArray)
{
$systemErrors = Get-EventLog -Computername $server -LogName $l -After $then -Before $now -EntryType Error | select LogName,EventID,MachineName,EntryType,Message,Source,TimeGenerated
#Write-Host $l
foreach ($obj in $systemErrors)
{
$logName = $l
$eventID = $obj.EventID.ToString()
$MachineName = $obj.MachineName
$entryTime = $obj.EntryType
$message = $obj.Message.Replace("`r`n", ",").Replace("`n", ",").Replace("`r", ",")
$source = $obj.Source
$timeGenerated = $obj.TimeGenerated.ToString()

$fullentry = $logName + ";" + $eventID + ";" + $MachineName + ";" + $entryTime + ";" + $message + ";" + $source + ";" + $timeGenerated

WriteToCSV "$logPath\$computers-$log-$date.csv" $fullentry
}
}
}
Function WriteToCSV
{ param ([string] $filePath, [string] $content)

$content | Out-File -FilePath $filePath -Append
}

$File = "LogName;EventID;MachineName;EntryType;Message;Source;TimeGenerated"
WriteToCSV "$logPath\$computers-$log-$date.csv" $File
ExecutePerComputer $computers

Excelsior!

Norman

Powershell & Extended Events

Norman Gillis — Wed, 21 Nov 2012 10:58:34 GMT

I have been doing a lot of work with SQL Server lately. Over on www.sqlskills.com Jonathan Kehayias has written a series of very good blog posts regarding working with Extended Events. I could not however find very much information around Powershell and Extended Events. After some searching on MSDN and TechNet I was able to piece together how to make a stand alone script to create an extended event session. This link was invaluable: http://technet.microsoft.com/en-us/library/ff877887.aspx

#$Instance = "SQLALL\NOGISQL2012"
$InstServiceName = "NOGISQL2012"
#$NodeName = "SQLALL"

Function CreateXEventSession()
{
    # PATH SQLSERVER:\XEvent\SQLALL\NOGISQL2012

    Set-Location SQLSERVER:\XEvent
    $h = hostname
    cd $h
    $store = dir | where {$_.DisplayName -ieq $InstServiceName}
    $session = new-object Microsoft.SqlServer.Management.XEvent.Session -argumentlist $store, "TestSession"
    $event = $session.AddEvent("sqlserver.sql_statement_completed")
    $event.AddAction("sqlserver.sql_text")
    $session.AddTarget("package0.ring_buffer")
    $session.Create()
}

write-host "--<>------ start ------<>--"
Import-Module -Name SQLPS
CreateXEventSession
write-host "--<>------ end ------<>--"
# todo --> start the session

good luck and have fun,

Norman

Windows 7 search returns nothing (again)

Norman Gillis — Tue, 22 Nov 2011 09:01:29 GMT

I have been stumped by this a few times and it drives me crazy. The new windows OS's are supposed to have the speediest search capability so far and yet when I search for files that I know exist, they cannot be found.

The other day I was trying to clean up my harddrive and I wanted to find all the files with extension BLG. Once found I wanted to delete them all. The search tool could find no files of that type at all. I was using the *.blg wildcard.

Solution:

select the folder in the windows explorer
hit the ALT key which should bring up the TOOLS menu
select Tools > Folder Options
a pop-up window appears with 3 tabs
select the SEARCH tab
selec the option "Always search file names and contents (this might take several minutes)

Your searches will now be slower but yes you will be able to find what your are looking for. I can't quite figure out what the point of having a search solution that is fast but can't anything!

gr,

Norman

Windows Azure - DSInit fails - solution

Norman Gillis — Wed, 09 Nov 2011 10:03:41 GMT

Hi all,

I hit an interesting undocumented hidden feature when using DSInit to set up the storage emulator database for my VS2010 projects.The documentation assumes that you have installed SQLExpress and indeed if you have the setup of the emulator will work fine. What however if you have your own SQL Server instance running? What if that instance is a named instance and not a default instance? As I found the creation will fail.

The command line that should work is: > DSInit /sqlinstance:myinstance /user:domain\user /forcecreate

when: myinstance is in the format of <server>\<instance> the creation will fail.

the solution is to use only the <instance> name thus instead of > DSInit /sqlinstance:<server>\<instance> USE > DSInit /sqlinstance:<instance>

Don't forget to run the command prompt as administrator and you can use /user:domain\user /forcecreate as required.

Tutorial: Creating a Windows Azure Hello World Application Using ASP.NET MVC 3: http://www.microsoft.com/windowsazure/learn/tutorials/getting-started-web-tutorial/

Tutorial: Deploying a Windows Azure Sample Application: http://www.microsoft.com/windowsazure/learn/tutorials/deploy-tutorial/

What startup stored procedures are we running?

Norman Gillis — Sun, 17 Jul 2011 19:34:00 GMT

For some environments certain actions must be taken when SQL Server starts up. For some customers temporary tables or other types of transient objects must be present in the Tempdb. Developers or DBAs will then create "start up stored procedures" to ensure that when SQL Server starts up these procedures will execute.

What happens if the original developers/DBAs leave and there is no documentation? How can we find out which objects are being executed at startup? I recently had this question so I thought I would post the answer here as it is interesting.

SELECT ObjectProperty(object_id,'ExecIsStartup'),*

FROM sys.objects

WHERE TYPE IN('X','P','PC')

AND ObjectProperty(object_id,'ExecIsStartup') = 1

regards,

Norman

SQL Server: Move a Table from the Primary Partition to a Secondary Partition

Norman Gillis — Mon, 13 Jun 2011 20:55:18 GMT

I get often asked by customers how they can best move data from their primary partition to a secondary partion. Often databases are created with a simple MDF & LDF set up and the data is on the Primary Default partition. One of the easiest ways to accomplish this task is to use the Create Index while using the With Move To option. I have included the script here:

/* -- clean up
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestMoveTableToPartition'
GO
USE [master]
GO
ALTER DATABASE [TestMoveTableToPartition] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [TestMoveTableToPartition]
GO
*/

CREATE DATABASE [TestMoveTableToPartition] ON PRIMARY
(NAME = N'TestMoveTableToPartition', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartition.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestMoveTableToPartition_log', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartition_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10 %)

GO
use
TestMoveTableToPartition

go
-- create a table on Primary (as default)
drop table Table01
go
create
table Table01(col1 int, col2 int, col3 binary(6000 ))

go
-- fill the table with some sample data
declare @i int
set @i=0
while @i < 10000
begin
insert into Table01(col1, col2) values (rand()*1000, rand()*1000 )
set @i=@i+1
end
go
-- create a clustered index (needed for the move)
create clustered index IX_Col1 on Table01 (col1 )

-- check the datafiles (mdf) to see how big it is, my test was 80 Mb
-- Now add a new Partition and a File (ndf) to it
USE
[master]

GO
ALTER
DATABASE [TestMoveTableToPartition] ADD FILEGROUP [TMTTP02]

GO
USE
[master]

GO
ALTER DATABASE [TestMoveTableToPartition]

ADD FILE ( NAME = N'TestMoveTableToPartitionNew', FILENAME = N'C:\DATA\SQL\TestMoveTableToPartitionNew.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

TO FILEGROUP [TMTTP02]

GO
USE
[TestMoveTableToPartition]

GO
CREATE CLUSTERED INDEX [IX_Col1] ON [dbo].[Table01]
(
[col1] ASC
)
WITH (DROP_EXISTING = ON, ONLINE = OFF) ON [TMTTP02]
GO

SSRS CLR deployment fails - VS 2010 & SQL Server 2008 R2

Norman Gillis — Sun, 01 May 2011 19:22:26 GMT

Hi all,

You can deploy a CLR project to SQL Server 2008 R2 with Visual Studio 2010 however do not forget to set the target .Net version to 3.5. The default for VS 2010 is .Net 4.0 and this will cause a deploy error, (fails to deploy). I lost a some time fiddling with this before I was able to get it to work.

gr,

Norman

Back online!

Norman Gillis — Sun, 01 May 2011 19:14:08 GMT

I am back online after a bit of a hiatus. Microsoft made some changes to the logon process and accounts in general. I was unable to sign in to my account for some time now.

According to Microsoft:
On January 27th, we have deployed Profile Integration to the MSDN & TechNet Blogs (http://blogs.msdn.com/b/help/archive/2011/01/21/integration-of-msdn-blog-user-account-with-msdn-forums-profile.aspx ). This is part of the larger STO Profile integration, which consolidate our users profile suites down to a single instance which will be used across all STO community platforms.

regards,
Norman

Writing to the EventLog from an Orchestration

Norman Gillis — Tue, 23 Mar 2010 17:44:00 GMT

Please be very careful with how you do this. This can be a disaster in a production environment (Imagine writing millions of messages to an eventlog)! I once saw an environment that was writing customer information in the EventLog (there is a very bad idea as anyone with rights to read the log can get access to that information).

Writing Message Content
Trace and debug messages are also useful for writing out the contents of messages and orchestration variables. This is useful for inspecting intermediate messages produced by multiple transforms and for determining the values of other transient values.

The first step is to add the appropriate variable declarations to the orchestration:

stringMessage of type string
xmlMessage of type System.Xml.XmlDocument
myMessage is the orchestration message whose contents are to be output

After these variables have been declared, add the following code to an Expression shape in your orchestration:

xmlMessage = myMessage;
stringMessage = xmlMessage.OuterXml;
System.Diagnostics.EventLog.WriteEntry("MyBizTalkLogEntry", stringMessage);

The is handy perhaps for you Dev environment but very dangerous for production.

For more debugging tips please refer to: http://technet.microsoft.com/en-us/library/cc825581(BTS.10).aspx

Running BizTalk Documentor on a 64bit machine

Norman Gillis — Tue, 23 Mar 2010 10:48:00 GMT

The BizTalk Documentor is a great tool for auto-generating documentation for your environment.

The tool can be downloaded here: http://biztalkdocumenter.codeplex.com/

If you have a 64bit environment then it will most likely not work for you. You can get around this by running with 32bit Coreflags on. You will need to have either the Win 2k? SDK installed or Visual Studio. Then you open a command prompt and execute the following: coreflags.exe Microsoft.Services.Tools.BiztalkDocumenter.exe /force /32Bit +

Happy documentoring! ;-p

SQL Profiler irritating "feature" - "You don't have permission to open this file" Trace

Norman Gillis — Wed, 17 Feb 2010 15:27:00 GMT

Hi all,

Ok, this is a very irritating feature that took a bit of searching before I figured out what was going on. I have a Windows 7 machine with SQL Server 2008 Dev installed. I set up a trace via the SQL Profiler so that I could detect Blocking transactions. I started the trace (not in the gui but as a script within the query analyzer) and did a repro. Once I caught my data I saved it and stopped the trace. I was able to import into a temp table via

SELECT * INTO dbo .MyTraceTable
FROM fn_trace_gettable( 'c:\temp\MyTrace.trc' , default );
GO

There after I tried to load the trace into the profiler (I had taken the Deadlock Graph) and wanted to see the image that was created. Instead of loading I received a rather annoying error message: "you don't have permission to open this file"! WTH! I am an administrator on my laptop and that account is a sysadmin in SQL and yet I am not allowed to open my own file that I created! This was a bit of a headscratcher.
To make a long story short the simple solution is to run the SQL Profiler "as administrator".
One of these days I really need to sit down and try to figure out how the UAC works otherwise there will never be a truce in my on-going war with it.

regards, Norman

BizTalk 2006 R2 Service Pack 1 fixes grouped by category

Norman Gillis — Thu, 11 Feb 2010 11:01:00 GMT

Hi all,

There has been a Service Pack released for BizTalk 2006 R2 and it can be downloaded here: http://www.microsoft.com/downloads/details.aspx?FamilyID=1768f7a3-d843-4f5b-aba7-b3d72892c16f&displaylang=en

For a listing of fixes grouped by category please follow this link: http://blogs.msdn.com/biztalkcrt/archive/2010/01/29/Categorized-list-of-fixes-included-in-biztalk-2006-r2-sp1.aspx

regards,

Norman

DTA Purge & Archive Job when Global Tracking is disabled

Norman Gillis — Mon, 01 Feb 2010 11:35:00 GMT

Hi all,

I have seen some confusion around the Job: DTA Purge & Archive, especially in regard to when the Global Tracking is disabled.

There are two important points to note:

1. Turning off Global Tracking by setting adm_Group table, column GlobalTrackingOption to 0 in the BizTalkMgmtDb does not always fully disable Global Tracking. See SOX061023700004.

2. Even when Global Tracking is successfully disabled the DTA database can continue to grow for two reasons:

i) Every time the BizTalk Backup job runs it inserts a row in the MarkLog table of every database backed up (that will include DTA).

ii) Every time you terminate a suspended instance a row will be written to ServiceInstances table in DTA

Point i. you cannot do anything about, although the Terminator tool has a task to clear this down.

Point ii. is a bug that is not going to be fixed. (Applies to 2009, R2, 2006, 2004). It is unfortunately not documented (yet).

If you enable the DTA Purge & Archive job then both Dtasp_BackupAndPurgeTrackingDatabase and Dtasp_PurgeTrackingDatabase will cause the

dta_ServiceInstances table to get cleared down (FYI along with dta_CallChain, dta_DebugTrace, dta_MessageInOutEvents, dta_ServiceInstanceExceptions, Tracking_Fragments1/Part1/Spool1 (not 2), dta_MessageFieldValues, and various Rules tables).

That is why it is worth enabling the job (even if it only runs once a week).

regards,

Norman

Windows 7 Godmode

Norman Gillis — Wed, 06 Jan 2010 08:17:00 GMT

Hi all,

Just like 3D shooters such as Doom, Windows 7 also has a Godmode! To activate Godmode simply make a new folder and give it this name: GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

Now you have created a folder that gives you quick access to all types of configuration settings.

gr,

Norman

Visual Studio Missing Properties Code Snippets

Norman Gillis — Fri, 01 Jan 2010 09:41:00 GMT

Hi all,

CodeSnippets within Visual Studio are very handy to speed up the coding process and reduce the amount of typing that you have to do. In particular if you have to create classes with a lot of properties it can save a lot time if you can automate it. Unfortunately the standard property only creates Get/Sets but doesn't automate placing the variable names. If you add this following snippet to your folder: "C:\Users\<YourUserName>\Documents\Visual Studio 2008\Code Snippets\Visual C#\My Code Snippets\propc.snippet" you can access via Ctrl-K-X your new snippet!

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>propc</Title>
<Shortcut>propc</Shortcut>
<Description>Code snippet for property and backing field</Description>
<Author>Microsoft Corporation</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>type</ID>
<ToolTip>Property type</ToolTip>
<Default>int</Default>
</Literal>
<Literal>
<ID>property</ID>
<ToolTip>Property name</ToolTip>
<Default>MyProperty</Default>
</Literal>
<Literal>
<ID>field</ID>
<ToolTip>The variable backing this property</ToolTip>
<Default>myVar</Default>
</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[private $type$ $field$;
public $type$ $property$
{
get { return $field$;}
set { $field$ = value;}
}
$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Happy Coding!
Norman

Debugging a vbs script (cscript)

Norman Gillis — Mon, 16 Nov 2009 20:55:00 GMT

Hi all,

You can use the Visual Studio Debugger to debug a .vbs (vbscript) file executed with cScript.exe by using the //X flag at the command line.

To debug MyTest.vbs cscript.exe MyTest.vbs //X The //X will set a breakpoint and invoke the "select a debugger" dialog where you can choose Visual Studio and step into your vbs code. Afterward it's just a question of stepping through the code (F10, F11). You can also set break points within the script and evaluate variables.

Microsoft Virtual Labs

Norman Gillis — Tue, 03 Nov 2009 07:38:00 GMT

A relatively unknown service that Microsoft provides is that of virtual labs. If you use this link:

http://msevents.microsoft.com/CUI/SearchDisplay.aspx?culture=en-US&evtTypHero=4#culture=en-US;sortKey=;sortOrder=;pageEvent=false;hdnInitialCount=;kwdAny=bizTalk;eventType=4;searchcontrol=yes;s=1

you will find 65 labs that you can sign up for and do free of charge. This labs are great because through your browser you can run a BizTalk server environment exactly as if you had a remote connection to the server. There are step-for-step manuals and all the source code is there as well. If you are curious about testing out some of this technology I high recommend giving them a try.

regards,

Norman

Convert Xml to Html using a Pipeline Xsl Transformation

Norman Gillis — Sun, 18 Oct 2009 13:02:00 GMT

Hi all,

The SDK that you can install with BizTalk has some very nice simple code examples that illustrate how you could tackle a specific problem. Please note however that these are not Enterprise tested solutions and are designed for Demo purposes only.

In this example I had been asked to do a demo using a pipeline. This sample was originally designed to use SMTP however considering that Windows Server 2008 doesn’t have this built-in support anymore (whereas Windows Server 2003 does) I changed it to be a simple File to File type of transport. This is much more practical when trying to show a demo.

Goal of the Demo:

1. Pick up an Xml file that we want to convert to Html.

2. Using a Custom Assembly locate a Xsl transformation file.

3. Convert the Xml to Html using the Transformation and Custom Code

4. Send the Html file out

Within the SDK there is a nice little project for us to use. Under the following directory (depending upon where you installed it): “C:\Program Files\Microsoft BizTalk Server 2009\SDK\Samples\Pipelines” you can find the folder “XslTransformComponent” and within that there are 2 projects. The first project is a BizTalk project called “SendHtmlMessage” and this is where our pipeline is designed. This is the project that you will deploy to your BizTalk 2009 server. The second project “XslTransfom’ is interesting because it contains the custom code to dynamically find and use a transformation “Xsl” file and apply over incoming xml.

First Steps:

When I did this for the first time I copied the whole folder to my C:\projects\pipelines directory so that the original code would stay clean and I would have backup if things went wrong. Now within the XslTransformComponent folder there should be 2 project folders (SendHtmlMessage and XslTransformComponent) as well as XslTransformComponent.sln file, .bat files (ignore these as I had only problems within them on Win2K8) and a DocInstance.xml file (which will be used later for testing). Since the .bat file deployment didn’t work for me I had to do a number of things manually. Afterward I was happy for this because by doing it by hand I got a good feeling for how the pieces worked together. The first manual operation I needed to do was create a strong name file for the BizTalk project. The command line (make sure you use the visual studio command prompt and not the one found with windows) I used was: “>sn –k SendHtmlMessage.snk”. Remember you can’t deploy a BizTalk project unless the assembly has been signed with a strongname. I also like to give my project a name so that I can find it easily in the BizTalk management console. A project can be given a name within the property pages under “Deploy” within Visual Studio. I called my project aptly enough SendHtmlMessage. ;p

Once the strongname is there it shouldn’t be a problem compiling your project. Note that we don’t need a strong name for the XslTransformComponent project nor did I install it to the GAC.

Project SendHtmlMessage should already have the 3 following files (and now the strongname file):

1. Confirmation.xsd

2. SendHtmlMessage.snk

3. Xml2HtmlSendPipeline.btp

a. A send pipeline contains 3 phases: i. Preassemble, ii. Assemble and iii. Encode

b. Phase i. Preassemble: leave empty

c. Phase ii. Assemble: the standard Xml Assembler

d. Phase iii. Encode: XslTransform

Project XslTransform should already have the (besides the AssemblyInfo class) only the class: XslTransformer.cs. This is where our custom code for transforming is found.

Next Steps:

Once the BizTalk project compiles we can deploy it. Once it is deploy open and refresh the BizTalk Management Console to see if the Application SendHtmlMessage is present. If it is not present recheck that the project deployed from Visual Studio (also check the property pages to make sure the correct SqlServer and Management database is being targeted).

Just a few more Steps:

Configure the FileAdapter ports.

1. Create a Receive Port and Location

a. Port Name: recprtTransform

i. Port Type: one way receive

b. Receive Location Name: reclocTransform

i. Type: File

ii. Configure the receive folder: C:\Projects\Pipelines\XslTransformComponent\TEST\IN\

iii. File mask is *.Xml

iv. Receive Handler: BizTalkServerApplcation

v. Receive Pipeline: PassThruReceive

2. Create a Send Port

a. Port Name: sndprtTransform

i. Port Type: one way receive

ii. Configure the receive folder: C:\Projects\Pipelines\XslTransformComponent\TEST\OUT

iii. File Name is: New_*.Html

iv. Send Pipeline: Xml2HtmlSendPipeline

Last Steps:

Now we must copy the XslTransform Dll from the “C:\Projects\Pipelines\XslTransformComponent\XslTransform\bin\Release” folder to the “C:\Program Files\Microsoft BizTalk Server 2009\Pipeline Components” folder.

The Transform.xsl file must be copied from the “C:\Projects\Pipelines\XslTransformComponent\SendHtmlMessage” folder to the same “C:\Program Files\Microsoft BizTalk Server 2009\Pipeline Components” folder.

Once this is done we can test our work!

Start the BizTalk SendHtmlMessage application.

Find the DocInstance.xml file and copy it (ctrl-c / ctrl-v) to the C:\Projects\Pipelines\XslTransformComponent\TEST\IN\ and if everything worked there should be a Html result in the ..\TEST\OUT folder.

Look at the results, how cool is that?

A simple BizTalk Server 2009 WCF SqlServer adapter Stored Procedure example

Norman Gillis — Mon, 05 Oct 2009 12:01:00 GMT

Hi all,

Continuing my delving into BizTalk 2009, ESB and WCF I have done a simple tutorial on how to insert a record into a SQL Server 2008 database using the new WCF Adapters. The new WCF Adapters are highly configurable and manageable and as such we recommend using them.

One important Gotcha: I had already installed the new ESB 2.0 guidance on my Windows Virtual PC. This meant that the GlobalBankESB database had already been created including a stored procedure that I wanted to call in my tutorial. This stored procedure was already being called from another tutorial project and as such there were already artifacts generated for it. In particular this was important: there was a record created in the BizTalkMgmntDb, table bt_DocumentSpec for the already generated artifacts related to the stored procedure and other project. When I used the same stored procedure to generate artifacts and use it in a different project I had a problem where the project wouldn't work. The following is the error that I received:
Event ID: 5719
Description:
There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive" Source: "XML disassembler" Receive Location: "C:\Projects\OrdersIn\*.xml" Reason: The disassembler cannot retrieve the document specification by using this type: "<MyDocName>". Either the schema is not deployed correctly, or more than one schema is deployed for the same message type.

How to get around this problem? First I had to undeploy from BizTalk the test project that I had made so that the double records within the bt_DocumentSpec table would be corrected. The next step was to create a copy of the stored procedure I wanted to use and rename it to something unique. Then I could regenerate the artifacts and redeploy and it worked. I looks as if you want to reuse this type of artifact within a BizTalk group you will have to generate your artifacts and then if you wish to reuse them you will have to share your project within your other projects to get access.

Simple BizTalk Server 2009 WCF SqlServer Adapter Example

Phase #1. The database, I had used the GlobalBankESB and storedprocedure "InsertProduct". I then had to make a copy of this procedure and named it InsertProduct2 (read section Gotcha above). Here is the table and stored procedure:

SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[Price] [money] NOT NULL,
[Quantity] [int] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[InsertProduct]
@Name nvarchar(100),
@Description nvarchar(MAX),
@Price money,
@Quantity int
AS
BEGIN
INSERT INTO [dbo].[Product] ([Name],[Description],[Price],[Quantity])
VALUES (@Name,@Description,@Price,@Quantity)
END
GO

Phase #2. Visual Studio, Create a VS studio project to generate the artifacts we need to call the stored procedure from BizTalk.
My goal here is to pick up an external message, convert it into an internal message and have BizTalk write a record to the database using the new WCF SQL functionality. I ended up with the following items in my project:

a XSD that defines the external message. This I created myself: ProductExt.xsd
a XSD that defines the internal message in such a manner that we can use it write to the database (generated items)
a transform map that allows me to convert from the external to my internal. This I created myself: ProductExt_To_ProductInt.btm
an Orchestration so that I can control what's going on and more easily debug in the event of a problem
a WCF binding file (a generated and a critical item)
don't forget that all BizTalk projects need a strong name, example: sn.exe -k NoGiWCFSQL.snk

Step 1. Create the project and add ProductExt.xsd.

<?xml version="1.0" encoding="utf-16" ?>
<?xml:namespace prefix = xs />
<xs:schema xmlns="http://NoGiBTSSQL.ProductExt" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://NoGiBTSSQL.ProductExt" xmlns:b="http://schemas.microsoft.com/BizTalk/2003">
<xs:element name="Product">
<xs:complexType><xs:sequence><xs:element name="Name" type="xs:string">
<xs:element name="Description" type="xs:string">
<xs:element name="Cost" type="xs:decimal"><xs:element name="Code" type="xs:string">
<xs:element name="Quantity" type="xs:int"></xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema

Step 2. Generate Artifacts

Right click the project and select Add > Add Generated Items.

A browser window will open, select ConsumeAdapterService

Select ConsumeAdapterService

This will reveal the Consume Adapter Service window:

Select a binding: sqlBinding

Configure a URI: mssql://<yourServer>/<yourInstance>/<yourDB>? (this connection URI is quite different that the usual connection strings that we expect with a SQLServer database)

Connect: If your connection status is not Connected recheck your URI

Select a category: I selected ‘Procedures’ as I want to call a stored procedure.

Available categories and operations: notice that there are 2 records here in this list, InsertProduct and InsertProduct2. This is the Gotcha that I spoke about. I originally tried to generate artifacts for the InsertProduct record (InsertProduct was not even present at the time) and that was successful. When I deployed my project however the processing failed. After quite some time debugging and searching I discovered that I had generated the same artifacts and deployed as were already done in an ESB tutorial. In the bt_DocumentSpec table there were double records present for the InsertProduct object. I am not sure if this is a bug or an undocumented hidden feature. To overcome this quickly I duplicated the InsertProduct stored procedure and appended it with 2. A better solution perhaps would be to reuse the already present artifacts but I thought this was interesting enough to write about it so that others can avoid it.

Click Ok

Visual Studio will now add a number of Artifacts to your project (depending upon what you have chosen to generate). In my case I had the following:

1. DataSetSchema.xsd

2. Procedure.xsd

a. Defines a Request Insert object

b. Defines a Response object

3. A binding file: WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml containing the information that I needed to create a Request / Response call for within my orchestration

Step 3. Create an Orchestration and transform map to insert the records.

The external message is picked up and then transformed to my internal representation defined by the Procedure.xsd Request object. The advantage of an Orchestration from a development point of view is that you can debug much more easily and add System.Diagnostics.EventLog.WriteEntry(“<myEntry>”) types of items to help you. After the record has been inserted a response comes back and this file gets placed in an ‘archive’. Note that the 3 ports are defined as logic ports

1. PortProductExt (always receiving)

2. PortReqRespInsertWCFSQL (request/response)

3. PortSendProductArchive (always sending)

Also note that

1. msgProductExt defined by my ProductExt.xsd

2. msgProductReq defined by the Procedure.xsd > request object

3. msgProductResp defined by the Procedure.xsd > response object

Step 4. Compile and deploy.

Notice now that there is an orchestration in the project and that it requires a Receive port, Request/Response port and a send port.

The first step is to bind the ports as follows:

1. Receive Port (and location) for ProductExt: C:\projects\ProductIn, File adapter, *.xml

2. Send Port for PortSendProductArchive: C:\projects\Archive, Archive_%MessageID%.xml

3. Send / Receive Port for the PortReqRespInsertWCFSQL: this is a nice trick and reveals the power of the WCF Adapters. Right click on the BizTalk project that you have deployed and select Import > Bindings. Browse to the xml (binding) file that was added to the project. In my case that was: WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml and select it. This will configure the WCF port for you, awesome.

4. Ensure that all the ports have been bound properly

5. Start the BizTalk application and test it by dropping an instance of the ProductExt into the C:\projects\ProductIn folder.

Note: WCF-Custom is selected as the type.

Note: the binding is sqlBinding and within the SOAP Action header there is a reference to my InsertProduct2 operation.

A record should have been created in the Database product table and an archive of the response message in the Archive folder!

Error attempting to install SqlServer 2008 on Windows 7.

Norman Gillis — Sun, 04 Oct 2009 19:21:00 GMT

Hi all,

I was attempting to install SqlServer 2008 on my Windows 7 laptop (just for the heck of it) and ran into an odd problem. Each time the setup ran everything would pass except that "requires reboot" would show as having failed. I rebooted several times however to no affect. After doing a bit of poking around I found that a registry key was holding me back.

This key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

had the following value: \??\C:\Windows\system32\spool\PRTPROCS\W32X86\1_HPZPPLHN.DLL

I deleted this value and ran setup again. SqlServer 2008 then installed perfectly on my Win7 machine.

*Important: it is recommended to install SP1 with SqlServer 2008.

Microsoft PFE BizTalk offerings

Norman Gillis — Mon, 28 Sep 2009 11:40:00 GMT

Hi all,

Within the Microsoft PFE BizTalk group we have a number of offerings/engagement that we can provide as a service.

BizTalk 2009 ESB 2.0 Development Workshop (coming soon)
BizTalk 2004/2006 HealthCheck
BizTalk 2006 Administration Workshop
BizTalk 2006 Development Workshop
BizTalk 2006 Disaster/Recovery Workshop
BizTalk 2004 > 2006 > 2009 Migration Workshop

Please send me an email if you have any questions regarding these services: gillisn@microsoft.com

Visual Studio 2008 - unable to create project - Part II

Norman Gillis — Thu, 24 Sep 2009 14:01:00 GMT

The saga of my Windows Server 2008 / BizTalk 2009 / ESB development virtual machine continues.....

I had an issue that I was unable to create any Itineraries in a normal C# project. The ESB 2.0 guidance has a set of great tools/software for doing this. I was able to solve this problem by running the Visual Studio 2008 Service Pack 1 again. I did a number of tutorials that comes with our PFE ESB workshops and it worked great. Microsoft offers workshops in BizTalk Administration and Development (the ESB being part of that).

Today when I tried to create a BizTalk 2009 project I received a nasty (tiny) little message: Creating project ‘project name’… project creation failed. If you encounter this, there will be no pop-up or anything. There is just a message in the lower left hand corner of the VS IDE. Of course the fact that the project didn't get created should also be a good indicator that something is wrong. ;p

I checked the Event Logs and unfortunately there was no information. A quick search of the net using www.bing.com lead me to: http://blogs.msdn.com/biztalkcrt/archive/2009/08/21/visual-studio-2008-fails-to-create-open-biztalk-projects.aspx

Basically it seems a registry setting is getting messed up/not correct:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}]
"PossibleProjectExtensions"="csproj" - Original VS install reg value.
"PossibleProjectExtensions"="csproj;btproj" - Post Biztalk installation reg value

The following is the entry for the 64 bit version of VS:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}

gr,
Norman

Creating Certificates

Norman Gillis — Wed, 23 Sep 2009 11:38:00 GMT

Hi all,

Certificates are part of the Windows environment and provide for better messaging security. I decided to write a blog about how to create a self-signing certificate because I do this only infrequently and it's a good memory exercise to write something down. ;p

To quickly review why certificates are a good thing:

you can send and receive data that is trusted
the data that you process is secure
ensure that only authorized parties receive your messages/data
ensure that you receive messages/data only from authorized parties

The first thing you are going to need is a Microsoft SDK. The Visual Studio 2008 SDK can be found here: http://www.microsoft.com/downloads/details.aspx?FamilyID=59ec6ec3-4273-48a3-ba25-dc925a45584d&DisplayLang=en

Once installed you will have to locate the MakeCert.exe application. In my case I found it under: C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin folder. If you start up this application in a command prompt you can do>MakeCert /? to see which switches are available. There is a complete description to be found here: http://msdn.microsoft.com/en-us/library/aa386968(vs.85).aspx

Typically* you will need to do something similiar to the following:

STEP #1, "makecert -r -pe -n CN=NOGICERT -b 01/01/2009 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss My -sr currentuser". This will install the certificate to the current user account personal store and will have a personal key with it. You will need to create a password here and don't lose it.

STEP #2. export the private key. I ended up saving it to C:\projects\NoGi.com.pfx. don't forget your password!

STEP #3. you can export the public key as well (this you would give to customers and clients). This was C:\projects\NoGi.com.pfx for me.

STEP #4. in the MMC go to the Local Computer Other People store and import the .cer file (the public file that you just exported). When I was setting up a Dev box for doing ESB 2.0 I had to do some a problem with developing itineraries. When you try to make an itinerary it demands that there be a certificate. I ended up importing my NoGi.com.cer also into the Current User > Trusted Root Certificates. Once I did this Visual Studio was happy again and I could compile my projects. This kind of information is vital and I wish Microsoft did a better job of documenting it. This issue was a real show stopper and I found it to be quite frustrating sifting through blogs to figure out how to create a selfsigning certificate and export etc. etc.
*Remember I set this up for a Dev box to give me self signing functionality, your setup may be different.

Where you need to install this self-signing certificate is dependant upon what you wish to use it for. BizTalk for example uses 2 Windows certificate stores:
1) Other People certificate store (Local Computer Folder), the public keys are stored here.
2) Personal certificate store (Current User Folder), private keys are stored here.

Other People Certificate Store.
Public key certificates are "public" and as their name implies are accessible to anyone with access to the computer on which they are stored. BizTalk for example will retrieve public key certificates from this store to encrypt messages and verify the digital signature of incoming messages. All users can read and use any certificates saved in this store.

Personal Certificate Store.
BizTalk (for example) uses the private key certificates saved in this store to decrypt incoming messages and sign outgoing messages. Windows accounts that are allowed to log on interactively will have a personal certificate store that only they can access. BizTalk server uses the personal store for the service account of each host instance in order to access the private key certificates belonging to that service account. Therefore every host instance that requires the certificate for decryption or signing of outbound messages must that that certificate saved in its' Personal Certificate store.

Certificates can be viewed via the MMC snap-ins > Certificate Console:

Certificate purpose	Certificate type	Certificate store
Signing	Own private key	Personal store for each service account of a host instance that has a send pipeline with a MIME/SMIME Encoder pipeline component configured to sign messages (Add Signing Cert To Message property set to True).
Verifying signature	Partner's public key	Other People store on each computer that has a host instance that has a receive pipeline with a MIME/SMIME Decoder pipeline component.
Decrypting	Own private key	Personal store for each service account of a host instance that has a receive pipeline with a MIME/SMIME Decoder pipeline component.
Encrypting	Partner's public key	Other People store on each computer that has a host instance that has a send pipeline with a MIME/SMIME Encoder pipeline component configured to encrypt messages (Enable encryption property set to True).
Party resolution	Partner's public key	Other People store on the administration computer from which you are configuring party resolution.

Visual Studio 2008 - unable to create project - unable to create itinerary

Norman Gillis — Tue, 22 Sep 2009 23:29:00 GMT

Hi all,

While continuing to try to build my Windows Virtual PC machine I encountered a particularly annoying (bug?) issue. I am making a stand alone box with Windows Server 2008, Visual Studio 2008, SQL Server 2008, BizTalk 2009 and including specifically the new ESB 2.0 guidance. I had finally gotten everything installed and configured and I attempted to make my first project from the tutorials and start learning. I opened a project and attempted to add a new item, an itinerary. When the item appeared the name immediately defaulted to the extension .cs (class) and once it was shown in the IDE it displayed the SQL connection screen and some other wierd stuff that I won't go into detail. It was all quite odd. I did some searching on Bing with only lacklustre results when I came across a vague article where someone had trouble creating VS projects. The suggestion was to run the VS 2008 SP1 again to fix that issue. I lost the url for that article (sorry about that). I did however decide to take that advice and run the service pack again and low and behold, it worked! Tomorrow I will be back at the ESB tutorials again!

regards,

Norman

"Please wait while the installer finishes determining your disk space requirements"

Norman Gillis — Tue, 22 Sep 2009 20:13:00 GMT

While attempting to build an ESB 2.0 dev machine I ran into this issue repeatedly. The installer would sit and wait, I would sit and wait but nothing happened. Finally I started closing it and opening the MSI again (whilst doing runas Administrator). This solved the problem for me however I have seen another possible solution as follows:

Go to command line in elevated security mode, and type:

msiexec.exe -package MyPackage.msi -qr

This will basically run the installation package in quiet mode with reduced UI, and apparently circumvent the bug.