Installing SQL Server 2016 Developer Edition and trying out Telco Customer Churn with R services.

During SQL Server Geeks Annual Summit 2016, #SSGAS2016 I was really impressed by Wee Hong Tok for his session on ‘SQL Server R services’ where I got to know more about SQL server 2016 and R services and the demo he presented in that particular session regarding Telco Customer Churn.

Also, another session that I am really impressed by Jen Stirrup for her session on ‘Delivering Practical Analytics and Results with Cortana Analytics’.  And also impressed by Andreas Wolter  Amit Bansal ,Other Speakers & SQL Server Geeks Community for such a friendliness and hospitality.

Since, I also work in Telco sector so first thing I want to try it out is what Wee Hong Tok had shown in his demo and thanks to Jen Stirrup where she introduce me with Cortana Analytics, I don’t know much of this SQL Server 2016 with R services and regarding Cortana Analytics now I want to know more on these.

One can find regarding Cortana Analytics and Telco Customer Churn from below links.

So, if you also want to try out this then all you need is firstly download the SQL Server 2016 Developer Edition or SQL Server 2016 Evaluation version for 180 Days.

For step by step installation you can follow below link

Before installation SQL Server 2016, you need to install Java Development Tool kit.

Also, don’t forget to check the R Services (In-Database) tab during installation, also there is separate R Server (Standalone) during installation just prefer R Services ( In-Database) option.


You can find more on R Services (In-database) and R Service (Standalone) from below links.

Once installation compete, in order to test Telco Customer Churn just go to below link to get the backup file for Telco Customer churn in GitHub link below,

Get the teloedw2.bak file and restore in SQL Server 2016 database, now read the file which contains:


Restore the database provided (telcoedw2.bak)

Run the code in TelcoChurn-Main.sql


TelcoChurn-Main.sql – Use this T-SQL script to try out the telco customer churn example.

TelcoChurn-Operationalize.sql – T-SQL scripts to create the stored procedures used in this example.

while going through above scripts and trying out Customer Churn I got few errors, so I am sharing my finding here, anyone if gets same error then this post might be helpful.

After installing SQL Server 2016 developer edition and trying for this Telco Customer churn SQL server scripts. I got certain error while running scripts TelcoChurn-Main.sql and TelcoChurn-Operationalize.sql. For first, after google I found out we need to change the Memory_Limit_percent in order to run above sq. scripts. So, just add MEMORY_LIMIT_PERCENT=50 into the config file for Rlauncher.config which can be found in below location:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Change as below;


While running this script TelcoChurn-Operationalize.sql we need to firstly install the required R packages, I have also uploaded one SQL File which installed required R packages before running the scripts of telco customer churn into my folk repository for sql-server-samples. The file contains enabling sp_execute_external_script to run R scripts in SQL Server 2016 and installing required R packages in order to run the Script TelcoChurn-Main.sql and TelcoChurn-Operationalize.sql successfully. Since we need to install those R packages before we run the scripts in order to avoid the error.

After that all the scripts run successfully and now you can learn more about R Services and R scripts & algorithm used and then test along with your own Telco data. Which I am planning to try out and will try out for sure.

There is always something new to learn into the world of DATA so called BIG DATA & now DATA Science/Analytics and Machine Learning.


Anil Maharjan

Speaking at SQL Server Geeks Annual Summit 2016, Asia’s Only Data & Analytics Conference.

Firstly I am happy and excited that I will be speaking at SQL Server Geeks Annual Summit 2016, Asia’s Only Data & Analytics Conference. I have been selected as a Speaker from Nepal and am representing as only one speaker from Nepal.

About the Conference:

SQLServerGeeks Annual Summit 2016 (SSGAS 2016) is Asia’s Only SQL Conference focusing on Microsoft Data. Scheduled from Aug 11-13 (Pre-Con on Aug 10) at NIMHANS, Bangalore, the summit will see 120+ sessions being delivered by 50+ speakers across 3 days. Joseph Sirosh (CVP, Data Group) will key-note the conference. Complete MS Data Platform stack is being covered at the summit. Speakers include Group PMs, Senior PMs, PMs, Premiere Field Engineers, Escalation Engineers & Data Architects from MTC. SQL CAT, SQL TIGER & Global Black Belt Team from Microsoft will deliver top-notch content and spend quality time with attendees at the convention center.

More details are here:

I am Speaking

Why one should attend this conference:

  • To get real-world training from industry experts
  • Know the latest trends in Data & Analytics world
  • Special focus on Analytics, Cloud & Big Data
  • To network & connect with the MVPs, MCMs
  • To learn from SQL product team, Redmond
  • Direct access to product team members
  • Benefit from new delivery formats like Open-Talks & Chalk-Talks*
  • Expert level demo-oriented sessions
  • Five parallel full-day classroom training

Please do register and be a part of this great event and follow #SSGAS2016 on Twitter for more news.

Hope to see you there and don’t forget to say hello to me in the event.


Anil Maharjan

Senior BI Engineer

Watch 24 Hours of PASS Business Analytics sessions if you had missed any.

From yesterday evening to till today morning I had a great time attending the ‘24 Hour of PASS Business Analytics’ free live 12 back-to-back webcasts from various BA Conference speakers.

If you have missed out to join the live sessions on 5th Feb 2014th which had begin at 08:00PT / 11:00 ET / 16:00 GMT, then no worries the recorded webcasts sessions is available now and you can just check out those great session and am sure you will get some great knowledge in the world of BA from a great speakers around the world.

You will be able to know the story behind your own DATA through these great sessions.

Also, there is a contest #pass24Hop Challenge organized for this event, where anyone can take part in the challenges and if you are lucky enough and passionate about DATA and BI then you could get a chance to win a free ticket to2014 PASS Business Analytics Conference which is going to be held on San Jose, CA from May 7-9. I think this event will be a great success and will bring more new challenges and opportunity in the world of BI, BA. Wish I could also attend this event  🙂 !!!


Anil Maharjan

Some Question and knowledge learn from PASS Summit 2013 Preview

The session’s was really great and so do the Speakers . Learn a lot off stuffs and knowledge from 24 hours for PASS SUMMIT Preview.

Here are some of the question I have asked so far and by doing some research on that I found some answers as:

1.Power view loads data in Memory, so how big data set can it load and will it be significant to work/analysis as similar to multidimensional OLAP cube analysis.?

: I could not get the clear answer of this one but I think Power view can’t be used as significant as SSAS providing .

2.Will SQL server going to handle the BIG data as similar like Hadoop and others.

Since they are quite different  product I know, Microsoft Big Data offers an integrated platform for managing data of any type or size, whether it is structured data from a relational data warehouse such as SQL Server 2012 Parallel Data Warehouse, unstructured data on Hadoop or streaming data.

PolyBase is a breakthrough new technology on the data processing engine in SQL Server 2012 Parallel Data Warehouse designed as the simplest way to combine non-relational data and traditional relational data in your analysis. So, MSSQL 2012 focusing  for Big DATA , structured/ Unstructured data as like Hadoop currently emerging.

3.Can Power view used to analyze the Multidimensional OLAP cube?

I think this feature is released in SQL Server 2012 SP1 CU 4 and still not works in Excel 2013 if I am not wrong.

4.Is it better to use 50-60 left join from almost 50 tables having cluster index to populate a single fact table or we can use some intermediate table using 20-20-20 joins and lastly join those tables to populate a single fact table.

: still waiting for answer

5.what will be the performance issue while replication AlwaysON High Availability and how does it sync with primary to secondary .

:This is nice feature I notice about in SQL 2012.

6.what will be better approach vertical partitioning or horizontal partitioning in SQL 2012.

:Vertical partitioning since horizontal partitioning is quite complex in SQL 2012 as per speaker.

7.Does Power view and Power Map only support Bing maps, can’t we use a Google maps.

:No, we cannot use Google maps for now.

These are some of the question I have asked so far, and the answers may not be quite enough or correct.

I hope someone knows better answer for these question too so, if you have any please share some.

That will surely helps us and wish I could attend the PASS Summit 2013 that is going to held on

Charlotte, NC: October 15-18, 2013 .


Anil Maharjan

Think while Rename a Column Name of Table in SQL.

If someone tries to rename the column of table then we write a query as

SP_RENAME  'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'
--we use as
SP_RENAME  'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN'
SP_RENAME  'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN'
which gives the column the name [CustomerName], not CustomerName.
For rename the column

You don’t use the [] escape in the second argument but by mistakenly if we use ‘[CustomerName]’ as new column name instead of CustomerName.

It sucessfully updates the column but while we select that column it’s now gives the error as

"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong"

Also while we try to drop the column itself we cannot delete it. It also gives error as

ALTER TABLE DROP COLUMN failed because column '[CustomerName]' does not exist in table 'Customer'.

Does that mean we have to recreate the customer table since the renamed column [CustomerName] cannot use in select statement too.

Well there is a method for changing the mistakenly rename column ‘[CustomerName]’ to CustomerName . Just simple you need to go to the table DimCustomer and then click plus sign of the table and then you can see the list of columns of DimCustomer table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[CustomerName]’ to CustomerName . This will solve the issue we are having.


If someone by mistakenly rename the column as above and getting similar issue this post will be surely helpful.

Also, there might be any alternative T-SQL method then please suggest some idea that will surely helpful.


Anil Maharjan

Date Range for SQL and Oracle.

Hello all,

This will be my memorable blog post for this year. Since, this is my last blog post for this year acc to Nepali year.We are celebrating happy new year(01/01/2070 B.S) day after tomorrow :). so, want to wish you all happy Nepali new year.

This blog is related to the Date Range. While working I need to generate the date range for Oracle side date range from start date to enddate(i.e. 2010-01-01 to 2012-01-01) . Though it’s easy for me in SQL side but in oracle side I have to do some research for this since I am not an Oracle guy.

I think there might be different alternative way for this but If you want to generate the date range from startdate to enddate, then you can simply run the script as below.

For SQL Date Range script:

--startdate: 2010-01-01 enddate :2012-01-01
CREATE TABLE [dbo].[DateRange](
	DateRange DATE
DECLARE @StartDate Date;
DECLARE @EndDate Date;
SET @StartDate='2010-01-01'
SET @EndDate='2012-01-01'
WHILE (@StartDate<@EndDate)
 SELECT @StartDate = DATEADD(month,1,@StartDate)--for month wise increment
 --SELECT @StartDate = DATEADD(year,1,@StartDate)--for year wise 
-- SELECT @StartDate = DATEADD(day,1,@StartDate) --for day wise
--SELECT * FROM DateRange

For Oracle Date Range Script:

   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
   WHEN does_not_exist
  CREATE TABLE DateRange (
  DateRange DATE 
    vblStartdate DATE;
    vblEnddate DATE;
    vblStartdate := To_Date('2010-01-01','YYYY-MM-DD');
    vblEnddate := To_Date('2012-01-01','YYYY-MM-DD');

    WHILE (vblStartDate <vblEndDate) 
EXECUTE IMMEDIATE ('SELECT Add_Months('''||vblStartDate||''',1) FROM DUAL')INTO  vblStartDate;
--select * from DateRange


DateRange -- month wise increment from startdate to enddate

I think this post will be helpful for some of us who is looking for such date range.

Once again Happy Nepali New YEAR 🙂 !!!


Anil Maharjan

SQL PASS Summit 2012 Hot News !!!

Hello all,

Some Hot news of the SQL PASS SUMMIT 2012.

PASS Summit is the world’s largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals.

Organized by and for SQL Server and BI users, PASS Summit delivers the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world.

The 3 most awaited days from Nov 7-9 are here for all the SQL and BI lovers around the world.

Currently, I couldn’t attend this session but I wish to attend this session in upcoming days or in near future summit’s a long run way to go .No worries, PASS Live TV is here for us who couldn’t make it to this SUMMIT in Seattle. The live covering of the SUMMIT can be browsed through the link below.

Also, it’s nice to hear the upcoming project news from the Summit Keynotes .Apart from that ,I have read few blogs regarding the Summit Keynotes  from the great persons and want to share with you guys too .So that one can know what’s in upcoming future technologies and updates in SQL and BI field mainly.

These are some most highlighted upcoming updates in our field. You can have a look on these from the great writer blogs ( By Chris ,Marco and Denny ).

  • Project Hekaton: Project Hekaton,codename for in-memory OLTP technology with improvement in performance and scalability.
  • Polybase : Polybase allows you to query data from an external source from within the SQL Server 2012 PDW.  This will allow you to create an external table which simply links to a Hadoop cluster.By using Polybase a single T-SQL query will run queries across relational data and Hadoop data. A single query language for both. Sounds really interesting for using BigData in a more integrated way with existing relational databases.

By Chris “Wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL?”Really looking forward to see these kinds of upcoming project by Microsoft SQL Server Team.

  • Updates on Columnstore: In the next major release of SQL Server the columnstore indexes will be updatable and it will be possible to create a clustered index with Columnstore index. This is really a great news for near real-time reporting needs.
  • Power View works with Multidimensional cube
  • Microsoft HDInsight Server CTP which is Microsoft’s Hadoop offering
  • SQL Server 2012 SP1 and Office 2013
  • Mobile BI and more

You can have a look in the links below for more details .

Just want to share the updates with you guys.we will be looking forward to play around with these great products and features from Microsoft.


Anil Maharjan

Free download Microsoft Press eBook: Microsoft SQL Server 2012 Pocket Consultant.

Hello all,

I just want to share a link to download a free copy of Microsoft Press eBook ‘Microsoft SQL Server 2012 Pocket Consultant’.
This could be a great eBook for you to learn all about how to manage SQL Server .

Currently, Microsoft Press just posted the first  two chapters within this free copy as Managing SQL Servers and Policy-Based Management.

So, SQL Geeks !!! Grab your digital copy and learn all about how to manage SQL Server!

I have already downloaded and have started to read and learn the things.Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 and to manage SQL Server.

Contents at a Glance

Chapter 1: Managing SQL Servers 

  • Using SQL Server Management Studio
  • Managing SQL Server Groups
  • Managing Servers
  • Using Windows Power Shell for SQL Server Management
  • Starting, Stopping, and Configuring SQL Server Agent
  • Starting, Stopping, and Configuring MSDTC
  • Managing SQL Server Startup
  • Managing Server Activity

Chapter 2: Implementing Policy-Based Management

  • Introducing Policy-Based Management
  • Working with Policy-Based Management
  • Configuring Central Management Servers
  • Managing Policies Throughout the Enterprise


Anil Maharjan

How can we rollback mistakenly UPDATE statement change in SQL Database.?

Hello all,

Beside than my regular helpful blog posts, it’s just a discussion type blog post that might be useful to all of us.

Let’s say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

* backup the transaction log

* restore the database from a full backup

* restore all transaction logs (in sequence) to the time right before the accident occurred.

Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven’t tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below:

So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012  then really want to here.


Anil Maharjan

Help on installing the SQL Server 2012 integrated along with SharePoint 2010.

Hello all,

After long time gap, I just want to post a blog that might be helpful to the one who is going to learn, try and setup these great products of Microsoft as SQL Server 2012 and SharePoint 2010.

If you are going to learn, try and install SQL Server 2012 integrated along with SharePoint 2010 then this blog might be very helpful.

Some of the things you should be consider before installing and some steps for proper installation are as:

Please read the requirements of your machine and requirement needed for setup clear fully before you began to install.

Step 1. Firstly you need to install the Microsoft SQL Server 2012 either standard or enterprise version .The installation setup can be easily found in the link below for 180 days as Evaluation version.

For installing the SQL Server 2012 properly you can see the nice video in

Step 2. After successful installation of the SQL Server 2012, you can install SharePoint 2010 either in Standalone mode or in Server Farm mode. Here is the link to download

Here, The Standalone SharePoint installation is supported and common for development environments of Reporting Services.

However a SharePoint farm installation is recommended for a production environment. A farm is enabled when you choose the Server Farm option in SharePoint setup. You must have a farm installation if you want to use PowerPivot for SharePoint or Power View.

Note: You need to install the SharePoint 2010 Enterprise Edition for Server Farm option or to use PowerPivot for SharePoint. We can’t simple install the Server Farm mode within Windows7 OS .If you want to try then you should have to create the Virtual Machine’s for Windows Server 2008 within Windows 7 OS. Also, the user must be the domainuser, and we can’t use the local user.

Step 3. If you are trying to install the SharePoint 2010 in Standalone mode for development purpose then the link below shows the Step by step guide to install SharePoint 2010 in Standalone mode.

After installation of SharePoint 2010 as per above, while configuring SharePoint 2010 central admin (SharePoint product configuration wizard) after configuring the IIS 7 as needed you will get the errors as.

i. Could not load file or assembly ‘Microsoft.IdentityModel, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

ii. An exception of type Microsoft.SharePoint.Upgrade.SPUpgradeException was thrown. Could not load file or assembly ‘System.Web.DataVisualization, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

After this you will successfully install and configure the SharePoint 2010 in Standalone mode. If required you can update the Hotfix for SharePoint 2010 SP1 from Microsoft too.

Step 4. Basically you just need to be careful with Reporting Services configuration, you need to choose SharePoint Integrated Mode in report server mode option while configuration and create the report server DB for SharePoint integrate mode, if you plan to embed reports within your SharePoint.

It’s not possible anymore with SQL Server 2012 to configure Reporting Services for SharePoint in the Reporting Services Configuration Manager. We have to use the SharePoint Central Administration to Configure Report Server Integration in SharePoint mode.

If you have chosen the Reporting Services Add-In for SharePoint Products while installing then you don’t need to install again Add-in.

If you are configuring a report server to run within a deployment of a SharePoint product, you must specify integration settings that determine the connection between the SharePoint technology instance and a Reporting Services report server

Here is the link to Configure Report Server Integration in SharePoint Central Administration

Step 5. Also, if you plan to use Power Pivot for SharePoint then you need to run install SQL Server 2012 again and choose for SQL Server Power Pivot for SharePoint mode which requires the Domainuser for analysis services to install. Also, for this you need to install the SharePoint 2010 in Server Farm Mode and also needs to configure as similar .Here is the link to setup in Server Farm mode.

Also, to learn PowerPivot more here’s the link below and also you need Ms-Office 2010.

Besides these you also need to download the Visual Studio 2010 from link below


Hope this Blog post will surely helpful to the one who wants to learn, try and setup these great products of MicrosoftSQL Server 2012 and SharePoint 2010.


Anil Maharjan