SQL Server 2000 English and British English formats

Hello Expert Exchange Members
I have an application that has a MSDE database, I need to transfer the database from MSDE to SQL Server, but MSDE is in English Format and my SQL Server is in British English format.

Is it possible to setup a database on SQL Server that is English format on a British English SQL Server?

Thank you for reading.

Regards

GrahamR99
GrahamR99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NightmanCTOCommented:
Hi Graham

What do you mean when you say "format"? If you mean the date format, the date is actually stored internally as two 4 byte integers, so this will not make any difference, and the database can be restored to the other server with no problems.
0
GrahamR99Author Commented:
Hi Nightman
When you right click the server and go to properties in Enterprise manager, the server settings tab has a setting for Default Language is English or Britsh English.

By Format I mean default Language.
0
NightmanCTOCommented:
It should work fine. That is simply the default language for server messages that are presented to a user. Unless you are explicitly programatically parsing these as text, you should have no problems at all.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GrahamR99Author Commented:
Hi Nightman
Unfortunately thats not the case, the database will not tranfer because dates are out of range.

Is there a way around this?

0
NightmanCTOCommented:
If the dates are stored as datetime datatype you should simply be able to backup the database and restore to the other server.

How are they stored (e.g. as varchar), and how are you trying to move the database to the new server?
0
GrahamR99Author Commented:
The application has a tool that moves the database, it looks like the application will not work without performing the move itself.

Do you think I could change the SQL Server to English and run the tool then switch back?
0
NightmanCTOCommented:
Might be worth a try. You may have to restart SQL Server after making the change.

Also, consider that the application may rely on dates in a specific format (see regional settings) - you should check with the vendor to see if this is the case.
0
Brain2000Commented:
It sounds like the operating system's settings between the two servers are different.  SQL doesn't know about how to store dates (unless you use the SET DATEFORMAT ymd command), which your application should be utilizing.

On the new SQL server, go to START->CONTROL PANEL->REGIONAL AND LANGUAGE OPTIONS.  Under BOTH the "Regional Options" and "Advanced" tab, change the language to "English (United Kingdom)".  This REQUIRES a reboot of the server.  Then, when the ODBC driver connects to the SQL server will default to DMY format instead of MDY format.

You may or may not have to leave the server in this locale through experimentation.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NightmanCTOCommented:
If date format is a problem for you it is the result of a poorly designed application, and NOT SQL Server. SQL Server stores dates as two 4 byte integers, and is therefore region independant.

The only times this becomes a problem are when converting of strings to dates. This is common with dynamic SQL instead of parameterised queries, or when dates have been stored as varchar / nvarchar in the database.

Use of SET DATEFORMAT in stored procedures is ill advised, as it forces a recompile on the stored procedure (CPU pressure) and then *discards* the cached query plan (memory pressure), causing the query optimiser to generate a *new* plan. All in all a very bad idea for performance on SQL.

The application *should* be written with proper datetime datatype arguments for stored procedures (making the application region independant) and proper use of datetime datatype columns in the schema.
0
Brain2000Commented:
Very true.  But I'm sure as you know, what applications should be doing, and what they actually do, are usually two very different things.  Unfortunately, we do not have access to the application.  We can only speculate from the error what the cause of the problem is.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Networking Hardware-Other

From novice to tech pro — start learning today.