One of the things I noticed at the last SQL Server Sri Lanka User Group meeting was, unawareness of IMPLICIT Transaction mode. Almost all were knew about AutoCommit and EXPLICIT modes but very few were aware of IMPLICIT mode. Are you aware of it? Then how often you use it?

The Difference between IMPLICT and EXPLICT is simple. EXPLICIT mode allows us to start the transaction wherever we want by using BEGIN TRAN statement. In IMPLICIT mode, the transaction automatically starts base on some T-SQL statements. Some of the statements are CREATE, INSERT, UPDATE, DELETE, ALTER TABLE. See the BOL for whole list. As EXPLICIT mode, either COMMIT or ROLLBACK is required to complete the transaction.

   1: USE Northwind

   2: GO

   3:  

   4: SET IMPLICIT_TRANSACTIONS ON

   5: SET XACT_ABORT ON

   6:  

   7: CREATE TABLE dbo.EmployeeAllowances

   8:     (EmployeeId int REFERENCES dbo.Employees(EmployeeID) NOT NULL,

   9:     MonthAndYear datetime NOT NULL,

  10:     Allowance money NOT NULL)

  11:  

  12:  

  13: INSERT INTO dbo.EmployeeAllowances

  14:     (EmployeeId, MonthAndYear, Allowance)

  15: VALUES

  16:     (100, getdate(), 450.00)

  17:  

  18:  

  19: COMMIT TRAN

The SET IMPLICIT_TRANSACTIONS ON tells the system to set the transaction mode to IMPLICIT. In above code, transaction starts with CREATE TABLE statement. Since I have turned XACT_ABORT on, transaction rolls back at any run-time error. If no run-time error, transaction is committed at COMMIT statement. In this case, everything is rolled back since INSERT statement raises an error.

It went okay. Chamindu's session was quite impressive. Though I have done some testing about SSIS custom components, I had no chance to implement a one for one of our applications. One thing I noted is, it is simple and very powerful.

In my presentation, one of my samples didn't work, because of the funny reason :(. I think that I have used a open-connection that had set to different isolation level, and tried to get a result-set related to another isolation level. I couldn't figure it out at that time, time might be reason because I had taken extra 15 minutes (according to Gogula :)). My presentation and samples (updated) are available for download, whoever wish to see Implementing Transaction with SQL Server 2005, visit this url.

It was part of my MSc stuff. I had had to write a bit-complex module using Java for one of the subjects and I spent couple of sleepless nights for completing it. I have not done any programming with either .NET or java for long time and unfortunately I had to use something called "BlueJ" was coding, that is not rich enough like Visual Studio :). In a way, it was fun, writing code without help from the system, (assume you are coding without intelli-scene) but only thing was, it took time that I expected.

SQL Server Sri Lanka User Group meeting will be held on 18th (tomorrow) at Microsoft Sri Lanka. As usual, there are two sessions; Once will be done by a new speaker, Chamindu Munasinghe who is a tech-lead at Eurocentre DDC. Chamindu will be demonstrating one of the important areas in the SSIS; Custom Components. I will be doing the second one that will be focused on "SQL Transactions". For more info, please visit SQL Server Universe.

I believe that these two topics are really important to both DBAs and DBEs. Hope you will be joining with us, for learning and for sharing.

SQL Server 2005 BPA July one is available for download at here. If you want to know whether you have configured the server properly and need some recommendations, this is the best tool for it.

Busy as bee :); Full of works on MSc, Full of works @ my office. In addition to that, training for different SQL area are required by various companies. Doing all these things parallel is bit difficult but have no other options. As a result, regular blog update was interrupted. I may be running like this for another two weeks :(?

Some of things, I wanted to add to my blog are;

3rd SQL Server Sri Lanka User Group meeting
It was held on June 20th. It was okay but I expected an improvement of the attendance but it was same as last time even though couple of new faces were there. Joy did a presentation regarding SQL Server Reporting Services and Gogula did one on Business Intelligence. I took 5-10 minutes to introduce the latest version of Red Gate SQLBackup tool. For more info, visit http://sqlserveruniverse.com

Capabilities of SQL Server 2005
Microsoft Sri Lanka had organized one day session that showed the capabilities of SQL Server 2005. I was invited to run the session and it was demonstrated to the DBAs in Sri Lanka Telecom. I enjoyed doing it too because they are Oracle experts and had a chance to see the differences between two products in various scenarios. One of the Microsoft consultants Manaphan Huntrakoon also participated for the session. He demonstrated a very valuable presentation that spoke about both Orcale and SQL Server 2005 components, comparing the way of they have been implemented. He is an expert on both SQL Server and Oracle. I wish I could be DBA who knows both DBMSs.

One of the limitations in SSISTask delayed my work flow
If it is unknown to you, it is time to know about it. One of the SQLTask in my SSIS package required a dynamic query and was supposed to be implemented with expression. But I immediately found that the expression cannot be exceeded more than 4000 characters and made me to split my task into too. Do not know whether it has been increased in 2008.