Blog

Export a SSMS Query Result Set to CSV

Export a SSMS Query Result Set to CSV

Once you have a query that is returning the data you like in SSMS you have a few options to move the result set into a format that you can use in other tools.

The simplest way is to select all results and then copy & paste them into whichever other tool you are using. This is great for smaller sets of data, and works very well when using tools such as MS Excel.

There are some cases though where the quantity of data returned does not fit into the excel spreadsheet, or you may want to be able to import data into a different tool. To do this you need to export the data from the query set to a format that can be referenced by the other tools.

There are 2 options that can be done. The first is to execute the query with the results being output to a file. The second is to configure a data export based on a query.

 

Execute query results to a file

  1. Build your Query

    Build your Query

    Open SSMS (SQL Server Management Service) and open / create the query for the data you are looking for.

  2. Next we have to configure the output to file options.


  3. On the menu bar select: Tools then Options

    Select Tools then Options

    Select Tools then Options

  4. On the left select Query Results, SQL Server, Results to Text.

    Set the Results options

    Set the Results options

  5. Set output format to CSV. You can also mark the Display Results in a separate tab and the switch to results tab after the query executes if you like. Click OK to save the changes.

  6. Now when you go to export your query results you have a few options to enable to results to file feature. Press Ctrl+Shift+F to set the query to file destination (Ctrl+D will revert back to the grid output). You can also select Query, Results To & Results to File. There is also a quick button located on the SSMS Standard Toolbar.

    Set the Results to go to Text

    Set the Results to go to Text

    Or use the button on the toolbar

    Or use the button on the toolbar

  7. After having selected the output format, you can Execute the query. You will be prompted to enter a file name and location for the output file. The file will be created with a .rpt extension, but it is a just a plain text file.

    Set the File Name

    Set the File Name

  8. After the export you can browse to the directory and change the extension of the output file to .CSV if you like, and then open the file to verify the contents.

 

 

Export query results to a file


  1. Open SSMS (SQL Server management Studio) and open / create the query for the data you are looking for.

    Write your Query

    Write your Query

  2. Select all of your query text and Copy it to the clipboard (Ctrl+C).

  3. In the Object explorer, right click on the database you wish to export data from and select Tasks, Export Data

    Select Tasks then Export Data

    Select Tasks then Export Data

  4. The SQL Server Import and Export Wizard will open with the data source defaulted to the database you right-clicked on. Leave this as it is and click Next.

    Choose your Data Source

    Choose your Data Source

  5. Select Flat File Destination for the destination and provide the file name and other options. Once done, click Next to continue.

    Set the Destination

    Set the Destination

    image020Set the File Name

    Set the File Name

  6. On the next screen you are asked if you want to Specify Table Copy or Query. Select the Write a query to specify the data to transfer radio button and then click Next.

    Get ready to put in your own query!

    Get ready to put in your own query!

  7. Paste the Query

    Paste the Query

    Paste your query code from step #2 into the SQL Statement box. Click Parse to ensure syntax is correct. Once it is all good, click OK to close dialog box and then click Next to continue.

    Valid SQL is good!

    Valid SQL is good!

  8. You can click Edit mappings if you like to map data types and transformations during the export for more accurate data. When ready click Next.

    Change mappings if you need to

    Change mappings if you need to

  9. Leave Run immediately marked and click Next

    Yes! Run it now!

    Yes! Run it now!

  10.  

  11. Review actions to be performed. When ready click Finish

    All Done!

    All Done!

  12. You will see process complete indicating success (if there are errors you will need to determine why, it may just be a lack of permissions to save files on the server, etc.). You will also see a count of records that were transferred. Click Close when done.

  13. Now you can use the new file in whichever tool you prefer.

Live 360 Discount Code & Helping Digby!

Live 360 Discount Code & Helping Digby!

Join me at SQL Live 360 this November!Live 360 discount code: LSPK05

I am hugely excited to have been selected to present at Live 360, November 17 – 21 in Orlando, FL.

Live! 360 brings together five conferences, and the brightest minds in IT and Dev, to explore leading edge technologies and conquer current ones.

More info is here: http://bit.ly/LSPK05HOME

I’ll be presenting the following session during the SQL Live 360 portion:

 Prevent Recovery Amnesia Forget the Backups

Through this introductory session we will review the various aspects & myths of backups in SQL Server.

We will pin down how developing a recovery strategy to meet business requirements is, in fact, more important than just a good backup strategy.

 

SPECIAL OFFER:

As a speaker, I am excited to be able to extend a $600 savings on the 5-day package for Live 360 this year!

Register here: http://bit.ly/LSPK05REG and use code LSPK05

Extra Bonus!

Digby! will get a new pool if you use our Live 360 discount code!

For every time this discount codes used, we will put the $50 we get in return towards paying for Digby’s second TPLO surgery he just had (And maybe towards a bigger pool for the dog park!)

You can learn more about what happened with Digby! and if you want to donate and not go to Live 360 you can do that as well here!

Get better at what’s available, and get ready for what’s coming.

We delve deep into the topics that matter most in your world today, covering practical knowledge, fundamentals, new technologies, and futures.

Register now!!

Take advantage of the additional $500 Summer Savings on top of our discount code before the price goes up  tomorrow! (You can still use our code after tomorrow too! Remember it’s LSPK05)

http://bit.ly/LSPK05REG

See you there!

 

Null Values in SQL Server

Null Values in SQL Server

Walk & Talk #001 – Unknown or Null Values

Ride with us in this video as we head to Richmond, Virginia to present to their user group.

This is the first official Walk & Talk, but you’ll notice we’re not exactly walking along.

Through this video we talk about and show you how NULL values in SQL Server work.

There are even demos on how NULL values affect your result sets, how to identify a NULL value, and how to deal with these unknown values.

You can download the scripts here: Download it!

 

Whitelist a Set of IP Addresses in SQL Server

Whitelist a Set of IP Addresses in SQL Server

Blacklist vs Whitelist

bouncer_EnviusDesignz

Are you on the Whitelist?

I think just about everyone is familiar to some degree with the concepts of a blacklist and whitelist.

If not, the easiest way I can summarize them is as follows:

A blacklist is a list of what can not access something you are trying to protect.

If you travel, this can be thought of as the TSA no fly list. If you are on the list, you are not getting anywhere fast.

A whitelist, on the other hand,  is just the opposite. It is a list of what is permitted to access something. If whatever is attempting access is not on the list, it isn’t getting in.

This is like your typical bouncer at that hip club with the clipboard that won’t let you in because you are not on “the list”.

There is a lot of debate of which is a better practice, to blacklist or whitelist, but I feel it is a matter of preference and how much maintenance you want to do.

 

What does this have to do with SQL Server?

Well, the other night I once again heard the question of: “Does SQL Server allow you to Blacklist/Whitelist certain IP addresses?”.

It is a good, and valid, question and I had to think a bit before answering that I was not aware of an easy way to do that.

Others in the room concurred that you pretty much would have to configure your DNS service to create the blacklist and/or whitelist.

Now, to me it makes sense that there are times you want to lock down access to your server to specific IPs, and other times you just want to stop other IPs from even getting near your server.

When I got home I started doing some research and was able to find a couple of articles written online about using a feature that came with SQL Server starting in SQL Server 2005: Logon triggers

One script that caught my eye was created in 2009 by

 

Welcome to the Whitelist!

Step this way please

Step this way please

Because of the question of creating a whitelist was what was asked, I am going to go through that process. In a different post I will detail the process for creating a blacklist of IP addresses.

Before we are able to put a logon trigger in place for a whitelist though, we are going to need to create a place to store our list. This will be our bouncer’s clipboard.

There isn’t much we really need, just a simple table to store the list of IPs allows to access our system (and 1 non-IP address, but we’ll get to that in a moment).

I am going to use the master database for this and just create a table with a varchar(15) field to hold the IP addresses.

Once we have the table created I will want to populate it with a few IP addresses I know will need access to the SQL Server. You will need to get the proper list together, but I include some commonly found ranges for test environments in this script.

Take note of the last value I am putting in the table ‘<local machine>’. If you are doing this from the machine on which you are going to place the logon trigger, it will not use the network to loop around to itself, but rather uses internal networking. If you leave off ‘<local machine>’ you may get locked out. Trust me on this. I found out the hard way on one of my test systems.

*Note: While you create this trigger and test it I strongly advise you do not disconnect your session. We are creating a very restrictive access logon trigger that will prevent anyone, even the DBA / admin from accessing the server from the wrong IP address. If you leave your session connected the logon trigger is not fired and you can undo the trigger. Once you know it is all working, you can safely close the connection. If you mess up and get locked out, you can get back in, bypassing the trigger, as a member of the sysadmin role and using the dedicated admin access or restart the server in single user mode.

Now that I have some IPs added to my list, I need to make sure that anyone is able to read from the list. To do this I grant select access to the Public role. This may seem counter-intuitive, but if the connection is not from one of these IP addresses, it won’t be able to really do anything with the data found since the connection will be refused.

Now for some extra function in the trigger, I want to record the IP address from failed attempts along with the number of times that IP address tries to access my system before I fully enable the blocking capability. Be aware that this number may be inflated as SSMS does connect multiple times to the server. This step is purely optional, but worth having in my opinion, to help you test and be a little more responsive with any addresses you may have missed or forgotten, but also to identify any IP addresses making high access attempts. You could then have the network admin update  the DNS server blacklist with the information you provide. It is a very simple table, just like the whitelist table, but includes a counter to be incremented each time a denied IP address attempts to connect. Permissions are granted to allow the trigger to use the table correctly to read, insert and update the values.

Once all our tables are created and ready to go, we can implement the logon trigger.

 

But Triggers are bad!

I know, I know. “But it’s a trigger! Triggers are bad!”.  Just like most things in life, there is a potential for triggers to be bad. It is like having hidden code in your system that you have to remember about. But like all those things that are “bad”, there are times that it may be the correct solution for an issue you have. There may be a blacklist and whitelist, but in general things are kind of grey. In this case, the trigger it lets us create a way to validate IP addresses without the immediate need to involve our network admins and gives us some extra insight to our own systems activity.

Anyhow, here is the basic code for the trigger.

Notice the ROLLBACK is commented out for the denied connections. This is so the IP_Attempts table can be populated for test / development purposes. When you add the ROLLBACK statement back in, the trigger will function as intended, but will not be able to record the denied IP addresses since the connection and trigger transaction are all rolled back. Once you have enough of a list of the attempts that are not on your whitelist in the IP_Attempts table you can make adjustments to your whitelist and add the ROLLBACK functionality back in by un-commenting the line.

You can change it up however you feel works better for you, and please share in the comments if you find a better way to do this.

Let’s step through this trigger in a little more detail:

First we state we are going to create a logon trigger called IPAddress_Check.

We declare a variable to hold the client IP address that is connecting, and we collect that IP address from the EVENT_INSTANCE/ClientHost data using the EVENT_DATA().value function.

Next we perform a simple comparison to see if the IP address is in our whitelist table. If it is we go ahead and “print ‘IP Address Allowed’” since we have to perform some action, and this is not visible to the end user. The logon then continues like normal.

If we don’t find an exact match for the incoming IP address, we perform a simple range check. We remove the last part of the IP address and replace it with a * and compare that to the values in our table (I’ve kept things simple in the table, but you could easily adapt it for more detailed ranges). Again, if there is a match, we print the “IP Address Allowed” message and continue with a regular logon.

Things start to get a little interesting if there is not match. I have opted to use a merge statement (SQL 2008 and later only, sorry, but you can change the code freely) to add or increment the IP_Attempts table accordingly, then rollback the connection attempt. The rollback action is what prevents any further action being taken by the connection from the wrong IP address.

That’s it. Nice, clean and simple.

I do advise working with your network admin to ensure your DNS is configured properly with its whitelist and blacklist options, but now you have an additional tool in your DBA belt to help keep things locked down a little tighter.

Try it out and let us know how it all works out in the comments below!

How Critical is a Demo?

How Critical is a Demo?

Do you always need a demo?

Do you always need a demo?

Is a demo always needed?

One of my more popular presentations I have been doing as of late is my “The Spy Who Loathed Me – An Intro to SQL Server Security” one. What is intriguing as that is is completely demo free.

This session covers a lot of material in a short time. The purpose of the session is not to teach you how actually take security measures, but to introduce you to the offerings SQL Server has in respects to security, and to inspire you, the learner, to want to learn more about the topics that caught your attention.

Each time I give this session, I state clearly that there is not a single demo being done in it. Then I expect about 1/2 the room to get up and walk out. Surprisingly they don’t. I then go on to state there are 2 reasons I don’t do any demo in this particular session.

 

1) There really isn’t enough time to include a demo with the quantity of content.

2) Any demo directly influences your choices and actions.

 

What does each of these really mean?

Let’s break it down a little more:

1) There really isn’t enough time to include a demo with the quantity of content.

I am going to take you through 90+% of the security features SQL has so you can get an idea of what is possible. I could easily make an entire week long course with demos on each topic covered; and that may happen (stay tuned…). I feel in this particular session it is important that you are introduced to what is possible. You get an overview of how different security features work, which hopefully peaks your interest, so you can then go and do further due diligence and determine what would actually work best for your environment.

2) Any demo directly influences your choices and actions.

If you see a demo the odds are that you are going to go and try it yourself. Even higher are the odds if it was a real cool demo that could help you. Demos directly influence what you know and do in SQL Server, or anything else really. A lot of people learn via observational learning. We see something done successfully, like a demo, and then try to emulate that something for our own benefit. There are times that is completely appropriate. For example, learning proper syntax, performance tuning suggestions, etc. There are also times when doing exactly as someone else did is not entirely appropriate, such as when database security is involved. Are there times it would be helpful to have a demo? Of course there are! Is it in the session I designed to relay as much information and inspire people to learn more about the different security features that SQL Server offers. Not so much. When we are talking security, especially at an introductory level, I feel that you should get informed of what is available with as little “influence” as possible. You can then dig in deeper with more focused sessions, training or documentation, that contains demos, to determine if and how the security feature is feasible for your environment.

There is a third item I don’t mention above, but is worth considering. Here’s my though on anyone who is making the effort to pull together a 60+ minute session without a lot of demos to help you learn. If they are good at what they do, you can rest assured that they have gone over the top to make the presentation strong enough to stand on its own and it isn’t something to be missed.

So, what is there to take away from all this rambling. It is quite simple. Just because a session doesn’t have a demo does not mean that it is of little or no value.

Broaden your horizons and accept that maybe, just maybe, you can learn a lot without seeing a actual demo all the time.

You may be surprised at the number of amazing sessions you have been missing out on.

Announcing SQL Summer Camp

A new way to train in SQL Server.SQLSummerCamp_logo1

 

I had an idea a few years back about a training event unlike any other. One where you could bring family, all have fun, and yet still learn about SQL Server from top people in the industry. After years of working things out, that idea has come to be.

That idea is now SQL Summer Camp.

SQL Summer Camp is a combination of making new friends, having fun and learning new things about SQL Server. This is not a rigorous, intensive boot camp style of training, nor will it be like a typical conference where you just sit and listen to speakers talk all day.

Instead, SQL Summer Camp will have a combination of fun activities, training, and social events related to SQL Server. We understand we will have people of various technical levels joining us, so we will have training and activities for everyone. Not only that, we are going to have it in Nova Scotia, Canada high on the wish list of locations to visit of many people.

You can look forward to things like presentations and training from some of the best SQL people in the world, camper-led panels, fun competitions and activities like the SQL Server Sea Shanty Sing Off and SQL Horror Stories by the nightly camp fire.

The goal is to create a near all-inclusive event that was educational and also promoted the social aspects of the SQL Server community. SQL Summer Camp provides a week of training, meals, events and activities centered on SQL Server and recharging your own batteries.

We know being alone for a week of training in a great location is sometimes awkward for friends and family. They usually want to go to the location as well, but don’t want to take the training, or maybe even aren’t all that geeky about SQL Server. Don’t worry, we have that covered. We offer a program for your friends and family, we call them Stowaways, and they have an entire week of events and activities they can participate in while you are in training. They also get to join you and participate in our larger group events like the SQL Server Schooner Sunset Sailing!

 

There is so much to be experienced at SQL Summer Camp it can’t be conveyed in just these few paragraphs.

Check out www.SQLSummerCamp.com for more information and we will see you at camp next year!