Solved

SQL Server 2000 English and British English formats

Posted on 2007-04-05
10
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn how the fundamental information of how to create a table.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

628 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