Merry Christmas – From SQL Server

Below is the script which you can run directly in your SSMS and SQL Server (With the power of  Spatial results) sends you Christmas wishes directly on your screen 🙂

USE tempdb

-- Prepare the scene

CREATE TABLE #ChristmasScene


item VARCHAR(32)

) ;

--Put up the tree and star

INSERT INTO #ChristmasScene

VALUES ( 'Tree',

'POLYGON((4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6, 4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0))' ),

( 'Base', 'POLYGON((2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0))' ),

( 'Star',

'POLYGON((4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2, 4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5))' )

--Decorate the tree


,@x INT

,@y INT ;

WHILE ( @i < 20 )


INSERT INTO #ChristmasScene

VALUES ( 'Bauble' + CAST(@i AS VARCHAR(8)),

GEOMETRY::Point(RAND() * 5 + 1.5, RAND() * 6, 0).STBuffer(0.3) )

SET @i = @i + 1 ;


--Christmas Greeting

INSERT INTO #ChristmasScene


'POLYGON((0 10, 0 11, 0.25 11, 0.5 10.5, 0.75 11, 1 11, 1 10, 0.75 10, 0.75 10.7, 0.5 10.2, 0.25 10.7, 0.25 10, 0 10))' ),

( 'E',

'POLYGON((1 10, 1 11, 2 11, 2 10.8, 1.25 10.8, 1.25 10.6, 1.75 10.6, 1.75 10.4, 1.25 10.4, 1.25 10.2, 2 10.2, 2 10, 1 10))' ),

( 'R',

'POLYGON((2 10, 2 11, 3 11, 3 10.5, 2.4 10.5, 3 10, 2.7 10, 2.2 10.4, 2.2 10, 2 10),(2.2 10.8, 2.8 10.8, 2.8 10.7, 2.2 10.7, 2.2 10.8))' ),

( 'R',

'POLYGON((3 10, 3 11, 4 11, 4 10.5, 3.4 10.5, 4 10, 3.7 10, 3.2 10.4, 3.2 10, 3 10),(3.2 10.8, 3.8 10.8, 3.8 10.7, 3.2 10.7, 3.2 10.8))' ),

( 'Y',

'POLYGON((4 11, 4.2 11, 4.5 10.6, 4.8 11, 5 11, 4.6 10.5, 4.6 10, 4.4 10, 4.4 10.5, 4 11))' ),

( 'C',

'POLYGON((0 9, 0 10, 1 10, 1 9.8, 0.2 9.8, 0.2 9.2, 1 9.2, 1 9, 0 9))' ),

( 'H',

'POLYGON((1 9, 1 10, 1.2 10, 1.2 9.6, 1.8 9.6, 1.8 10, 2 10, 2 9, 1.8 9, 1.8 9.4, 1.2 9.4, 1.2 9, 1 9))' ),

( 'R',

'POLYGON((2 9, 2 10, 3 10, 3 9.5, 2.4 9.5, 3 9, 2.7 9, 2.2 9.4, 2.2 9, 2 9),(2.2 9.8, 2.8 9.8, 2.8 9.7, 2.2 9.7, 2.2 9.8))' ),

( 'I',

'POLYGON((3.2 9, 3.2 9.2, 3.4 9.2, 3.4 9.8, 3.2 9.8, 3.2 10, 3.8 10, 3.8 9.8, 3.6 9.8, 3.6 9.2, 3.8 9.2, 3.8 9, 3.2 9))' ),

( 'S',

'POLYGON((4 9, 4 9.2, 4.8 9.2, 4.8 9.4, 4 9.4, 4 10, 5 10, 5 9.8, 4.2 9.8, 4.2 9.6, 5 9.6, 5 9, 4 9))' ),

( 'T',

'POLYGON((5 9.8, 5 10, 6 10, 6 9.8, 5.6 9.8, 5.6 9, 5.4 9, 5.4 9.8, 5 9.8))' ),

( 'M',

'POLYGON((6 9, 6 10, 6.25 10, 6.5 9.5, 6.75 10, 7 10, 7 9, 6.75 9, 6.75 9.7, 6.5 9.2, 6.25 9.7, 6.25 9, 6 9))' ),

( 'A',

'POLYGON((7 9, 7 10, 8 10, 8 9, 7.75 9, 7.75 9.3, 7.25 9.3, 7.25 9, 7 9),(7.25 9.5, 7.25 9.8, 7.75 9.8, 7.75 9.5, 7.25 9.5))' ),

( 'S',

'POLYGON((8 9, 8 9.2, 8.8 9.2, 8.8 9.4, 8 9.4, 8 10, 9 10, 9 9.8, 8.2 9.8, 8.2 9.6, 9 9.6, 9 9, 8 9))' ) ;

--Admire the scene


FROM #ChristmasScene -- Tidy up the pine needles and put away the decorations

DROP TABLE #ChristmasScene

After letting it run (Shouldn’t take more than a second), navigate to ‘spatial results’ tab in your results pane and voila…..

The total credit goes to the original author of this script. Unfortunately, I don’t have his/her name, I saved this script years ago in my tool set.

Wishing you all Merry Christmas and a very happy new year 🙂


SSMS – Run as a different user shortcut

It’s always a good practice to have a separate account from your regular AD account (which you use to login to your workstation) when working with SQL Server, especially for performing admin related tasks. In this blog post, I will show you a simple trick how to create a shortcut for SSMS on your desktop which opens the application with different credentials.

Let’s say I am a DBA and I have two separate domain accounts, ‘SQLTREK\Sreekanth’ (Which I use to login to my Workstation/laptop) and ‘SQLTREK\Sreekanth_adm’ (Which I should be using when I connect to my SQL Servers). In this case, If I want to connect to a SQL Instance from my local SSMS installed on my laptop, what I could do is right click on SSMS(holding shift key) and select ‘Run as different user’ and provide SQLTREK\Sreekanth_adm credentials. Well, what If I don’t want to go through this process each and every time I close and re-open SSMS? Let’s see how we can achieve this.

Right click anywhere on your desktop and create a new shortcut

Now locate your SSMS.exe path on your machine and prefix with runas.exe /user:Diffuser, See below for exact syntax.

My SSMS path:
“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”

So, My Run as command which I should use to create shortcut is
C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

This will open your SSMS.exe application as user ‘SQLTREK\Sreekanth_ADM’ instead of my regular account. Even better, if you don’t want to enter the password each time you open SSMS, you can pass /SaveCred switch. In that case, this is what I would use.

C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM /savecred ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

Now, give your shortcut a name.

Now I have the shortcut created, but it looks ugly with no icon. Well, let’s make it fancy…right click on the shortcut and go to properties and select change Icon button.

Now, click on browse and go to the folder where SSMS is installed.

In my case it’s “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio” and select “ssms.ico” and hit open–>OK–>Apply–>OK.

Now I have my fancy looking SSMS shortcut with my admin credentials saved.

From now on I don’t have to hold shift key and select run as diff user and provide credentials each and every time I want to connect to my SQL Instances locally from my machine.

Hope this helps, Cheers!

Note: Using /savecred is a bad security practice.

Dedicated Admin Connection(DAC) from SSMS

DAC is probably the most useful tool for troubleshooting SQL Server in few scenarios. Basically, It can be used as a back door entry in case the server goes unresponsive and doesn’t accept any new connections. So, how to make a DAC to SQL Server Instance? Preferred way is to use SQLCMD by issuing -A switch. But few folks might prefer SSMS over SQLCMD. In this blog post let’s see how to make a dedicated admin connection from SSMS.

Well, all you need to do is prefexing “admin:”to your instance name. I’ve a default SQL Instance(SQLTREK2), so in order to make a dedicated admin connection, all I’ve to do is use “admin:SQLTREK2” as my connection string. Okay, Let’s see what my SSMS does when I do that.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

If you wonder what multiple connections its talking about, It is referring to Object Explorer(Yes, OE establishes its own connection). But there can be only one DAC session at any given time. So, how to get around this issue in SSMS?

Method 1:
Creating DAC session by initiating connection from query pane. In the below screenshot, you can see I have a query pane which is currently disconnected.

Right click in the query pane and go to connection and click connect(providing admin:Instance name)

Well, SSMS created a pop-up which says it failed to connect, But I would like you folks to focus on the status bar where it did established a connection using admin:SQLTREK2. So basically this window is your DAC session.

Method 2:
By using ‘Database Engine Query’ connection. By using this method, you can avoid the error message which SSMS returned(shown in the above screenshot). All you have to do is click on Database Engine Query button right beside ‘New Query’ and make a connection. See below screen shots.

Voila! That was easy right….

Bonus points to you if you have noticed difference between ‘connect to server’ and ‘connect to database engine’. See below if you haven’t.

From Object Explorer: (DAC fails)

DB Engine query: (DAC Succeeds)

Hope this helps! Happy holidays…!

SSMS – Security Vulnerability Assessment(VA)

In this blog post, let’s explore one of the fantastic features added to our good old friend SSMS by Microsoft, Security Vulnerability tool. I am currently running SSMS 17.6, but this feature which we are going to see has been introduced in version 17.4. Okay….So, let’s see what this is all about.

I have a database named “ChickenNuggets” which I would like to know what are the potential security risks it has. Well, All I need to do is connect to SQL Instance from SSMS 17.4 or above and Right click on the database and go to tasks and navigate to VA and do a scan. See below screenshot for what I am talking about.

Give it a path and Wait for few seconds and voila… get something like this.

As you can see, it reported 10 failed checks for this DB. Also, You can select any item and approve as baseline if that’s normal in your environment, That way SSMS doesn’t include that particular item under failed list as it has been included in baseline.

As you can see it’s giving a detailed explanation along with what exact Query it ran for this particular check and the best part is it gives you remediation script as well.

Now, What If I want to scan something at Instance level, I could perform the same on Master Database.

That’s all good so far right? But there is still huge room for improvement. What if my instance has 100 databases? I hope you already guessed where I am going with this….Yes, That’s correct, I have to repeat this 100 times at individual database level. Well that sucks. Also, I don’t see anyway to export these results to different formats. It would be really nice if Microsoft gave us an option to export this to PDF format or HTML.  But hey…this is a start and I am positive Microsoft is listening to community and these will be made available to us very soon in future releases of SSMS.

BTW only SQL Server 2012 Instances and later are supported, and of course Azure SQL Database 🙂 .This is extremely light weight tool which you can run at any time without impacting your workload. It just runs bunch of DMVs…Shouldn’t block anything. But hey, don’t trust anyone and anything posted online when it comes to your Production Databases. After all that’s our Bread and Butter!

Happy weekend folks.Cheers!

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

Obtaining Pipe Delimited Results from SQL Server using SSMS

In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.

As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below.

In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.

Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below

In the top right corner drop down the list and select Custom delimiter as shown below.

Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)

Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)

Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.

Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂

As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS  🙂

Hope this helps!




SQL Server 2012 RC0 on Windows 8

Today I got some leisure time and thought of Installing SQL 2012 on my Win8 machine and sharing my experiences at a very high level. Well,  I’ve Installed SQL Server 2012 RC0 on my Windows 8(Consumer Preview – Evaluation Copy, Build 8250) Machine.

To be honest, I was expecting few bugs and annoying errors. But let me tell you this, My Installation went Flawless. FYI, I’ve Installed only basic components(DB Engine – with Replication, SSIS,Complete Client Tools, SDK Connectivity).

Just a screenshot of how it looks like on Win8. Well, Technically there is no difference in how SSMS looks and how it behaves, but as most of you are already aware of…there is no START Button on my Menu bar on my Desktop! See below on the bottom Left corner.

So, where did I open SSMS from?? If you said “Metro Screen” yes, you are correct. See the below Screenshot for how Windows8 organized programs on my Metro Screen.

Hmm…this is something which really disappointed me. Basically, It created bunch of Apps for each exe! Imagine how ugly your Metro Screen gets if you Install all the components of SQL Server!! I Wish it was smart enough to create at least folders/Groups and place related exe’s accordingly.

Btw, if you want to Run SSMS as Admin, just right click on the App and you will see the available options in the bottom of your Screen as shown below. ( This applies to all the Executables)