Save yourself the trouble – SQL Profiling your Application the easy way

comments
Microsoft SQL Server's Profiler is a necessity when digging around on a SQL server trying to find any bottlenecks or long running queries. How you personally lock down your profiling session to show only your application's data access appears, from the experience of looking over others' shoulders as well as how I personally go about it, to be a "how you taught yourself one day when first debugging queries". Like most things in dev-land someone had this problem WAY before you and there is a final, easy solution.

Compare & Update Database Schemas Right Within Your IDE - Part 2 Automation with TeamCity

comments
When updating a project’s Database Schema as part of your deployment strategy, you want to automate as much of the process as possible to avoid human errors. If you have a Visual Studio Premium installed on your build server, generating schema update scripts is easy to achieve with the built-in database tools that the IDE contains. I will show you how to do this easily and also automatically deploy the changes to your destination server with the awesomeness of TeamCity.

How-to: Quick & Dirty SQL Express scheduled backup

comments
SQL Express is pretty awesome as a light weight database server, and when Microsoft released it using the same database engine as the full version they did the world a huge favour. I have seen SQL Express in use on a lot of Virtual Private Servers & development boxes, but in most cases users don’t backup their databases regularly because of the missing scheduled jobs functionality that doesn’t ship in the Express version of Microsoft SQL Server. Like most limitations there is an easy way around this.

Continuous Integration Tip #3 – Version your Databases as part of your automated build

comments
Having a good Continuous Integration setup is the gift that keeps on giving, but what about your database? For most web applications these days, your database is a large part of your application – so why is versioning it such an uncommon thing? Because it’s time consuming and complicated – two requirements that Continuous Integration love conquering with a one-two punch.

Debugging SQL Queries, Functions, & Stored Procedures with SQL Management Studio’s Integrated Debugger

comments
Working with Stored Procedures and functions on a database tier can be time consuming, hard to debug and sometimes just difficult to get clarity on what is “happening”. A rarely discussed feature of Microsoft SQL Management Studio is its inbuilt debugging features. These allow you to see exactly what is going on, and step through your logic in a similar fashion to working in Visual Studio.

Visually edit records for any query using SQL Management Studio

comments
I watched an interesting thing take place the other day, in the form of someone using the visual tooling in SQL Management Studio to “Edit Top 200 rows” in a table, then realising that the record they wanted was not there, running a manual SELECT query to find the record, and then running a manual UPDATE command to edit the row. This can all be done a lot quicker using the same visual tooling they used in the first action – it’s just sneakily hidden in SQL Management Studio.

Stored Procedures – time (again) to reassess their use?

comments
So in today's day and age stored procedures are not a new thing at all. DotNet Developers seem to put themselves in one of two camps, those that think they are awesome and use them for everything, and those that think they only have certain times when they are needed and only use them when the absolute need arises (these days, not regularly).

Output data as raw XML from MS SQL – a little known feature

comments
In the modern world we live in with Web Services, RPC calls and the like, SQL server has had a lot of work on its hands trying to keep its followers from leaving to newer ways to store data such as document databases. Microsoft SQL server is like an old friend that developers have either come to love or hate – but like old friends, they may have been keeping some secrets. One of these hidden gems appears in the shape of native XML output support for queries.

Changing all database table’s owner back to DBO

comments
One of the niggling that often happens when your deployed to a cheap shared hosting environment is having your table owners change on you. If you have created a data access layer that references tables using their long name this is an issue. This can be caused by a deployment script not maintaining user permissions on your tables or maybe you’re using a web console to interract with you database like myLittleAdmin and it doesn’t allow you to create a table under any user but your own.

Adding Google ‘sounds like’ to your sites search

comments
It seems we live in an age where a lot of things are taken for granted on the web. Alternative spellings of things being offered to us when we search is a common example of this – simply look at Google and Bing’s “Did you mean X?” and you’ll know exactly what I'm talking about. At first though this can be seem quite daunting for the everyday web developer, but luckily Microsoft SQL server is here to save the day!

Retrieve only the first record from a LEFT JOIN

comments
Recently I was working on a project where I had to return a simple list of products, and if they had images associated with them, return information about only one along with the product. Times like these, a simple left join or inner join just doesn’t cut it if there are more than one image/record per product. When this happens there is almost always a simple solution, so let’s take a look.

Upgrading Umbraco from 3 to 4 – Not all smooth sailing

comments
If you have ever had to setup or manage an Umbraco installation you will know both the pleasure and the occasional pain that it can bring. Umbraco is part of the growing list of “oober cool” up and coming Dot Net CMS that are getting attention in the “get it up quick” world of marketing driven sites in the market place (Ford Australia for example). I recently had to upgrade a clients installation from 3.0.5 to 4.0.2.1 and the story that follows will hopefully help someone in a similar position so that they can revel in the same relief that I do currently.

Disconnecting users from a MSSQL database using SQL script

comments
There are many times when there can be a requirement to disconnect users from your Microsoft SQL database. Sometimes you can have bad code keeping connections alive that shouldn’t be, other times your simply want to restore a backup or do something that requires no users to be using the database. Reasons aside, I’ll show you some simple SQL to make it happen.

Running large MSSQL scripts & resulting memory errors

comments
Today i was battling in the trenches while trying to fix a deployment issue on a foreign webhost with a support team that had a lead time of 3 days on a support ticket when i came across a very annoying issue. While trying to import a large MSSQL script (140mb) i was having a repeated dual to the death with the error “701 Insufficient Memory” and his evil twin (kind of like the white dreadlock guys in the Matrix) - “System.OutOfMemoryException” in SQL management studio. But never fear, there is a solution, and its really simple.