Sampe Script to Create Varchar(Max) from Adventure Works DB

DROP TABLE dbo.SQLServerLog;

create table dbo.SQLServerLog
SQLLogID Int Identity(1,1),
SQLLog varchar(max)
Insert into dbo.SQLServerLog
”” + Convert(Varchar(max),DatabaseLogID) +
”” + ‘,’ + ”” + DatabaseUser +
”” + ‘,’ + ”” + [Event] +
”” + ‘,’ + ”” + [Schema] +
”” + ‘,’ + ”” + [Object] +
”” + ‘,’ + ”” + [TSQL] +
”” + ‘,’ + ”” + Convert(varchar(max),XmlEvent) + ””
FROM [AdventureWorks2012].[dbo].[DatabaseLog]
Where DataLength(XMLEvent) > 8000
–Order by 1 desc

Select * from dbo.SQLServerLog

Advertisements 2016 Rookie of the Year

Feeling exhilarated in receiving the award from Thanks MSSQLTips for the recognition.

This has fuelled my passion for writing for the community.


Why do I need to learn PowerShell now?

I am well settled and very happy with my day-to-day job, So why do I care about learning PowerShell script ?  This is the thought I had in my mind few years back. However a SQLBits session unleashed the potential of PowerShell. Since then I am learning and using PowerShell a lot. The PowerShell knowledge enable me to automate all activities related to deployment and helped me to achieve the Continuous Integration in the early stages.
So if you are like minded person and asking the same question then read further. I have highlighted the areas where I have seen tremendous benefits of PowerShell.

1)    Deployment automation for Microsoft BI
PowerShell will help you to access the functionality of all Microsoft Business Intelligence applications (SSIS, SSAS and SSRS). You can access SQL server to deploy new tables or you can execute a SSIS package.


2)    Microsoft’s recommended way for automation
Microsoft has got a good support for PowerShell. It is the standard and recommended automation tool. PowerShell can be used for Windows Server, SharePoint, Office  365, Azure , SSIS, SSAS and SSRS. PowerShell is the first choice to automate the configuration and management activities.


3)    PowerShell Automation is a key skill
If you are managing a release for many servers at the same time, then many things could go wrong. Now stay cool with PowerShell script automation. This will reduce manual errors and increase the productivity within the team. It is a key skill for release managers and DevOps consultants.


4)    Greater support for DevOps / Continuous Integration tools
Most of the popular tools in DevOps (Chef, Puppet, Jenkins, Bamboo, TeamCity,etc)  does support PowerShell. So you can leverage the PowerShell to support for Build and deployment automation.


5)    Easy to learn
If you are familiar with .net framework, then you will be able to learn PowerShell quickly, as the PowerShell has been developed based on .NET.
So, what are you waiting for? Start your PowerShell journey today.


Learn PowerShell basics here
Bit lazy to read ? You can watch a video to gear up your skill here
Super excited and want to use PS in your day to day activities?
Deploy your SSIS projects with the help of PowerShell
Enable continuous delivery for SSRS

Continuous Integration (CI) Tools


After my last blog post, I have got very good response from individuals and organisations for help. Based on their request, I have shortlisted and booked few appointments to help them.

Last week, I had a session with an individual to help him to implement continuous integration for their Data Warehouse. I have discussed about the possible options and recommended few approaches to proceed. I am glad, I was able to share my experience and help them to take the first step.

During the session we have discussed about various CI tools available in the market and thought I will provide a little blog post to help others as well.

Continue reading “Continuous Integration (CI) Tools”

Giving back to the community & Free Goodies

I have learned a lot from the community and it’s my turn to give back to the community now. I am available (free of cost) for 30 mins consultation for each week.
Where can I help ?

I worked on these areas extensively and I would like to offer help.
1)    Continuous Integration / Continuous Delivery for BI applications
2)    Deployment automation
3)    Building test framework

How does it work ?
You can contact me on using the “Contact me”  page on my blog. I recommend you to write few lines about the problem you are having in your organisation. This will enable me to understand whether I can be of any help. If I can help, then I will let you know the date and time for a 30 min call.

During this call, I will share my thoughts and may propose a solution. This may not resolve all your issues. But at least it will give you a direction to proceed.
After the call, I will write a blog post about the problem and the suggested solution. This would help others, if they come across the same issue.

Few rules:
1)    Provide an official mail id for communication. I am committed and serious about this initiative and I expect the same from you
2)    Time duration is strictly 30 min
3)    No training or Career counselling during this time

Null – Special Data Value

What is NULL ?

Null denotes that a data value which is not exist in the database. The term “NULL” has been coined by E.F.Codd.

Null usually represent Inapplicable or Missing information in the system. In SQL Server NULL is a keyword/reserved word.


Is Null as same as Zero ?

No, Missing or inapplicable information can’t be assumed as Zero value.


The price of a product can be a numeric  value or “Don’t know the price”. If we don’t know the value of price then it will be classified as null.


NULL Keyword

SQL server uses the keyword NULL to differentiate among other data values.


A simple example to understand about null


SQL  Code Snippet:

Declare @tabEmployee table (ID int, Namenvarchar(255), Department nvarchar(50), Salarydecimal(18,2))

 Insert into @tabEmployee (ID,Name,Department,Salary)

values (1000,’Sam’,’Sales’,50000),




Select ID ,Name,Department,Salary from@tabEmployee


Null for numeric data type:


It has been observed that we don’t have the department information about the employee Mark. Hence a null has been presented to denote the missing information.


Null for string data type:


As we don’t know the  Department name for the employee Tom, it has been represented as NULL.


How to compare null value ?

As null is a special data value , it is not possible to compare using the equal sign.


Hence we need a special operator to handle values with NULL. The “IS NULL” operator will be useful to compare against the NULL data values.


The operator “IS NULL” can be used for string data types as well


Is zero length string  or blank value is as same as Null ?

No,  The below mentioned query and result set confirms the fact that only nullable values can be compared using “IS NULL”.