I got caught by another localizaion feature of SQL Server today.
SQL Server is not aware of regional settings.

This can be a real pain. If your machine uses say German regional settings and your database returns 1.01  if you use variant conversion routines (hey I am still using ado – we all can’t be on the bleeding edge) this can arrive as 101

This is not what is wanted. This has brought me to question where in a data access layer should the localization go?

This week I tripped over the following in the MSDN library:


GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

This is a bit of a pain.

I work on a three tier application that makes heavy use of stored procedures (600 and growing).
 
I have a set of expect scripts that I use to refresh the stored procedures.
These are great – it puts a gui onto the batch files that actually run osql commands.
This takes away a lot of the support issues involved in having people misstype complex commands.

The only problem is the scripts are slow to run. It can take 5 mins to refresh the lot.
This is not really that big a problem – but I am trying to put together a continuous integration server and it would be good if the database could be rebuilt very quickly.

To advance my C# knowledge I have started an ADO.NET app that reads the same batch files and then executes the stored procedures (one per file).

This is where I found the above problem. OleDBCommand objects don’t understand the GO command. It looks like I am going to have to preprocess the script and replace GO with ;

SQL Server 2000 has some interesting issues regarding it’s use of regional settings.
I had a slight problem with some date conversion issues.

If you wanted to convert a string to a date in transact sql typically you would use:

DECLARE @DATE DATETIME
SET @DATE  = ‘2005-03-15 09:46’

This should be 15th March 2005 etc

Since no default conversion has been specified a sane assumption would be that the machines regional settings would be applied. This is how windows applications are supposed to work.

I developed and tested this code on a standard US-English installation of SQL Server.
I even played with the regional settings and it worked just fine.

A customer installed it on a Spanish langauage version of SQL Server and the code broke.
They tried changing the regional settings to US-English and it still did not work.

SQL Server uses the language of the installation to determine the defaults for date conversion.
This is horrible from a testing perpective. I would need to install foreign language editions of SQL Server to test this – and I still can’t find this documented anywhere!

The solution to this problem is to specifiy the date format explicitly using the third parameter of the convert function:

DECLARE @DATE DATETIME
SET @DATE  = Convert(DATETIME,’2005-03-15 09:46′,121)

The moral of this story is don’t trust any automatic conversion always be specific.