Tuesday, December 21, 2010

Subscribing/Pulling stored procedure optimisation. How to resolve a stall problem of the Advent Geneva Workflow Manager.

If you need to implement a custom queue process, one of the simplest solutions would be SQL Server table based solution. Especially, this is beneficial if there is a UI Control panel involved with capabilities to provide a real-time information about queue and dequeue progress.
BizTalk based Advent Geneva WorkFlow Manager (WFM) use a similar approach where the local SQL Server DB preserve information about WFM's activities in the "Activities" table. The only challenge is to prevent subscriber process from pulling the same record more then one time. This is why the "Status" field instoduced:  when subscriber pulls data, the flag(status) must be updated. Flag also must be a one of the conditions for pulling data select statement, so when subscriber pulls, it selects only "Open" records (Status = 'O'). As soon as it red it - status must be flipped to "Processed" (Status='P').
Here is how it would looks in T-SQL:
Declare @ID as int

Set @ID= (Select Top 1 [ID] From [Queue] Where [Status]='O')
Update  [Queue] Set [Status]='P' Where [ID]=@ID

Select * From Queue Where [ID]=@ID

At first, this approach looks solid. However, we have to remember that this queue table would be accessed by many processes simultaneously, so when subscriber try to read and update the same records at the same time - the SQL DB table immediately introduce a table lock. In the case of Advent Geneva WFM, all activities suddenly become frozen and the only quick fix in this situation is to restart subscription process(restart BizTalk host instances).
From my experience, at the time when I was part of New York Life Investments Geneva development team, our infrastructure team were slated  to restart BizTalk host instance 3-5 times per day. They actually had a scripting approach to do it (can you believe it?).
Finally, at some point, I was sick and tired of hearing about Geneva WFM stall problems, so, after spending some time on research, I found the way or resolving this problem.
The fix is really simple and at the same time very elegant. Please see below:
Here is the same statement, but written differently:

[ID] [int] NOT NULL,
[Status] [nchar](1)

UPDATE TOP (1) [Queue]
SET[Queue].[Status] = 'P'
WHERE [Queue].[Status] = 'O'

SELECT * FROM @Updated

As you see from the script, I took advantage from the SQL Server build-in INSERT, UPDATE triggers. So, instead of two separate actions (select first and then update selected) - the updated stored procedure have only one action : "to update  status". To output the result of a select query - I am using "INSERTED" trigger table.
As soon as we deployed modified script - all table locks disappeared and from that day on Geneva WorkFlow Manager was newer frozen, all activities were processed on time.

Tuesday, October 19, 2010

Add LinkedIn icon link in your Outlook 2007 email signature

If you really want to present yourself professionally, the very first step is to have a nice email signature. And you would expect that folks in Microsoft made your life easier with every new release on the Office products, such as MS Outlook 2007. This is true in most cases, but not in the case when you want to have a clickable image in your email signature.

A few days ego I decided to change the appearance on my email signature and add a link to my professional social network profile - LinkedIn - in a form of clickable image.
I was surprised when I realized that it is not really straightforward as it should be. All what Outlook is offering for signature design is an options to add just image or hyperlink with text. I searched Google and found a few posts with solutions - non of which works in my case for some reason, probably due to the fact that I have 64 bit
laptop with Windows 7 on it .

It was bugging me for a couple of days and, finally, yesterday, I was able to resolve this issue and build my ideal email signature:

Here is a detail instruction how to do it.
  1. Open Outlook and create your signature: Tools-Options-Mail format-Signatures-New

  2. Add all information which you have planned for your signature. Please note – If you want to have a clickable email address, you can do it by using "Insert Hyperlink" icon:

  3. Now your signature looks similar to this one:

  4. Before we go to the final steps, we need to get a LinkedIn icon. To get them you have to login to your LinkedIn account-Profile-Change public profile settings-customized buttons.
    Download any icon you like and save it locally, on your PC.
  5. Click "Picture" icon and navigate to the saved .png file which you just downloaded and click OK
  6. Save your signature and close Outlook. Just remember what name you assigned to it – in my case I used "Test"

  7. What we have right now is not what we want because the image is just an image and does not have hyperlink capability. Let's fix it
  8. We have to locate a source-file which Outlook use for this signature. In my case, all of my signature files located in the next folder:
    "C:\Users\AlexStar\AppData\Roaming\Microsoft\Signatures". If you not sure, just search for "Signatures" folder and select the one created for your User account.
  9. As you can see, Outlook created three files and one folder with the name similar to your signature name:

  10. We just need to modify two files: YourSignatureName.htm and YourSignatureName.rtf First let's change .htm file.

  11. Open YourSignatureName.thm file in the text editor (In my case I use Notepad++) . As you can see, Outlook created a lot of comments which you can easily erase

  12. Locate the line with a link to your image. In my case, it was this one:

    <img border=0 width=80 height=15 src="Test_files/image001.gif" v:shapes="_x0000_i1025">

  13. Change it to the following:
    <a href="your linkedin public profile URL"><img border=0 width=80 height=15 src=" Test_files/image001.gif " v:shapes="_x0000_i1025"></a>
  14. Save .htm file
  15. Go back to the "Signatures" folder and double click on your modified .htm file – it should be opened in the default browser
  16. Make sure that your linkedin image hyperlink works. Now copy all the text right from the browser window
  17. Double click on the .rtf file – you should see your signature information opened in the MS Word
  18. Overwrite all information in the .rtf file with the one you copied from the browser
  19. Save .rtf file
  20. Your custom signature is ready.