Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2000 English and British English formats

Posted on 2007-04-05
10
Medium Priority
?
687 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 500 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 500 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

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

704 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