Spinning up a new Linux VM on Microsoft Azure

January 2, 2017 2 comments

First of all a very very Happy New Year to all of you !!!

This is my first blog post in 2017, and I’ll start with the most happening thing with SQL Server, which is SQL Server for Linux.

So today first of all we will see how to setup a Linux VM on Microsoft Azure cloud. And in next posts I’ve show how can we Install & Setup SQL Server on Linux.

I will use Ubuntu Server 16.04 LTS (Xenial Xerus) version from Azure Marketplace, and show how to setup a Linux VM in a step by step process.

All you need is a Microsoft Azure account, if you have not setup, please do it from here.

1. Go to and login with your credentials.

2. Click on the “+” icon, under “Marketplace” select Compute and under “Featured Apps” select Ubuntu Server.


3. The “Deployment Model” must be showing as Resource Manager by default, if not select form the drop-down list, and click on Create button.


4. Now on the Create virtual machine window, under “1. Basic” section provide:
– Name: new name for your Linux VM
– VM disk type: select Disk type, SSD or HDD
– User name: provide a unique user name for Admin purpose
– Authentication type: I selected “Password”
– Password & Confirm password
– Subscription: will show you your current subscription
– Resource Group: Provide a new name or select from the drop-down list
– Location: select location as per your preference and available in the list.
… and click OK.


5. On “2. Size” section Choose a size for your VM. I selected the DS1_v2 Standard, the cheapest one, finally click on Select button.


6. On 3. Settings section check the properties, I’ve left them as default for now, and click OK:


7. On the “4. Summary” section, the Portal will do various validations, just confirm all the settings and click OK.


8. And after few seconds or minutes your new Ubuntu Linux VM will be created and you can check the new widget appearing on your Dashboard as shown below:


9. Now to get the NIC (network Interface Card) or the User Name & IP address of your VM, click on the widget.

It will open a new window where you can check all the details related to your new Linux VM.

Just click the Connect button at the top which will show you the details so that you can connect via SSH or Telnet.


10. Now to connect the Linux VM you can either:

a. SSH directly from a Mac or Linux Terminal workstation.

b. Or, if you are on a Windows workstation, you need to use PuTTY, MobaXTerm or Cygwin to SSH to the Linux Azure VM.

Here, we will try connecting from a Windows workstation, so we will download PuTTY from

The downloaded PuTTY is a simple EXE application, just open it. Here we will provide the IP address that we saw above, you can copy and paste it as shown below and click Open:


11. This will open a PuTTY command shell window which will prompt you to enter the Linux username & password that you set in previous steps, please apply as shown below:


12. After you successfully logged in you will see some welcome message from Ubuntu with other details.

Just to make sure I’m really connected to a Linux command shell, I tried executing a Unix command “ls /” to list all the files & directories in the root drive.


So, this is how you can simply Create or Spin up a Linux VM in few minutes !!!

In my [next post] I’ll setup or install SQL Server vNext on this Linux VM and show you how can we connect via SSMS from a Windows workstation.

–> You can also watch this on YouTube:


2016 blogging in review

December 31, 2016 3 comments

Happy New Year from SQLwithManoj !!!

This time stats helper monkeys didn’t prepare annual report for any of their blogs for year 2016. So I prepared my own Annual Report.

–> Crunchy numbers

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 640,749 times by 396,212 unique visitors in 2016. If it were an exhibit at the Louvre Museum, it would take about 20 days for that many people to see it.

There were 117 pictures uploaded, taking up a total of 3.1 MB. That’s about 2-3 pictures per week.

The most popular post blogged in 2016 was SQL Server 2016 RTM full and final version available viewed 25,829 times.


–> Posting Patterns

In 2016, there were 52 new posts, growing the total archive of this blog to 416 posts.

LONGEST STREAK: 10 post each day, in March 2016


–> Attractions in 2015

These are the top 5 posts that got most views in 2016:

1. Passed 70-461 Exam : Querying Microsoft SQL Server 2012 (25,946 views)

2. SQL Server 2016 RTM full and final version available (25,829 views)

3. DB Basics – What are Candidate, Primary, Composite & Super Keys (17,189 views)

4. What is SQL, PL/SQL, T-SQL and difference between them (12,966 views)

5. SQL Basics – Difference between TRUNCATE, DELETE and DROP? (11,694 views)


–> How did they find you?

The top referring sites and search engines in 2016 were:


–> Where did they come from?

Out of 210 countries, top 5 visitors came from India, United States, United Kingdom, Canada and Australia:


That’s all for 2016, see you in year 2017, all the best !!!

Connect me on Facebook, Twitter, LinkedIn, YouTube, Google, Email

New built-in function TRANSLATE() in SQL Server vNext (2018)

December 29, 2016 2 comments

Microsoft looks very serious this time to move people from other databases to SQL Server. As with SQL Server 2016 & vNext (2018) you can see lot of Built-in function added, which were present in other databases from long back, will ease database development in SQL Server.

One of this function is TRANSLATE() function, which can be used like a REPLACE() function, and would avoid using REPLACE() function multiple times in a query.


TRANSLATE ( inputString, characters, translations)

Note: characters and translations params should have same length.

–> Consider this example I’ve taken from MSDN:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');


Input Output
2*[3+4]/{7-2} 2*(3+4)/(7-2)


–> If you had to do same with REPLACE() function then you would end up writing multiple & nested REPLACE() function, like:

				REPLACE('2*[3+4]/{7-2}', '[', '('), 
			']', ')'), 
		'{', '('), 
	'}', ')');


After working with this new feature it reminds me of IIF vs CASE statement. The IIF() function also works as a shortcut of CASE statement and cuts lot of clutter and gives you clean code.

Hope you find this small utility very handy while developing complex queries, will post more scenarios if I came across going forward, thanks !!!

New built-in function TRIM() in SQL Server vNext (2018)

December 26, 2016 1 comment

If you are thinking the new TRIM() function in SQL Server vNext is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !

– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.

– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.

–> Now, with teh new TRIM() function you can do both, but more than that.

Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:

	TRIM ('  Manoj Pandey     ') as col1,
	LTRIM(RTRIM('  Manoj Pandey     ')) as col2


Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:

	TRIM ( 'm,y' FROM 'Manoj Pandey') as col1,
	TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2,
	TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3


Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.

Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.

–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.

SELECT 'Manoj Pandey' as st,
	TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1,
	TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2,
	TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3


Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.

SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’

December 25, 2016 Leave a comment

Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:

Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.


The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.

–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:

sp_configure 'show advanced options', 1
EXEC sp_configure


–> To enable it run the below Query:

sp_configure 'Ad Hoc Distributed Queries', 1
EXEC sp_configure


…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.