I was recently looking for an easy way to run queries and scripts against a hosted SQL Server database without having to come out of pocket and happened upon Microsoft SQL Server Management Studio Express. While I have been fully aware of the Express tools (C#, VB.NET, C++) I had no knowledge of a SQL Management Express.

Unfortunately part way through the installation I was faced with this message:

“The installed has encountered and unexpected error installing this package. This may indicate a problem with this package. The error code is 29506.”

Now the laptop I was using has 64bit version of Windows 7, and this is what I believe to be the cause of the issue. My brief research did not really find issues with Vista. In response I attempted to install the msi as an administrator and that seemed to do the trick. You can run any msi as an administrator from a command prompt as follows:

Related Links

• Microsoft Visual Studio Express
• Microsoft SQL Server Management Studio Express

I do not do nearly enough straight SQL these days, there is always a layer of abstraction between me and those db tables. Anyway I always forget how to reseed identity values for table. The following SQL statement ensures that the next insert on mytable will result in the seed value being 1.

DBCC CHECKIDENT (mytable, reseed, 0)

If you have done any reasonable level of SQL programming you would have been exposed to either NOLOCK or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (the default is READ COMMITTED). I have used these to help perform what is referred to as a ‘dirty read’, that is, ignoring any potential updates and inserts to the tables whilst performing the query. This also prevents the current query from producing and waiting for database locks.

There are a couple of other arguments that change the locking behavior, none of which I have ever had reason to use but I wanted to make a quick note of the them.

REPEATABLE READ
Locks are placed on data that is used in the query, this prevents others from updating the data, but it is possible for new rows to be inserted into the data set by other users. Concurrency is lower and this option should only be used when necessary.

SERIALIZABLE
Places a complete lock on the data set, inhibiting others from updating or inserting rows into the data set until the transaction has been completed. This is the most restrictive of all isolation levels. Concurrency is lowest with this option so this option should only be used where appropriate. This option has the same effect as setting HOLDLOCK (vs NOLOCK)  on all tables in the SELECT statement.

I am not sure what it is with developers but every single employer I have worked for seems to present me with another opportunity to fix legacy code with a conversion issue. Several weeks ago I was faced with a late Friday night Long (Big INT) to INT conversion ... sigh. I am not sure if there is some misplaced desire to save disk space or the maybe another specific design rational in using the smallest types available.

Anyway, I realized that I need to modify the XSD's associated with exposed methods in order to support the larger data type requirements, but I also needed to quickly jog my memory on the names of XSD types and how they relate to SQL data types ... too many languages ... too many types.

You can find a complete list here.

Many of our database systems have made the slow migration to SQL Server 2005 just in time for the full scale appearance of SQL Server 2008. I have, over the years, used 'isqlw' for my shortcut to query analyzer from the run window. I noticed a while back that this no longer works for 2005, and that you have to type in 'sqlwb'.

I figured the difference is due to the fact that you can have both version run along side each other ... my muscle memory now has to reformat itself for a new short cut ... I do not cope well with change.

My brain seems to be freezing on DB related situations, any way I forgot how to check for the existence of a stored procedure before dropping it. I will be able to find the information quickly now!

IF EXISTS (select * from syscomments where id = object_id ('dbo.SPROC_NAME'))
BEGIN    
    DROP PROCEDURE dbo.SPROC_NAME
END

This was going to be my next topic but when Scott Gu blogs on a topic it is difficult to find a new angle. So here it is ... All developers please heed the warning, be careful with our private information and review this blog!

"Everyone is born with genius, but most people only keep it a few minutes." - Edgard Varese

I worked for a company, that shall remain unnamed, that had specific database naming standards. The standards were strict, as they should be, and I stayed with the company over four almost five years and the naming conventions are now immersed in my SQL conscience.

For example all tables in the database had to be prefixed with a 't' (Why? no one knew but we did it, and there was very little benefit in changing it), so I would begin every SQL select with: "select * from tWhatever"

This is wonderful and harmless until I came to my new place of work in which I do not have to worry about the 't' or the 'idb' or anything else like that. Unfortunately my fingers just cannot seem to break the habit and 'select * from' does not seem complete without the 't'.