Part of my job is to have an up to date documentation of all integrations we have. I am used to using Visio for this, and have at my previous clients kept it quite simple with manual formatting for visualizing if a message is under construction or to be deleted etc.
At this moment, I am trying out having "a lot" of documentation gathered in the Visio document. Besides having shapes for "Systems" and connections between these that each is equivalent of a "Message", I have added custom data fields on these that allow me to document metadata on each message flow. Part of the metadata I have on a message is Id, Name, Trigger, Status, Transport type and so on. Some of these fields are then used to dynamically alter the format of the connection to give a visual cue to the underlying data. If the Status is "Planned" I give the connection a green color, if the connection is marked as "Request/Response" I visualize this by having a symbol indicating so.
This all works fine and gives me one single document for the overview of the integrations. I then thought of implementing a change list so that I could follow up on revisions of the diagram. Having a normal text based change list on a separate sheet would be possible, but would incur manual work to update it and also not be mandatory (meaning that it would be forgotten and hence useless). Instead, I opted for an extra custom data field on the shapes used indicating the last time the shape was updated (data wise, not if it was moved). This is handled automatically by code that triggers when the shape data is updated.
I have one class module called "UpdateShapeTimeStamp":
Dim WithEvents appObj As Visio.Application
Private Sub appObj_CellChanged(ByVal Cell As IVCell)
If (Cell.Shape.CellExists("Prop.Updated", 0)) Then
Cell.Shape.CellsU("Prop.Updated").FormulaU = Chr(34) & Now() & Chr(34)
End If
End Sub
Private Sub Class_Initialize()
Set appObj = Application
End Sub
Private Sub Class_Terminate()
Set appObj = Nothing
End Sub
and the Document code to enable this code:
Dim UpdateShapeTimeStampClass As UpdateShapeTimeStamp
Private Sub Document_BeforeDocumentClose(ByVal Doc As IVDocument)
Set UpdateShapeTimeStampClass = Nothing
End Sub
Private Sub Document_DocumentOpened(ByVal Doc As IVDocument)
Set UpdateShapeTimeStampClass = New UpdateShapeTimeStamp
End Sub
Now when changing the shape data on any shape, the event will trigger and check if the custom field "Updated" is available (meaning that it is one of my custom shapes) and if so, set it to the current datetime.
This allows me to have an automatic tracking of all changes in the diagram for each shape. Neat!
Tuesday, February 28, 2012
Tuesday, January 24, 2012
Outlook 2010 macro to copy item links to the Windows clipboard
I have used OneNote for a few years to handle all my notes regarding different projects and all information I gather during work. It works very well, and I have during this time looked at how to use Outlook in a more advanced way. Of course, I want to tie the two products together.
In the basic form, it is possible to create OneNote note taking pages from an email or appointment in OneNote. This is nice, since I can simply bring up the context menu and choose "OneNote" in order to create a place for all my notes from this specific meeting (and also write down things to bring up before the meeting occurs so that I am prepared). I have however often run into the issue that a lot of scheduled meetings will be accompanied by separate emails containing information that is relevant. In some way I'd like to group these separate items together, preferably in the OneNote page that I have created for the meeting.
I looked at how OneNote creates a page for an email, which is in the same way as for an appointment. It will bring with it a few pieces of information and also create a link back to the email item in Outlook. This link is what I am interested in.
I looked into different ways of creating it. Most promising was a utility called Linker that can copy the internal ID of an Outlook item and put it in the clipboard. The flaw was that it prefixes the ID with "outlook:". This works natively for Outlook 2003. Outlook 2007 can handle it with a registry hack. Outlook 2010 will not handle it at all.
I at least got a bit closer to a solution.
I then found out that a proper prefix in Outlook 2010 is "onenote:outlook?folder=Contacts&entryid=". I looked into how to get the ID from the Outlook item via VBA code and it proved quite easy. I then hacked together a small Macro that would grab the ID from the currently selected item, create the correct HTML link with the prefix above, and then put it in the clipboard. This did of course not work.
The clipboard is an intricate piece of Windows that can handle a lot of different data. Putting raw text into it is simple, putting an HTML link into it a bit more difficult. I turned to Google and found a support article titled "How to add HTML code to the clipboard by using Visual Basic".
This helped me create the more intricate string used to identify HTML in Windows. Using this, I had it working.
After a while I noticed that special characters (å ä ö specifically since I'm in Sweden) did not work and got encoded in a wrong way. After trying different ways of encoding the string I finally resorted to try and put the entity name ä in the string and this worked! It is HTML I'm working with after all. A quick google gave me this nice piece of code to encode special characters in a string, and now I have it all working flawlessly.
My workflow now is as follows:
An appointment is made in Outlook, by me or someone else. I rightclick this and add a OneNote page with information regarding this meeting under a tab for the specific project. Those emails that are of interest for this meeting is then linked in the OneNote page using my Macro. I simply click the email in Outlook, hit my shortcut button to copy the link, and then paste it in OneNote. I have created the macro so that the link description is fetched from the subject in the email. Perfect!
I also noticed that the macro will create links to pretty much anything in Outlook. Emails, appointments, contacts and so on. This way I can link together all items in one OneNote page so I don't have to browse or search in Outlook for whatever I need.
Here is the complete code for the macro if you are interested:
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) _
As Long
Private Declare Function GlobalAlloc Lib "kernel32" ( _
ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function SetClipboardData Lib "user32" ( _
ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function RegisterClipboardFormat Lib "user32" Alias _
"RegisterClipboardFormatA" (ByVal lpString As String) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalUnlock Lib "kernel32" ( _
ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSource As Any, ByVal cbLength As Long)
Private Declare Function GetClipboardData Lib "user32" ( _
ByVal wFormat As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" ( _
ByVal lpData As Long) As Long
Private Const m_sDescription = _
"Version:1.0" & vbCrLf & _
"StartHTML:aaaaaaaaaa" & vbCrLf & _
"EndHTML:bbbbbbbbbb" & vbCrLf & _
"StartFragment:cccccccccc" & vbCrLf & _
"EndFragment:dddddddddd" & vbCrLf
Private m_cfHTMLClipFormat As Long
Function RegisterCF() As Long
'Register the HTML clipboard format
If (m_cfHTMLClipFormat = 0) Then
m_cfHTMLClipFormat = RegisterClipboardFormat("HTML Format")
End If
RegisterCF = m_cfHTMLClipFormat
End Function
Public Sub PutHTMLClipboard(sHtmlFragment As String, _
Optional sContextStart As String = "", _
Optional sContextEnd As String = "")
Dim sData As String
If RegisterCF = 0 Then Exit Sub
'Add the starting and ending tags for the HTML fragment
sContextStart = sContextStart & ""
sContextEnd = "" & sContextEnd
'Build the HTML given the description, the fragment and the context.
'And, replace the offset place holders in the description with values
'for the offsets of StartHMTL, EndHTML, StartFragment and EndFragment.
sData = m_sDescription & sContextStart & sHtmlFragment & sContextEnd
sData = Replace(sData, "aaaaaaaaaa", _
Format(Len(m_sDescription), "0000000000"))
sData = Replace(sData, "bbbbbbbbbb", Format(Len(sData), "0000000000"))
sData = Replace(sData, "cccccccccc", Format(Len(m_sDescription & _
sContextStart), "0000000000"))
sData = Replace(sData, "dddddddddd", Format(Len(m_sDescription & _
sContextStart & sHtmlFragment), "0000000000"))
'Add the HTML code to the clipboard
If CBool(OpenClipboard(0)) Then
Dim hMemHandle As Long, lpData As Long
hMemHandle = GlobalAlloc(0, Len(sData) + 10)
If CBool(hMemHandle) Then
lpData = GlobalLock(hMemHandle)
If lpData <> 0 Then
CopyMemory ByVal lpData, ByVal sData, Len(sData)
GlobalUnlock hMemHandle
EmptyClipboard
SetClipboardData m_cfHTMLClipFormat, hMemHandle
End If
End If
Call CloseClipboard
End If
End Sub
' Add the current selected item as Clipboard link
Sub AddOutlookItemAsClipboardLink()
Dim linkString As String
linkString = "{1}"
linkString = Replace(linkString, "{0}", ActiveExplorer.Selection.Item(1).EntryID)
linkString = Replace(linkString, "{1}", EncodeString(ActiveExplorer.Selection.Item(1).Subject()))
PutHTMLClipboard (linkString)
End Sub
' Encodes special characters to their entity equivalent
Function EncodeString(ByVal strOriginal) As String
Dim currChar, i, sOut, CharList
CharList = "óáéíúÁÉÍÓÚ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×÷ÀÂÃÄÅÆÇÈÊËÌÎÏÐÑÒÔÕÖØÙÛÜÝÞßàâãäåæçèêëìîïðñòôõöøùûüýþÿ"
sOut = strOriginal
For i = 1 To Len(CharList)
currChar = Mid(CharList, i, 1)
sOut = Replace(sOut, currChar, "&#x" & Hex(AscW(currChar)) & ";")
Next
EncodeString = sOut
End Function
In the basic form, it is possible to create OneNote note taking pages from an email or appointment in OneNote. This is nice, since I can simply bring up the context menu and choose "OneNote" in order to create a place for all my notes from this specific meeting (and also write down things to bring up before the meeting occurs so that I am prepared). I have however often run into the issue that a lot of scheduled meetings will be accompanied by separate emails containing information that is relevant. In some way I'd like to group these separate items together, preferably in the OneNote page that I have created for the meeting.
I looked at how OneNote creates a page for an email, which is in the same way as for an appointment. It will bring with it a few pieces of information and also create a link back to the email item in Outlook. This link is what I am interested in.
I looked into different ways of creating it. Most promising was a utility called Linker that can copy the internal ID of an Outlook item and put it in the clipboard. The flaw was that it prefixes the ID with "outlook:". This works natively for Outlook 2003. Outlook 2007 can handle it with a registry hack. Outlook 2010 will not handle it at all.
I at least got a bit closer to a solution.
I then found out that a proper prefix in Outlook 2010 is "onenote:outlook?folder=Contacts&entryid=". I looked into how to get the ID from the Outlook item via VBA code and it proved quite easy. I then hacked together a small Macro that would grab the ID from the currently selected item, create the correct HTML link with the prefix above, and then put it in the clipboard. This did of course not work.
The clipboard is an intricate piece of Windows that can handle a lot of different data. Putting raw text into it is simple, putting an HTML link into it a bit more difficult. I turned to Google and found a support article titled "How to add HTML code to the clipboard by using Visual Basic".
This helped me create the more intricate string used to identify HTML in Windows. Using this, I had it working.
After a while I noticed that special characters (å ä ö specifically since I'm in Sweden) did not work and got encoded in a wrong way. After trying different ways of encoding the string I finally resorted to try and put the entity name ä in the string and this worked! It is HTML I'm working with after all. A quick google gave me this nice piece of code to encode special characters in a string, and now I have it all working flawlessly.
My workflow now is as follows:
An appointment is made in Outlook, by me or someone else. I rightclick this and add a OneNote page with information regarding this meeting under a tab for the specific project. Those emails that are of interest for this meeting is then linked in the OneNote page using my Macro. I simply click the email in Outlook, hit my shortcut button to copy the link, and then paste it in OneNote. I have created the macro so that the link description is fetched from the subject in the email. Perfect!
I also noticed that the macro will create links to pretty much anything in Outlook. Emails, appointments, contacts and so on. This way I can link together all items in one OneNote page so I don't have to browse or search in Outlook for whatever I need.
Here is the complete code for the macro if you are interested:
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) _
As Long
Private Declare Function GlobalAlloc Lib "kernel32" ( _
ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function SetClipboardData Lib "user32" ( _
ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function RegisterClipboardFormat Lib "user32" Alias _
"RegisterClipboardFormatA" (ByVal lpString As String) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalUnlock Lib "kernel32" ( _
ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSource As Any, ByVal cbLength As Long)
Private Declare Function GetClipboardData Lib "user32" ( _
ByVal wFormat As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" ( _
ByVal lpData As Long) As Long
Private Const m_sDescription = _
"Version:1.0" & vbCrLf & _
"StartHTML:aaaaaaaaaa" & vbCrLf & _
"EndHTML:bbbbbbbbbb" & vbCrLf & _
"StartFragment:cccccccccc" & vbCrLf & _
"EndFragment:dddddddddd" & vbCrLf
Private m_cfHTMLClipFormat As Long
Function RegisterCF() As Long
'Register the HTML clipboard format
If (m_cfHTMLClipFormat = 0) Then
m_cfHTMLClipFormat = RegisterClipboardFormat("HTML Format")
End If
RegisterCF = m_cfHTMLClipFormat
End Function
Public Sub PutHTMLClipboard(sHtmlFragment As String, _
Optional sContextStart As String = "", _
Optional sContextEnd As String = "")
Dim sData As String
If RegisterCF = 0 Then Exit Sub
'Add the starting and ending tags for the HTML fragment
sContextStart = sContextStart & ""
sContextEnd = "" & sContextEnd
'Build the HTML given the description, the fragment and the context.
'And, replace the offset place holders in the description with values
'for the offsets of StartHMTL, EndHTML, StartFragment and EndFragment.
sData = m_sDescription & sContextStart & sHtmlFragment & sContextEnd
sData = Replace(sData, "aaaaaaaaaa", _
Format(Len(m_sDescription), "0000000000"))
sData = Replace(sData, "bbbbbbbbbb", Format(Len(sData), "0000000000"))
sData = Replace(sData, "cccccccccc", Format(Len(m_sDescription & _
sContextStart), "0000000000"))
sData = Replace(sData, "dddddddddd", Format(Len(m_sDescription & _
sContextStart & sHtmlFragment), "0000000000"))
'Add the HTML code to the clipboard
If CBool(OpenClipboard(0)) Then
Dim hMemHandle As Long, lpData As Long
hMemHandle = GlobalAlloc(0, Len(sData) + 10)
If CBool(hMemHandle) Then
lpData = GlobalLock(hMemHandle)
If lpData <> 0 Then
CopyMemory ByVal lpData, ByVal sData, Len(sData)
GlobalUnlock hMemHandle
EmptyClipboard
SetClipboardData m_cfHTMLClipFormat, hMemHandle
End If
End If
Call CloseClipboard
End If
End Sub
' Add the current selected item as Clipboard link
Sub AddOutlookItemAsClipboardLink()
Dim linkString As String
linkString = "{1}"
linkString = Replace(linkString, "{0}", ActiveExplorer.Selection.Item(1).EntryID)
linkString = Replace(linkString, "{1}", EncodeString(ActiveExplorer.Selection.Item(1).Subject()))
PutHTMLClipboard (linkString)
End Sub
' Encodes special characters to their entity equivalent
Function EncodeString(ByVal strOriginal) As String
Dim currChar, i, sOut, CharList
CharList = "óáéíúÁÉÍÓÚ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×÷ÀÂÃÄÅÆÇÈÊËÌÎÏÐÑÒÔÕÖØÙÛÜÝÞßàâãäåæçèêëìîïðñòôõöøùûüýþÿ"
sOut = strOriginal
For i = 1 To Len(CharList)
currChar = Mid(CharList, i, 1)
sOut = Replace(sOut, currChar, "&#x" & Hex(AscW(currChar)) & ";")
Next
EncodeString = sOut
End Function
Wednesday, October 12, 2011
Convert C# to PowerShell code
Yesterday I saw a link on our company's intranet linking to a colleague's blog. It proved to be a very nice read. Among the .Net Reflector addins available on CodePlex, there's one for PowerShell allowing you to convert C# code to PowerShell. That will come in handy a lot of times since I definately write C# code faster than PowerShell code. Thanks for that tip!
The only downside is that Reflector for the last year requires a license fee and I have since switched over to ILSpy. With this and the other plugins available, it's either time to switch over to Reflector again, or rewrite some of the plugins to work with ILSpy.
The only downside is that Reflector for the last year requires a license fee and I have since switched over to ILSpy. With this and the other plugins available, it's either time to switch over to Reflector again, or rewrite some of the plugins to work with ILSpy.
Wednesday, May 18, 2011
Dangers when upgrading the Codeplex SFTP adapter
I encountered some dangers that one can run into when upgrading the BizTalk SFTP adapter that can be found on Codeplex.
On one installation where the adapter is used did the issue of the adapter stop working frequently arise. When the issue was fixed in a subsequent version of the adapter, it was upgraded, but the problem remained.
When I looked at the server, I noticed that the adapter never had got upgraded, even though they very well installed the new version. I then replicated what I believe to be the steps taken during the upgrade in order to fully understand what had happened.
I learned that when upgrading version 1.3.3 to 1.4.0, it is necessary to completely uninstall the previous version before installing the new. This was never done during the upgrade process. The old version remained installed and the setup program for the new version reported back that the installation was successful. The BizTalk administrator never noticed the dual entries in the Add/Remove programs window nor the old timestamps in the adapter installation folder (which was how I noticed it from the beginning).
Uninstalling the two adapter entries in the control panel and then reinstalling the new version solved the issue as can be seen in the installation directory.
I then also noticed that when properly upgrading the adapter, receive locations bound to the SFTP adapter will not start unless you for each and every one open the adapter properties windows and save it. This is due to a difference in the properties that will make the bindings fail otherwise, rendering the port to shut down.
On one installation where the adapter is used did the issue of the adapter stop working frequently arise. When the issue was fixed in a subsequent version of the adapter, it was upgraded, but the problem remained.
When I looked at the server, I noticed that the adapter never had got upgraded, even though they very well installed the new version. I then replicated what I believe to be the steps taken during the upgrade in order to fully understand what had happened.
I learned that when upgrading version 1.3.3 to 1.4.0, it is necessary to completely uninstall the previous version before installing the new. This was never done during the upgrade process. The old version remained installed and the setup program for the new version reported back that the installation was successful. The BizTalk administrator never noticed the dual entries in the Add/Remove programs window nor the old timestamps in the adapter installation folder (which was how I noticed it from the beginning).
Uninstalling the two adapter entries in the control panel and then reinstalling the new version solved the issue as can be seen in the installation directory.
I then also noticed that when properly upgrading the adapter, receive locations bound to the SFTP adapter will not start unless you for each and every one open the adapter properties windows and save it. This is due to a difference in the properties that will make the bindings fail otherwise, rendering the port to shut down.
Friday, April 29, 2011
BizTalk 2010 and Dynamics AX 4.0
We just did a quick check for a client investigating the possibility to upgrade their BizTalk 2006 platform to the latest (2010) version. The main issue was whether the vast amount of integrations to their Ax 4.0 system would still work.
Some of the integrations are made using the Ax AIF adapter in BizTalk and while their is a lot of information on the web that doesn't say that BizTalk 2010 can work with the Ax 4.0 adapter, there is not a single document that explicitly says that the two are incompatible even though you more or less can assume so based on the information available. Mainly because BizTalk 2010 requires Windows Server 2008 and the adapter is not supported on this platform. The adapter is not supported on 64-bit systems at all and I doubt there is many admins looking at installing a fresh new integration platform today and not putting it on a 64-bit Windows.
We checked with Microsoft just to be sure and received the answer that BizTalk 2010 and Ax 4.0 cannot be integrated using the adapter. Not even Ax 2009 is fully supported by BizTalk 2010 as of today which is an interesting fact. Instead are we investigating the amount of work needed to change the adapter based flows to MSMQ integrations instead. The upgrade of the integration platform is more important than to keep an outdated adapter working..
Some of the integrations are made using the Ax AIF adapter in BizTalk and while their is a lot of information on the web that doesn't say that BizTalk 2010 can work with the Ax 4.0 adapter, there is not a single document that explicitly says that the two are incompatible even though you more or less can assume so based on the information available. Mainly because BizTalk 2010 requires Windows Server 2008 and the adapter is not supported on this platform. The adapter is not supported on 64-bit systems at all and I doubt there is many admins looking at installing a fresh new integration platform today and not putting it on a 64-bit Windows.
We checked with Microsoft just to be sure and received the answer that BizTalk 2010 and Ax 4.0 cannot be integrated using the adapter. Not even Ax 2009 is fully supported by BizTalk 2010 as of today which is an interesting fact. Instead are we investigating the amount of work needed to change the adapter based flows to MSMQ integrations instead. The upgrade of the integration platform is more important than to keep an outdated adapter working..
Wednesday, March 30, 2011
BizTalk 2010 certification
I've been waiting for a new exam for BizTalk to appear since the "current" one has been for 2006r2 for quite a while. Today I took a new look and found that Exam 70-595: Developing Business Process and Integration Solutions by Using Microsoft BizTalk Server 2010 is available. The old exams for 2006 and 2006r2 are set for retirement this summer.
Time to update the skills that I rarely (or never) use such as EDI and RFID and then it's off to take the test.
Time to update the skills that I rarely (or never) use such as EDI and RFID and then it's off to take the test.
Wednesday, January 19, 2011
Recycle IIS application pools using PowerShell
I have been working on a collection of deployment scripts for BizTalk solutions using PowerShell. One functionality is installation of services hosted in the IIS. After installation, the application pool need a restart in order to properly pick up the new/updated services.
At first, I just recycled all available application pools by the following command line
& $env:windir\system32\inetsrv\appcmd list apppools /xml | & $env:windir\system32\inetsrv\appcmd recycle apppools /in
First we list all available application pools and then we pipe this list as input into the recycle command.
While nice, it is unnecessary to recycle those application pools that are unaffected by our installation.
The main finesse in my scripts are that they are picking up all information on what to do from a configuration file. This XML file includes among other things the application pool for each service to install in IIS. Based on this I updated my recycle command to recycle each and every application pool that were specified in the configuration file. In order to not restart an application pool more than once, I pipe the foreach with sort-object and get-unique.
# List to hold the apppools
$appPools = New-Object System.Collections.ArrayList
# Loop through the objects in the xml file
# and extract the apppool name and add to the list
ForEach($wcfSetup in $xmlFile.DeployConfiguration.WcfSetups.WcfSetup)
{
[void]$appPools.Add($wcfSetup.ApplicationPool.Trim())
}
# Recycle each unique apppool in the list
foreach($appPool in $appPools | sort-object | get-unique)
{
& $env:windir\system32\inetsrv\appcmd recycle apppool /apppool.name:"$appPool"
}
At first, I just recycled all available application pools by the following command line
& $env:windir\system32\inetsrv\appcmd list apppools /xml | & $env:windir\system32\inetsrv\appcmd recycle apppools /in
First we list all available application pools and then we pipe this list as input into the recycle command.
While nice, it is unnecessary to recycle those application pools that are unaffected by our installation.
The main finesse in my scripts are that they are picking up all information on what to do from a configuration file. This XML file includes among other things the application pool for each service to install in IIS. Based on this I updated my recycle command to recycle each and every application pool that were specified in the configuration file. In order to not restart an application pool more than once, I pipe the foreach with sort-object and get-unique.
# List to hold the apppools
$appPools = New-Object System.Collections.ArrayList
# Loop through the objects in the xml file
# and extract the apppool name and add to the list
ForEach($wcfSetup in $xmlFile.DeployConfiguration.WcfSetups.WcfSetup)
{
[void]$appPools.Add($wcfSetup.ApplicationPool.Trim())
}
# Recycle each unique apppool in the list
foreach($appPool in $appPools | sort-object | get-unique)
{
& $env:windir\system32\inetsrv\appcmd recycle apppool /apppool.name:"$appPool"
}
Wednesday, December 1, 2010
Creating a multidimensional strongly typed array in Powershell
When getting stuck for a short while trying to figure out how to create a strongly typed multidimensional array in Powershell I tried to find an example on the net just to find that there simply just is no example of it to be found. Maybe it's too easy? The Technet page on the New-Object cmdlet gave me what I needed.
The way I later on created my two dimensional array was like this:
The way I later on created my two dimensional array was like this:
$d = New-Object 'Object[,]' 10, 20This is however created as an Object array while I needed int. I changed the code to this:
$d = New-Object 'Int32[,]' 10, 20Then I thought that it should be possible to streamline it a bit so I got this:
$d = Int32[,] 10, 20Simple enough. When I see it, I wonder why I couldn't figure it out quicker.
Tuesday, November 30, 2010
Recycle application pools using appcmd.exe in IIS 7
While hacking along on some deployment scripts today I encountered an issue when I needed to recycle specific application pools via code.
In IIS 7, appcmd.exe was added to enable a programmatic way of interacting with the IIS. On Technet, the syntax to recycle an app pool is as follows:
As an addition, application pools with spaces in their names can be recycled by simply wrapping the name in quotes as so:
In IIS 7, appcmd.exe was added to enable a programmatic way of interacting with the IIS. On Technet, the syntax to recycle an app pool is as follows:
appcmd recycle apppool /apppool.name: stringThis will however result in the following error message:
ERROR ( message:The attribute "apppool.name" is not supported in the current command usage. )Which can feel a bit strange. I then noticed that the article on Technet has a typo and the space between /apppool.name: and the name of the application pool should not be there. Without it the command will execute perfectly.
As an addition, application pools with spaces in their names can be recycled by simply wrapping the name in quotes as so:
appcmd recycle apppool /apppool.name:"My Application Pool Name"I added a comment about the issue in the Technet article for others with the same issue. I guess there is not a way to submit corrections to articles other than comments?
Friday, November 19, 2010
Calling stored procedures from BizTalk (and other applications) and the FMTONLY flag
Most developers will run into the issue of not being able to generate metadata from a stored procedure even though it is perfectly valid and can be accessed and run without a hitch from the SQL Server Management Studio or directly from code. A lot of the time there will be an error message on the lines of
In some cases there will be no error at all (SSIS is prone to this for example).
The above error message will give us something to go on though. When reviewing the stored procedure we can see that a temporary table is created and filled with data. Later on this table is used in a select in order to return data to the client. Nothing unusual, still it fails. The stored procedure looks like this (never mind the necessity of the temp table, it is just a demo of the issue):
The next step is then to run profiler during a run of the generate wizard to see what is actually happening in the background. How does the metadata get generated by the wizard and why does it fail?
When running the generation, we can see the following bits in the trace
Now we are getting somewhere. The FMTONLY setting is used in order to not process any rows but just return response metadata to the client. However, our stored procedure uses a temporary table which FMTONLY=ON will cause to not be created since any modifications are allowed to be made. When trying to do a select on the temporary table, it will fail since it never was created causing the error messages mentioned above.
There is a way around this issue though. Since we know what is happening, we can revert the execution of SET FMTONLY ON that the adapter does before the execution of the procedure. We should however not just add a command of SET FMTONLY OFF to the beginning of our procedure. Actually executing the entire procedure which such a solution will result in might not be a good choice. If we only do a select on data it is fine, but if the procedure also includes insert, update and delete statements, these will be called as well.
Instead, we check for the FMTONLY flag early on and if it is set, we switch it off when needed and then switch it back on again. Our modified and metadata-generation-secure procedure now look like this:
What magic is done here?
First, we declare a variable that can hold the current setting for the FMTONLY flag, we call it @FmtOnlyIsSet and set it to false by default.
The IF (1=0) bit may look a bit off, but is in fact quite clever. When FMTONLY is set to ON, all conditional statements will be ignored but the content will be executed. This since all possible return paths has to be checked. By checking for an impossible match (1=0) we can be sure that the statement inside the IF will be run only if FMTONLY is ON. Hence we set our FMTONLY flag to true here.
Then we simply check if our flag is set to true when needed and if so, switch off the FMTONLY setting. Afterwards we do the same check and switch it back on. This part is important due to what I mentioned above. If we don't switch the FMTONLY setting back on, all statements will be run just as in a normal execution of the procedure which might not be wanted.
It is noteworthy to know that the FMTONLY setting not only will be used during metadata generation in development, but also when actually calling the procedure from the application. I noticed this when using typed datasets in BizTalk server with the WCF-SQL adapter. I couldn't do alterations to the stored procedure which I handled by instead doing a mock procedure to generate schemas from. Then I assumed that I could safely call the original procedure from BizTalk but I still got the invalid object error message. A quick look in Profiler showed that the adapter will do two passes to the stored procedure. First once with FMTONLY set to ON and then one without to actually execute the code.
My guess is that the adapter is smart enough to do a check that the signature for the procedure matches the previously generated and deployed metadata before executing code that could change data in the database. If the returned dataset wouldn't match the schema, we would know before any code has been executed.
I have only seen this when using typed datasets though which make sense. By using the technique described above, it isn't an issue at all. I'd rather like the idea that the contract is checked first before executing the procedure. It is also not posing a performance hit. The result from the metadata extraction will be cached so only the first call will need to fetch metadata. I am still not sure for how long the metadata is cached before it is refreshed in a call but it seems to hold it for quite a while.
Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData'
Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData' ---> System.Data.SqlClient.SqlException: Invalid object name '#temp01'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
...
In some cases there will be no error at all (SSIS is prone to this for example).
The above error message will give us something to go on though. When reviewing the stored procedure we can see that a temporary table is created and filled with data. Later on this table is used in a select in order to return data to the client. Nothing unusual, still it fails. The stored procedure looks like this (never mind the necessity of the temp table, it is just a demo of the issue):
ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
SELECT t1, t2, t3 INTO #temp01 FROM Table_1
SELECT t1, t2, t3 FROM #temp01
RETURN
The next step is then to run profiler during a run of the generate wizard to see what is actually happening in the background. How does the metadata get generated by the wizard and why does it fail?
When running the generation, we can see the following bits in the trace
SET FMTONLY OFF; SET FMTONLY ON;
exec [dbo].[FetchTestData] @a4=NULL
SET FMTONLY OFF;
Now we are getting somewhere. The FMTONLY setting is used in order to not process any rows but just return response metadata to the client. However, our stored procedure uses a temporary table which FMTONLY=ON will cause to not be created since any modifications are allowed to be made. When trying to do a select on the temporary table, it will fail since it never was created causing the error messages mentioned above.
There is a way around this issue though. Since we know what is happening, we can revert the execution of SET FMTONLY ON that the adapter does before the execution of the procedure. We should however not just add a command of SET FMTONLY OFF to the beginning of our procedure. Actually executing the entire procedure which such a solution will result in might not be a good choice. If we only do a select on data it is fine, but if the procedure also includes insert, update and delete statements, these will be called as well.
Instead, we check for the FMTONLY flag early on and if it is set, we switch it off when needed and then switch it back on again. Our modified and metadata-generation-secure procedure now look like this:
ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
DECLARE @FmtOnlyIsSet bit = 0
IF (1=0) BEGIN SET @FmtOnlyIsSet = 1 END
IF @FmtOnlyIsSet = 1
SET FMTONLY OFF
SELECT t1, t2, t3 INTO #temp01 FROM Table_1
IF @FmtOnlyIsSet IS NULL
SET FMTONLY ON
SELECT t1, t2, t3 FROM #temp01
RETURN
What magic is done here?
First, we declare a variable that can hold the current setting for the FMTONLY flag, we call it @FmtOnlyIsSet and set it to false by default.
The IF (1=0) bit may look a bit off, but is in fact quite clever. When FMTONLY is set to ON, all conditional statements will be ignored but the content will be executed. This since all possible return paths has to be checked. By checking for an impossible match (1=0) we can be sure that the statement inside the IF will be run only if FMTONLY is ON. Hence we set our FMTONLY flag to true here.
Then we simply check if our flag is set to true when needed and if so, switch off the FMTONLY setting. Afterwards we do the same check and switch it back on. This part is important due to what I mentioned above. If we don't switch the FMTONLY setting back on, all statements will be run just as in a normal execution of the procedure which might not be wanted.
It is noteworthy to know that the FMTONLY setting not only will be used during metadata generation in development, but also when actually calling the procedure from the application. I noticed this when using typed datasets in BizTalk server with the WCF-SQL adapter. I couldn't do alterations to the stored procedure which I handled by instead doing a mock procedure to generate schemas from. Then I assumed that I could safely call the original procedure from BizTalk but I still got the invalid object error message. A quick look in Profiler showed that the adapter will do two passes to the stored procedure. First once with FMTONLY set to ON and then one without to actually execute the code.
My guess is that the adapter is smart enough to do a check that the signature for the procedure matches the previously generated and deployed metadata before executing code that could change data in the database. If the returned dataset wouldn't match the schema, we would know before any code has been executed.
I have only seen this when using typed datasets though which make sense. By using the technique described above, it isn't an issue at all. I'd rather like the idea that the contract is checked first before executing the procedure. It is also not posing a performance hit. The result from the metadata extraction will be cached so only the first call will need to fetch metadata. I am still not sure for how long the metadata is cached before it is refreshed in a call but it seems to hold it for quite a while.
Subscribe to:
Posts (Atom)