Simon Thorneycroft and Jonathan Hodgson

Can't read Sql because it's too messy

Continuing the Sql theme... sure we have all been there before where you're looking at someone else's (or your own) sql and it's a bit of mess.

I often use this little tool which can reformat sql code from this:

SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

to:

SELECT
 Orders.ShippedDate,
 Orders.OrderID,
 [Order Subtotals].Subtotal,
 DATENAME(yy, ShippedDate) AS Year
FROM
 Orders
INNER JOIN
 [Order Subtotals]
ON
 Orders.OrderID = "Order Subtotals".OrderID
WHERE
 Orders.ShippedDate Between @Beginning_Date AND @Ending_Date

After you install it, ignore the evaluation splashscreen this first time and click close, then it sits in the taskbar. You'll probably want to change the options, normally I switch oneline off and set keywords to uppercase on. Then copy the sql to the clipboard and press the hotkey Ctrl+Shift+J and then paste the newly formatted sql back. It's not perfect, but it is handy for quickly cleaning up sql.

You can download niceSql from http://home.broadpark.no/~ihalvor/

Seems I'm not the only one looking for better tool support in Sql Server as Dan Fernandez has some great ideas.

Also Apex Sql Edit has some good features like Intellisense, better editor, unit testing, etc.

Whilst we're on the subject, in Sql Server 2005 nearly all the dialog boxes have been improved so they are no longer modal and you can make all your changes and then view those changes as a script for later use; useful for learning Sql syntax for backup and restore commands.

Comparing Sql Server schema and data between databases and servers

I'm going a bit Sql Server blog crazy...

Some tools you just need to have, one of those is Sql Compare from Red Gate, they are a British company based near Cambridge.

"SQL Compare is for use in a development environment when changes made to a local database need to be pushed to a live database on a remote server. Traditionally, this meant spending hours trawling through database schemas, hand-generating migration scripts. SQL Compare will automate this process for you. It will compare and synchronize all database objects, including, but not limited to, tables, stored procedures, views and user-defined functions. SQL Compare does this fiddly and tedious job properly – for example, dependencies and referential integrity are properly taken into account, and the SQL scripts are of a very high quality."

One thing that is particularly good about this set of tools is that it places created scripts within a transaction, so it will rollback any changes should an error occur.  The scripts also include users and associated permissions for all objects. Perfect tool for double-checking you haven't missed anything and checking that development, staging, test and production are all in sync after a release.

Sql Server 2005 Integration Services (Think replacement for DTS but much, much more)

There is so much new to learn about SQL Server 2005 let alone when to use it.

This video gives a good introduction on SSIS, where the demo shows creating and executing a package. You'll see the new tools, how the items change from white to green whilst running including showing the number of rows processed. Also the ability to add ""viewers" to see the data at each step.

Don't forget you can also try some of the hands-on-labs.

Jamie Thomson from Conchango has been posting some great blog entries on Sql Server 2005 and SSIS.

VS.NET custom projects...

Test driven development is a great way to deliver solutions, constantly giving you the confidence to add a piece of functionality or perform that next large refactoring. But setting up your project can be a real pain, at least two projects are required - the test assembly and the assembly under test - plus a bundle of references to NUnit, so I use a custom project wizard for VS .NET 2003 to do these painfully boring tasks for me. Tools for generating code and performing mundane tasks should always be encouraged; they allow you to get on with the stuff you enjoy without breaking your train of thought. If you don’t believe me then you need to read ‘The Pragmatic Programmer’! So, here is the approach to produce a simplified version of one of the wizards I use for my projects.

1. Fire up notepad and create a ProjectAndTests.vsdir file that is located in your program files\Microsoft Visual Studio .NET 2003\VC#\CSharpProjects, these files contain a lot of stuff that you just do not need to understand (perhaps I will go into detail in a later post). Okay, Add the following single line to your blank file:
   

   CSharpDLLWithTest.vsz|{FAE04EC1-301F-11d3-BF4B-00C04F79EFBC}|Class Library And Test|1|Class Library and Test Template|{FAE04EC1-301F-11d3-BF4B-00C04F79EFBC}|4547| |ClassLibraryWithTestProject

   
   This gives VS the information it needs to display your project in the C# projects section.
2. Now you need to create the vsz file that you just referred to. In the spirit of doing the least that works (XP), open the CSharpDLL.vsz file from this directory and change the line that says Param="WIZARD_NAME = CSharpDLLWiz" to Param="WIZARD_NAME = CSharpDLLWithTestWiz". Save this file as CSharpDLLWithTestWiz.vsz so VS now knows where your template should be.
3. Go up one directory and then navigate to the VC#Wizards subdirectory. Make a copy of the CSharpDLLWiz folder with its contents and place it at the same level renamed as CSharpDLLWithTestWiz. The same name as that used in the previous step.
4. Almost there, now we navigate into the CSharpDLLWithTestWiz\scripts\1033 directory, open the default.js file in there and make a couple of changes. cut the lines between:
   

   var strProjectName = wizard.FindSymbol("PROJECT_NAME");
   
   and
   

   proj.Save();

   
   exclusive and move them to a function:
   function CreateProject(strProjectPath, strProjectName) { ... } add a final line to this function to return the proj instance (return proj;). now replace the empty space where you just cut from with two calls to your new function:
   

   proj = CreateProject(strProjectPath + "Test", strProjectName + "Test");
   var refmanager = GetCSharpReferenceManager(proj);
   refmanager.Add(path to your nunit dll minus the extension here);
   proj.Save();
   proj = CreateProject(strProjectPath, strProjectName);
   //the following line should be the proj.Save(); line that you had left from the original.

   
   Save the file and close it.
5. You’re done, fire up a fresh instance of VS.NET 2003 and create your new DLL with test project.

That’s it, this has been a bit of a whirlwind tour although the steps are about as simple as I could think of making them. I hope this helps you to begin automating away some of the pain in your development. If you get stuck, just email me at simon.thorneycroft@syncadia.com.