Month: July 2012

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 Licensing – Myths/Gossips

This will be a pretty short blog post where I would like to share my thoughts on new Licensing policies of Microsoft SQL Sever 2012. Last year, when Microsoft announced that they are moving to Core Based Licensing from Socket based starting SQL 2012, I still remember many conversations we had within our team that “Microsoft is probably taking a wrong step by increasing the price, we might need to pay more than 2 to 3 times just for licensing on what we are paying currently”. Also there are huge ripples all over the internet regarding this change. My thoughts on this are “Nope! This doesn’t apply in all cases”. Few people just started to panic without properly understanding the new licensing terms and conditions. Actually, there is no need for creating such an attention all over the internet. Oracle has core based licensing since ages(come on guys, we are not running on SQL Server 6.5 or 7….we are taking about the best of its kind, the awesomely awesome SQL Server 2012 :D)

Why am I saying that this doesn’t apply in all cases?

Well, Core Based licensing limitation is only for High end Enterprise Edition(around 6900USD/Core). You can get BI Edition(around 8500USD/Server+around 200/CAL/user) license on Server based+CAL. Standard Edition has both the flavors – You can go with either Core Based(around 1800USD/Core) or Server Based(around 900USD+ around 200 USD/CAL/user). So If your shop is running on Standard Edition, you should be good straight away!

Note: We must buy minimum of 4 core licenses if we choose core based as per MSFT! Remember, this is just list price. Depending on your corporate agreements with Microsoft, you’ll be having special discounts in most of the cases…. 🙂

So, First we’ve to identify our needs, what exactly we really need in our environments. Do we really need Enterprise editions on each and every box? Do we really need each and every Physical server in our data center to be a 4 socket Quad Core Server? Gathering this crucial information and planning accordingly will save millions of dollars for any large SQL Server Shop.

For Virtualization – we’ve to license all the Virtual CPU’s. This might sound little harsh to few folks! How ever, if you are planning for SQL Server 2012 Enterprise, once you license all the CPU Cores in the physical Server(Host), you can have unlimited number of VM’s 🙂

So….My humble request is, stop blaming Microsoft and understand your real requirement and only pay for your real needs. Please do not just blame without really understanding the underlying nuts and bolts of licensing. I totally agree…licensing any enterprise product is always a pain and very confusing with especially with each and every release/edition. Touch base with your procurement department(if your team is not responsible for direclty dealing with MSFT) within your organization to know more about your corporate agreements you’ve with MSFT for licensing.

Visit this for more info. Have a wonderful weekend all!