German/Spanish SQL Server installation problems (Samuel)

Fri, 01 Jul 2005 11:38:54 GMT

Q: When I install the software on the a German/Spanish SQL server, I get a lot of SQL errors about datetime value conversion, similar to the following:

converting datatype char to date-time, value out of range

A:In our software when we communicate with the database or whenever we need to use date literals, we standardize the format to ISO-8601 datetime format: yyyy'-'MM'-'dd HH:mm:ss However, certain versions of the SQL server do not understand this format, and so far they have been the German and Spanish versions.

Wolfram provided the following info that will solve this problem on the server side without having to modify our software at all (I slightly modified the info he provided):

Solution

When creating a new database on the SQL Server, it takes the default country settings - also when creating a new (security) user for the forum db. This means when creating the forum with the 'SQL Server Enterprise Manager' in a German environment, the 'datetime' fields of the tables will act in the format 'dd.MM.yyyy HH:mm:ss' - but the forum sw tries to write date/time information just like 'yyyy-MM-dd HH:mm:SS' - an exception is thrown.

To solve this problem, you have to change the default language of the (security) db user access to 'english' only. Here's the procedure to start a new installation if you are using the German version of the SQL Server:

start 'SQL Server Enterprise Manager'
select the sql server
select 'database': create a new database (example: 'dbForum')
select 'security / user': create a new database user (example: 'dbAdminForum') with 'SQL-Server authentification'
change 'Database' to the new database name (example: 'dbForum')
change 'Language' to 'English'
ignore the message box
select the new database (example: 'dbAdminForum')
add the new user to your database an select 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter' (actually you can get by with just 'db_datareader' and 'db_datawriter')
set the database permissions for the user (right click the new database entry, under 'Properties')
start 'SQL Query Analyser' for the new database
open the sql create script (for example: '2.5.5Unicode_Full.sql')
with F5 the script executes
close SQL Query Analyser and SQL Server Enterprise Manager
change the 'masterinclude.aspx' entries for the forum
start the 'default.aspx' in your forum folder