Free SQL 2008 R2 Training in Sydney

There is a SQL 2008 ½ a day training on the 29th of Jan – which is free offer from one of the consulting companies. Might be good if anyone would like to attend this..

Details:
Sydney SQL Server 2008 R2
Event Date: 29.01.10

WARDY IT Solutions is proud to announce that they will be delivering the first SQL Server 2008 R2 workshops in Australia.

This workshop is a free ½ day Hot Lap. A Hot Lap is a quick and concise way of finding out what is new in SQL Server 2008 R2 and how these changes will improve database operations, developer efficiency and enable self-service Business Intelligence solutions.

This workshop will also include a light breakfast and morning tea.

When: 8:00am – 12:00pm, Friday the 29th of January 2010.
Where: Microsoft Sydney, 1 Epping Rd, North Ryde NSW 2113

Happy learning

BizTalk: Debatch a SWIFT message using the Swift Accelerator

 I guess this appears to be quite a common requirement. We’re receiving message from our clients in the following format: 

Format: (MTS21 [MTxxx] $)*

 xxx = The MT format (eg: MT535, MT940, MT950, etc)
* = The sequence can repeat itself multiple times

 The incoming file/message can contain a mix match of different messages in the above formatted sequence.

Without diving into an elegant solution, I’ll demonstrate how to debatch a swift message into individual chunks (using the Swift disassembler). This would extract out MTxxx from the above set into your respective output directory.

 BizTalk Artifacts:

Pretty elaborative from the above structure (picture is worth a thousand words 8)  ) 

 Let’s explore the pipeline properties

The Message Header and Trailer Schemas were selected for the respective properties (marked in green). Note that I’ve changed the Fragmentation and Inbound Debatching to True. Also, BRE validation hasn’t been used for this example – however, this is an important property that SHOULD be explored in your final solution in one form or the other(also check out : A4SWIFT_Failed = true).
 

Schemas:

The above solution uses 6 schemas. With the exception of (MessageTrailer.xsd) all others are provided with the Swift Accelerator. I’ve attached the MessageTrailer.xsd with this solution. Download the file and save the content within it it as xsd (you owe me a coffee :) ).

Click here:  MessageTrailer.doc

 Once you deploy your solution, the incoming file would be debatched into individual chunks – magic  

Hope it helps.. Happy BizTalk-ing

BizTalk: Working with the Swift Accelerator 3.0

For simplicity sake, I’ll be using a single swift message to transform it into its respective XML using the Swift Accelerator.

 

Make sure BizTalk Accelerator for Swift 3.0 is installed with the appropriate message pack.

 

 

 

Development

1)     Add the following three references

a. Microsoft.Solutions.FinancialServices.SWIFT. PipelineComponents.SWIFTAsm.dll

b. Microsoft.Solutions.FinancialServices.SWIFT. PipelineComponents.SWIFTDasm.dll

c. Microsoft.Solutions.FinancialServices.SWIFT. RuntimeSchemas.dll

 

2)     Add the respective schemas (in this example MT535)

    

     Directory: C:\Program Files\Microsoft BizTalk Accelerator for SWIFT\SWIFT Messages\A4SWIFT 3.0

 

3)     Add the pipeline components
If the component doesn’t exist on your pipeline toolbox, Choose Items à BizTalk Pipeline components à Swift Disassembler.

 

 

4)     This is how my pipeline component looked like:

 

 

 

5)     Once the sample MT535 file was dropped for BizTalk to receive, the respective XML was produced.
Note: Before dropping in the file, validate the file against the MTxxx schema to pass successfully

6)     Receive location:

 

 

7)     Send port:

 

 

 

Hope it helps.. Happy coding J

BizTalk:Cannot locate document specification because multiple schemas matched the message type

Error:

While working with the Swift adapter, a common error encountered is as follows:

 <?xml version=”1.0″ encoding=”Windows-1252″?><SWIFT_ERROR MessageType=”"><ParseError ExceptionType=”" LineNumber=”0″ LinePosition=”0″ MultipleEmptyLines=”False” SchemaName=”http://schemas.microsoft.com/BizTalk/Solutions/ FinancialServices/SWIFT/SWIFTHeader#SWIFTHeader”><Message> Cannot locate document specification because multiple schemas matched the message type “http://schemas.microsoft.com/BizTalk/Solutions/FinancialServices/SWIFT/ SWIFTHeader#SWIFTHeader“. </Message></ParseError></SWIFT_ERROR>

 The reason is because the same schema has been deployed in another (BizTalk) application and the Swift disassembler errors out!

 Solution:

1. Remove / Move the schema to your current application
2. Optionally, Add a reference from your previous application to your current application (might require new deployment strategy – single gateway for all swift message/routing)

 I had the luxury with step 1 listed above.
Happy coding

BizTalk: Mapper Enhancements with BizTalk 2009 R2

In the recently concluded PDC 2009 in LA, there were some interesting sessions. The upcoming WCF and WF components will be named Windows AppFabric (former named “Dublin). Keeping all aside, there was an interesting talk about the roadmap for BizTalk. I was specially interested in the following upgrades:

   –> New mapper enhancements to make it easier to work with and understand complex maps
   –>  An out of the box FTPS adapter.
   –>  A new dashboard to apply and manage performance parameters
   –>  PowerShell based capabilities for management tasks
   –>  Updated SCOM object model to better reflect BizTalk artifacts

Let’s look at the mapper enhancements that were discussed during the session, with a series of screen shots

Maps cluttered (Original map)
 

Same map as above, now has Control bar at the top (zoom/search functionality). This is visually appealing as it displays links for the current pages, and also keep links from the other pages (grayed out). This really shows me what is happening with my whole map, irrespective of which page I would be in.

 

 -         Clicking on a node would highlight the related associations (Reduce clutter)
 

 -         Click on the map node or functoid,  and the map would scroll itself to the respective nodes (called the Auto scroll)

-         Search for tags within the map: “Loop” would highlight the filtered nodes and functoids
-         Move functoid from one page to the other. (Would be great to copy and paste between multiple maps!! – not available for BTS 2009 R2)

 -         Mapping is available for Data contracts and schemas. Once the map is ready, we could right click on the map and create a workflow activity. This would generate .NET code

 Adding hosted workflows in BizTalk

Workflow templates available within a BizTalk project (Add new item):

1)     Code activity

2)     Activity

3)     WCF Workflow Service

4)     Activity Designer

Choosing between workflow host and BizTalk orchestration is about low latency. The first case would skip the message box – process the request and provide the response directly. Factors to consider here would be around scale out, transaction costs, etc

 Video link:  Click here.

Happy learning

What is sharepoint ? High level introduction ..

Microsoft has divided the SharePoint capabilities into 8 categories (drawn below). Using these capabilities customers have implemented projects from collaboration to business intelligence (case studies). 

Videos

1) SharePoint in Plain English
2) SharePoint Intro 

 

Capabilities

Details

Top 10 benefits: Click here
Case Studies     : Click here 

 

Further Info

To analyse it further, here is a good site that demonstrates SharePoint by audience type – specially how to automate a workflow process using SharePoint. For the developer audience, click here

Hope it gives you a good overview on sharepoint..

Happy learning

SQL: Search in a list of comma seperated values (CSV)

Often you’d be given a list of reference Ids, and you would like to pass these Ids to a stored procedure, to retrieve the records.

The easiest way is to create a function that would return a table and then use this function. This can be used from your stored procedure to filter the required ..

Here is the script for the handy function .. 

CREATE FUNCTION [dbo].[ListToTable]
(@list varchar(7000))
   RETURNS @tbl TABLE (number int NULL) AS
   BEGIN
         DECLARE @pos   int,    
         @nextpos  int,          
         @valuelen   int
   SELECT @pos = 0, @nextpos = 1
   WHILE @nextpos > 0
   BEGIN
     
     SELECT @nextpos =  charindex(‘,’, @list, @pos + 1)     
     SELECT @valuelen = CASE WHEN  @nextpos > 0               
                             THEN @nextpos                             
                        ELSE len(@list) + 1                        
                        END - @pos - 1  
     INSERT @tbl (number)     
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))     
     SELECT @pos = @nextpos
   END
  RETURN
END

 This is how to use it within a stored proc:  

DECLARE @receivedIds AS VARCHAR(MAX)
SET @receivedIds = ‘12,13,16,18,20,65,91′ — Wud be a incoming param
SELECT number FROM 
   dbo.IntListToTbl (@receivedIds)

  

Happy coding  

SQL: Delete vs Truncate table

Having a small issue with a stored proc executing a “Delete FROM tablename” statement against a (temp) table containing a huge number of rows. In this instance a “truncate table tablename” would have suited better.

From the MSDN documentation: 
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

• Less transaction log space is used.
  The DELETE statement removes rows one at a time and records an entry
  in the transaction log for each deleted row. TRUNCATE TABLE removes the
  data by deallocating the data pages used to store the table data and
  records only the page deallocations in the transaction log.
  

• Fewer locks are typically used.
  When the DELETE statement is executed using a row lock, each row in
  the table is locked for deletion. TRUNCATE TABLE always locks the
  table and page but not each row.

• Without exception, zero pages are left in the table.
  After a DELETE statement is executed, the table can still contain
  empty pages.For example, empty pages in a heap cannot be deallocated
  without at least an exclusive (LCK_M_X) table lock. If the
  delete operation does not use a table lock, the table (heap) will
  contain many empty pages. For indexes, the delete operation can leave
  empty pages behind, although these pages will be deallocated quickly
  by a background cleanup process.

 

 Extrapolating on this – if your intention is to simply remove all records from a table, without the need for a rollback – truncate table is significantly more performant.

Happy terminating.. err – truncating

VS: Enable Ankh Add-in for VS 2008 solution

Post updated : Please check out the comments to download the installer..

1) Close all instances of VS 2008

2) Download the following file and copy the contents into notepad
    File:  RegFile

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\AddIns\Ankh]
“SatelliteDllPath”=”C:\\Program Files\\AnkhSVN\\“
“LoadBehavior”=dword:00000001
“CommandPreload”=dword:00000001
“AboutBoxIcon”=”"
“CommandLineSafe”=dword:00000000
“Description”=”Subversion addin for Visual Studio .NET 2008″
“FriendlyName”=”AnkhSVN”
“SatelliteDllName”=”BitmapDll.dll”
“AboutBoxDetails”=”Please visit http://ankhsvn.tigris.org for more information
Below is the version information for the various AnkhSVN components
AnkhSVN 1.0.2.2778
OpenSSL 0.9.8a
Neon 0.25.5
ZLib 1.2.3
Berkeley DB 4.4.20
Subversion 1.4.5
Ankh 1.0.2Final
apr-iconv 0.9.7
apr-util 0.9.12
apr 0.9.12″
“LoadBehavior”=dword:00000001

 

3) Save the file with .reg extention

4)  Execute the saved file à This shall add an entry to your registry

5)  Open VS 2008 and Ankh should be enabled for your solution.

 Happy coding

Entity framework: The learning guide

Here is a good learning guide on the Entity framework. All credit to my namesake Zeeshan. Good work mate

Learning guide: Click here (pdf)
Source code: Click here (source)

Happy learning