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).

 

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.

 

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.

 

There are quite a few built-in stored procedures in Microsoft's SQL Server that can help you troubleshoot database issues and make those long nights less painful. Knowing they exist can be a life saver, so lets take a look.

 

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!

 

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.

 

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.

 

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.

 

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.