VERY URGENT!! Decimal sign in sql server

[VBmim]

Hello Folks

We migrated our nt server to a newer machine (w2000). The sql server was moved by creating the databases on the new machine, and then exporting them from the old to the new machine...

Now, what is my problem:

When I have an update or an insert query like this:

insert into table(StringValue1, FloatValue1, FloatValue2) values('thisisatest','4,45','5,45')

I get an error on the new server: "error converting datatype varchar to float". The old server runs the query fine.

When I use '.' instead of ',' the query gives no errors on the new server (but, of course, gives errors on the old server).

Is there a setting somewhere where you can set your decimal sign? How can I let my new sql server accept ',' again for decimal sign?

This is a very urgent matter because all of my vb programmation works with the ',' as the decimal sign...

Thanx in advance

Greetz

Mim

 

[soy4soy]

This is going to sound silly, but when you set up your new server, did you set it up to use English (USA) as it's language? SOunds like you did. You will need to change that... not sure how. I know you can select the settings upon setting up the computer. (the default is USA English)

If there is no way to change the language after the fact, you can always reinstall (ugh!!!)

 

[VBmim]

Hello

Thanks for your response.

The language of the new machine (the language of the os I mean) is english. In the regional settings, the language is set to dutch, and the decimal symbol to ','.

The old server language (of the os) was dutch...

Do you think that is the cause?

Because if this is the cause, I am in deep shit. This means that I will have to:
- reinstall everything on the server (everything is on this server: mail, sql, user files)
- or rewrite all my vb applications

Sigh.....

 

[soy4soy]

Hmmm... without knowing first-hand how the server was built (can you talk to the one that built it?), it is hard for me to say for sure, but I know that for each server I've built for clients in the UK, that I have always screwed it up on the first build (my classic case of Americanism.... ARE there other countries?) ;-)

If you have access to the bloke that set up the OS, I'd share the problem with him to see if he has any ideas. I can't say for sure. (Yeah, I know: BIG Help I am!)

If it isn't the OS setup, there are similar settings in the SQL setup, but if you were able to restore the old databases to the new server, I'd be less suspicious of the SQL settings.

 

[TheBugSlayer]

In the SQL server installation choose Custom instead of the default one. This is likely a matter of collation. What you need to do is to install the new server with the same collation of your old server.

I would recommend you study the collation topic in BOL. There are some steps involved in changing collation after the server is already installed. You will need to rebuild the Master database.

I hope this helps.

 

[soy4soy]

But if the collation were not compatible, would he be able to import the old databases?

 

[SQLBill]

But if the collation were not compatible, would he be able to import the old databases?

Mostly yes...however some things will cause trouble and one of them is the use of the comma instead of the period as the decimal "marker".

-SQLBIll

 

[VBmim]

Hmmmmm

I did a little bit of research...

In the database 'master' you can find a table syslanguages. There are all character sets that you can use for your sql server.

Apparently, I have more problems than the . and , issue... You also have your date format,and the name of months and days.

The configuration I would like to have:
Date format: mdy
Decimal sign: ,
Month names: januari, februari, maart, april....

The configuration I have now
Date format: mdy
Decimal sign: .
Month names: januari, februari, march, april, may...

I know now that my sql server has the default character set (english_uk, 0). So what I would like to do is change the character set to 7 (dutch) and then change that character set to change the dateformat from dmy to mdy.

I already know that if you want to change your character set you have to rebuilt your databases and stuff... What I don't know is:
- how to change the character set
- how to rebuilt the databases (I suppose from a backup but I am not sure)


The sky is clearing up but there still are some big clouds there....