Thursday, February 16, 2012

SQL Server and Excel: Include column headers

If you want to build a quick report from your sql results in Excel, I'd highly recommend turning an option in Management Studio (SSMS) called "Include column headers when copying or saving the results".  Dare I say it's one of those helpful options you only know about if you happened to stumble across it?

You'll find it under:
  1. [System Menu] > Tools > Options...
  2. Query Results + SQL Server + Results to Grid
  3. Check the box that says "Include column headers when copying or saving the results"

Options dialog:



Example of use:

Select from Results Grid
Select All (or Ctrl+A & Ctrl+C)

Pasting into Excel
Headers included!

Monday, February 13, 2012

Backup Visual SVN Repositories

(using svnadmin dump)

After migrating from a Linux SVN server to a Windows server with Visual SVN, I needed a new backup script.  Visual SVN doesn't come with a backup solution, but it does have enough SVN admin tools to be able to complete this task without having to install any additional SVN tools. 

The scripts perform an svadmin dump on every repository on the server, then zip those dump files into a one zip file with the current date, using 7-zip.  I used 2 scripts because I wanted to get as much logging as possible into the final zip file. 

You’ll find loads of similar scripts throughout the web, the ones below are just my variant of other methods found; please feel free to cherry pick anything you need out of here.  It goes without saying that you'll have to customize each script to get it work on your system.  This code is free and comes with no guarantees.    Good luck!

Code Highlights:
These are conventions I felt were interesting, I've removed some of the script particulars:

Call svn_dump_repos.cmd > log.txt
  • Calls the dump script and pipes the output to a text file (log_timestamp.txt)
Dir /A:D /B> dirs.txt
  •  (Run in Repository folder) Stores a list of all folders (repositories) into a text file (dirs.txt)
FOR /F %%r IN (dirs.txt) DO ( ... )
  • Runs a loop for each item (%%r) in dirs.txt (i.e. each repository)

I highlighted the same lines that I felt were important in the code below.

Script 1:
svn_backup.cmd
@ECHO OFF
REM svn_backup.cmd
REM 02/10/2012 jmj
REM
REM This script will log the entire script: svn_dump_repos.cmd, and add it to the final zip file.
REM Instead of providing command-line arguments, each variable is set below.
REM

CLS

REM cpuname used for filename, workingdir for holding dumps/logs/etc,
REM and destdir for final destination
set cpuname=svnserver
set workingdir=D:\svn_backup\temp
set destdir=\\backup_server\DEV

REM timestamp = YYYYMMDD
set timestamp=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%

REM Program file locations
set zipexe="C:\Program Files\7-Zip\7z.exe"


echo.
echo ****************************************************
echo Starting svn_dump_repos.cmd, (log as log_timestamp.txt)
echo ****************************************************
if not exist %workingdir% md %workingdir%
call svn_dump_repos.cmd > %workingdir%\log_%timestamp%.txt


REM svn_dump_repos.cmd created the final zip file, add any log files into it (*.txt)
echo.
echo ****************************************************
echo Adding log files to zip file
echo ****************************************************
cd %workingdir%
%zipexe% a %cpuname%_%timestamp%.zip %workingdir%\*.txt

echo.
echo ****************************************************
echo Move archive to final destination
echo ****************************************************
move *.zip %destdir%

echo.
echo ****************************************************
echo Removing the temporary files and exit
echo ****************************************************
del /f /q %workingdir%\*.txt
del /f /q %workingdir%\*.svndump

Script 2:
svn_dump_repos.cmd
@ECHO OFF
REM svn_dump_repos.cmd
REM 02/10/2012 jmj
REM
REM This script will perform a svnadmin dump on each repository in a folder
REM
REM Instead of providing command-line arguments, each variable is set below.
REM

REM cpuname used for filename, workingdir for holding dumps/logs/etc,
REM and destdir for final destination (not used here, but you want to include location in log)
set cpuname=svnserver
set repo=D:\Repositories
set workingdir=D:\svn_backup\temp
set destdir=\\backup_server\DEV

REM timestamp = YYYYMMDD
set timestamp=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%

REM Program file locations
set svnadminexe="C:\Program Files\VisualSVN Server\bin\svnadmin"
set zipexe="C:\Program Files\7-Zip\7z.exe"


echo.
echo ****************************************************
echo Hello! Ready! 
echo.
echo Create svn dump files for each repository on server
echo.
echo Using these Settings:
echo  - Computer Name: %cpuname%
echo  - Repository Folder: %repo%
echo  - Working Directory: %workingdir%
echo  - Destination Directory: %destdir%
echo  - Zip Exe (7-zip) Location: %zipexe%
echo  - svnadmin Location: %svnadminexe%
echo ****************************************************
echo.

echo.
echo ****************************************************
echo Dump Respositories (name_timestamp.svndump)
echo ****************************************************
cd %repo%
dir /A:D /B> %workingdir%\dirs.txt
cd %workingdir%

FOR /F %%r IN (%workingdir%\dirs.txt) DO (
    echo Dumping: %%r
    %svnadminexe% dump %repo%\%%r > %workingdir%\%%r_%timestamp%.svndump
    if errorlevel 1 then Goto Error
)

echo.
echo ****************************************************
echo Compressing Dump files (servername_timestamp.zip)
echo ****************************************************
%zipexe% a -tzip %workingdir%\%cpuname%_%timestamp%.zip %workingdir%\*.svndump

if errorlevel 1 then Goto Error

echo.
echo ****************************************************
echo testing the new archive
echo ****************************************************
%zipexe% t %workingdir%\%cpuname%_%timestamp%.zip

if errorlevel 1 then Goto Error

echo.
echo ****************************************************
echo listing contents of new archive
echo ****************************************************
%zipexe% l %workingdir%\%cpuname%_%timestamp%.zip

if errorlevel 1 then Goto Error

GOTO Finish

:Error
echo.
echo There was an Error in the process
echo Please double-check the results!
echo.
echo GoodBye!
@ECHO ON

:Finish
echo.
echo Finished!

@ECHO ON




Friday, February 3, 2012

SQL 2008 Installation Follies


I installed SQL Server 2008 (R2) on my machine alongside SQL Server 2005, which was already on there as the default instance; I named my 2008 instance “SQL2008”. 

If you’re like me, you have lived the past few years without the need to think about default instances.   So remember that stuff like “(local)” will connect to your 2005 instance, and “(local)\SQL2008” will connect to your 2008 instance.

SSMS 2008 will gladly connect to your (default) 2005 instance, and then you will be left scratching your head trying to figure out why you can’t create databases with a compatibility level of 100, or continue to get frustrating errors trying to restore 2008 backups.

That’s my errant move of the day, I hope this finds you before you do anything drastic.

Wednesday, February 1, 2012

Show/Hide Results Pane – SQL Server 2008

A few years back I wrote an article about doing this same thing in SQL Server 2005.  All the images from the previous post were somehow lost, and since the instructions are basically the same for SQL 2008 I thought it would be a nice refresher.

One of the simplest and most useful features when debugging a sql script is to be able to run your query to see some results, then hide the results pane to continue tweaking your sql code. This feature was a button on the toolbar in Query Analyzer in SQL Server 2000, but for some reason it has been missing in SSMS (SQL Server Management Studio) since 2005. Well, the button may not exist by default, but it's easy to add it in.

This is what we are striving for, a button on the SQL Editor toolbar that says either "Show Results Pane" or "Hide Results Pane":


To add a button to hide the results pane:

1. Click on the little down-arrow at the end of a toolbar and choose "Add or Remove Buttons", then choose "Customize...":
Add button to Toolbar


2. The Customize dialog window will open with the "Commands" tab selected. Now we only need to find the command we are looking for. Under "Categories:", choose "Window". Then under "Commands:", find the command "Show Results Pane":

Choosing the "Show Results Pane" command from the menus


 3. Once you have located the "Show Results Pane" command, you have to click and drag it to where you want it to be located on the toolbar:

Starting the drag


Placing on toolbar



End result after placement

Originally I had my button located on the Standard toolbar, but eventually I figured out it made more sense to add it to the SQL Editor toolbar. It's pretty easy to find the SQL Editor toolbar - it only displays when you are working in a query window.


Here is the end result again:

Notice the button says "Hide Results Pane" when the results pane is showing:
"Hide Results Pane" Button


Also notice that the button says "Show Results Pane" when the results pane is hidden:
"Show Results Pane" Button