Solved

SQL Server 2000 English and British English formats

Posted on 2007-04-05
10
680 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:GrahamR99
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18857867
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
 

Author Comment

by:GrahamR99
ID: 18858161
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18858199
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
 

Author Comment

by:GrahamR99
ID: 18858353
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18858819
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:GrahamR99
ID: 18858853
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18859112
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
 
LVL 8

Accepted Solution

by:
Brain2000 earned 250 total points
ID: 18873411
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
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 250 total points
ID: 18873492
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
 
LVL 8

Expert Comment

by:Brain2000
ID: 18873614
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now