Using Microsoft’s SQL Server Management Studio’s Import and Export Wizard, I would get “Unexpected Unrecoverable Error” message when I would click “Messages…” or view a report when an import failed. This was very annoying and I wasn’t getting any work done this way.

Thanks to the SQL Server Forums I was able to resolve this issue by installing the Extended .NET Framework 4. If you have the Client Profile version installed, you will still apparently get this error. Be sure to do a Windows Update after the install as there is a security patch for it.

BTW, this import Wizard is a big fail for Microsoft. SQL Server 2000’s version did much more – now my life is just plain more difficult thanks to their “upgrade” – just say’n.

I am running SQL Server Management Studio 2008 R2 64-Bit on Windows 7 64-Bit.

I’m basing my current project around SQL 2008’s hierarchyid data type, which is completely new to me. One frustrating issue I have found is the inability to edit any hierarchyid type’s data as a string inside the Microsoft SQL Server Management Studio’s edit feature. Granted I can control the data via the New Query feature, but I tend to use the interactive editor for quick and easy access to my data during the development process.

Take the following example:

Notice the hierarchyid data is converted into a string to be shown. However if you where to try and update the current value of ‘/1/’ to ‘/1/1/’ or update the current NULL value to ‘/1/1/’ then you would receive an error stating “Invalid cast from ‘System.String’ to ‘Microsoft.SqlServer.Types.SqlHierarchyId'”.

I’m not sure what data would even be acceptable in the field. I tried entering ‘0x5AC0’ for the NULL data and still received an error.

So from what I can see is this data type is unchangeable inside the editor.

The workaround for this would be to run the following TSQL:

UPDATE Table1
SET Question_Hierarchy_ID = '/1/1/'
WHERE ID=1

In the 1930’s Miss Hannigan wanted to delete orphan Annie, but now we have child neglect laws that prohibit that. Good thing we now have SQL.

Lets say we have two SQL tables that contains names of team members. One is for softball and one is for baseball. We want to remove all players from softball that do not play baseball. You could do this in a loop or a subquery, but if you have team data from the past 100 years this may become quite costly.

Here’s a quick way to make this happen:

DELETE S
FROM Softball S
LEFT JOIN Baseball B ON (S.Name = B.Name)
WHERE S.Name IS NULL

What this is doing is matching the table Softball with Baseball. Any orphaned Softball players (no match with Baseball) end up getting deleted.

Normally, if I’ve ever needed a large amount of text data stored in a database field, I would use a column type of text. I would usually do this when going beyond 256 characters. I think this stems back to use MS SQL 6.

Now as of SQL 7, the varchar type can hold around 8000 characters (8k) and nvarchar can hold around 4000 characters. This limitation stems from the 8KB internal page size SQL Server uses to save data to disk. But who really wants to deal with putting in client-side validation of 8k characters when you can just make it unlimited?

But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.

Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file).

Most clients that I’ve worked on are still on SQL 2000, and maybe 2005 – so I haven’t really caught up to the nifty 2005 and 2008 changes yet. Reason? MS SQL Servers are freak’n expensive!

In SQL Server 2005 Microsoft added support for the varchar(max) and nvarchar(max) data types. The difference between varchar(8000) and varchar(max) is that it can now store up to 2GB of data (about 2,147,483,647 characters) instead of 8K. That seems to be plenty in my books for anything I need it for. Continue reading →

Recently I came accross a blockade after upgrading my SQL Management Studio from 2005 to 2008. All of a sudden if I added a “no-null” option column in a table or inserted a column in the middle of the existing schema I could not save. At first I thought there was some hidden permission I was missing, but after futher research I find out it’s a setting in the Studio itself.

The full error is: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

To get rid of this annoying “feature”, take these steps.

1. Go to Tools > Options > Designers > Table and Database Designers via the menu
2. Uncheck the option ‘Prevent saving changes that require table re-creation

Note: This is a safety feature and turning this off opens you up to possible loss of data, so make sure you know what you’re modifying or at least make a backup.

In the past I learned how to combine one query to correctly update or insert a record based upon if it exists or not. I would only use this if I didn’t already know if it existed because an un-needed select statement would be run and it increases the size of the query string. Example:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)

Thanks to Jeremiah Clark’s Blog, I’ve found an often better way of doing this.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)

The first example will do a table/index scan for both the SELECT and UPDATE statements. The second example will perform one less table/index scan, likely increasing performance.