Voice of the DBA

Tag: Redgate

  • A Quick Export with SQL Packager

    Disclosure: I work for Red Gate Software

    Someone asked me the other day if I’d ever used SQL Packager to export a table to send to another person. I hadn’t, and in fact hadn’t even ever run the tool, but this individual said it worked great.

    Since Red Gate tools are designed to be simple and intuitive, I thought I should give it a try and see what happens. I went through the Start Menu and found SQL Packager in my toolbelt installation:

    packager0

    I documented this as I went, shooting this images as I went through the process for the first time. As soon as Packager started, it began the packing wizard.

    packager1

    SQL Packager is designed to help you bundle up a database, or part of a database, as a part of an installation in your application. It can produce an .exe, a C# project, or a set of scripts that you can include as a distributable item in your application installation (or upgrade). The information can be compressed, so you reduce the requirements for your customers.

    In my case, I decided to just package up a table. I first signed into my local instance, and chose the AdventureWorks database.

    packager2

    Next, I chose just one table, the Customers table. The Red Gate tools tend to follow a similar, intuitive design, and try to do the most common things for most customers. In this case, the entire database was selected (this is a database packaging tool), so I deselected all, and then chose the Customer table.

    packager3

    Once I choose the table and click next, and confirm the selection, the packaging begins. I get some options as to how I might choose to build my package.

    packager6

    The options are shown, and in this case I choose to save the script. Once I clicked next, I had a change to see the final script. First there was the schema tab:

    packager4

    On this tab, all the DDL for my table is there, including a couple dependent tables, and some functions needed for defaults or computer columns. Keys and indexes were included.

    On the data tab, I had the DML for the actual data.

    packager5

    The comment says “Add 1000 rows”, which seems like a default. However I went back and checked in SSMS, and sure enough, my table had 1000 rows.

    I clicked next, and had the chance to specify a save location.

    packager7

    After saving, I opened the script in SSMS, just to check. Sure enough, the DDL was at the top:

    packager8

    and the data at the bottom:

    packager9

    Simple, and easy.

    If you are looking for a way to move certain sections of your database for a deployment, like all the lookup tables, give SQL Packager a try.

    If you need to send some stuff to a client or friend, it might be a simple way as well to export the DDL and DML into one package.

  • T-SQL Tuesday #25 – T-SQL Tricks

    TSQL2sDay150x150It’s time for T-SQL Tuesday again, and this time Allen White (@SQLRunr | blog) is asking for your tricks. If you want to participate, read Allen’s post and learn how.

    The question this month is: What T-SQL tricks do you use today to make your job easier?

    My Tricks

    I don’t have any great whiz bang tricks in T-SQL, and I’m sure there are more than a few people that can out-code me with their. However I do like to make my job easier, and so I have a couple of administrative tricks for use with your T-SQL environments. These are the ways that I save time, and work more efficiently.

    The thing that has helped me most often in my career is to keep little snippets of code handy to that I make few mistakes and save time. These days I do that quite often with SQL Prompt, a third party tool from my employer. It basically implements intellisense for SSMS, but more importantly, it gives me shortcuts.

    However the biggest advantage to me is the Snippets in Prompt. There’s a feature that allows you to type a shortcut and then press “Tab” and have that shortcut replaced with a longer section of text. For example. I have this shortcut:

    prompt1

    If I type “zqd” in SSMS, and then hit tab, the T-SQL in the “code” box above appears. There are a few very frequently used slices of code that I can insert like this, without taking my hands off the keyboard, which is very handy. There’s even a whole snippet manager in Prompt that has pre-defined, and custom, snippets.

    prompt2

    The most often one I use is “ssf”, which inserts this:

    SELECT TOP 10 * FROM

    So do you need to buy SQL Prompt? No, but if you do, tell them I recommended it so my boss with be happy and maybe send me a nice bonus next Christmas.

    A very similar functionality is in SSMS. I actually used to heavily use templates in the old Query Analyzer days of SQL Server 7/2000 and this has continued in Management Studio with the Template Explorer

    template1

    I can drag a template from the explorer on the right into the code window and the code appears. I’ve pulled in the backup template. You can even add your own:

    template2

    It’s easy to do, and you can read more about Template Explorer in BOL.

    However if you’re like me, you move around, you use VMs for coding, and you want to be sure that your tools are on all these machines. There are a few ways to do this:

    • portable drives
    • cloud sevices

    I guess these are both the same thing, just implemented differently. I’ve used both ways, and while I do carry some flash drives, and hard drives, with various items on there, I find that I can never quite keep these up to date, and they’re really emergency drives for me in the event I don’t have connectivity.

    The primary way that I manage mode snippets, templates, etc. is by putting all my code in centralized places. For Prompt and SSMS, these locations are known, and while configurable, I stick with the defaults. For me this means I have three folders to track:

    • SQL Prompt default snippet folder
    • SSMS Templates folder
    • \SQL in my Documents folder in Windows

    All three of these folders are the same on all my machines, and I use a cloud service to keep them in sync. For me, I have two difference services in play, mostly for testing, and I see little difference between them. I have Live Mesh, a Microsoft service, for some folders, and DropBox for others. From what I’ve seen, they both work essentially the same, though DropBox is a little smoother for me with the Apple integration of some apps. That probably doesn’t matter for most of you, but it’s a difference. Live Mesh works on my Macbook, but not on the iPhone.

    There are other cloud services, and you can choose the one that works well for you, but I highly recommend you have a script library, as well as a snippet/template library, and you use a cloud service to be sure you can access those files if you are away from your primary machine. You might be surprised how handy this is when working on a server or remote machine.

    That’s my T-SQL Tuesday trick for T-SQL, better script management to make your work easier.

  • Do You Want to be a DBA in Space?

    Not the trip for me, but it might be for you. Enter today.

    I had a chance to see a few of the DBA in Space videos before the contest was released and I shared them with my kids. My son’s first reaction was “can I go?” He couldn’t, but it did excite him a little to think about potentially going into space at some point in his life and he thought it was amazing that some average computer worker, like me, would get the chance. It’s an amazing contest and I’m still stunned that my employer, Red Gate Software, is offering it to anyone that works with databases.

    And that’s interesting. Developers, administrators, if you’re administering databases in any way, you’ve got a chance to enter the contest. You can get started now by signing up and going through all the videos, answering the questions as you go. The contest ends Friday, November 18, 2011, so there are a few days left to follow Brad McGehee on his space alien adventure and have fun with a few silly questions.

    I, however, wouldn’t go to space. I don’t like flying, and I’m not thrilled with heights, and it wouldn’t be my choice for a prize. I do know many people that love science fiction, the space program, and would be thrilled to go. If I could win (I’m not eligible), I’d take the cash alternative, $102,000, and probably take the trip of a lifetime. Maybe take the family and ski in Switzerland, maybe fly to Australia and enjoy surfing and diving down under with the kids, but definitely fly first class everywhere.

    Let your mind wander, ask your friends and family where they’re like to go? Would they have the adventure of a lifetime with you or would they think about sending you up in a rocket, as many of the Slashdotters want to do. It could be your choice, but only if you enter the contest this week.

    Steve Jones

    The Voice of the DBA Podcasts

  • SQL in the City – LA

    I had a fantastic time at SQL in the City in London, and it was quite an honor for me to speak there, at the Royal Society of Medicine. Tomorrow I’m taking off for LA, for SQL in the City – LA, the second part of our experiment. This time I’m joined by Kalen Delaney, Denny Cherry, Aaron Nelson, and Rob Sullivan in addition to Grant, Brad, and a number of Red Gate’ers.

    We’ll be at the Skirball Cultural Center, which is just off the 405 in LA. If you’re in the area, the map is below, and you can still register if you can come on Friday.

    The last time we did this, we had a great mix of sessions, having panels of breaks taking place in one room when someone was speaking in another. There was plenty of refreshments, including a closing thank you with Red Gate beer.

    This should be another fun event, and I’m hoping that it goes over well. If you enjoy it, please let Red Gate know so we can schedule more of these next year.

    Location