Change collation to SQL Server 2005 after upgrading databases

by:
Jeremy Kadlec, Contributor

Want to change the collation to SQL Server 2005 standards after upgrading databases from SQL Server 2000? Learn the best approach involving DTS and SSIS.

One of my clients is running a 7GB database in SQL Server 2000 and they're rolling everything out to SQL Server 2005. Basically, I need to copy the database from 2000 to 2005, but change the collation to the SQL Server 2005 standard.

So far I have been creating new databases (test), scripting the old databases and applying the scripts before importing the data via Data Transformation Services (DTS). This did not work, so I split the script up (tables, views, sp's, fk's etc). However, the scripts generated, call stored procedures, views or tables before they've been created and there always seems to be some views that get missed.

Step 2 of 2:

Safe Harbor

Can you suggest a smoother approach? I have read your article Step-by-Step Guide: Migrating to SQL Server 2005 with Copy Database Wizard, but it doesn't mention collation.

Depending on the collation change (binary to non-binary or vice versa), the best approach with native tools would be to script out the objects and then migrate the data via DTS or SQL Server Integration Services. Just be careful to make sure the collation is correct at the database, table and column levels when you are finished.

This was first published in December 2007

Dig deeper on SQL Server Stored Procedures

All
News
Get Started
Manage
Problem Solve

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

E-Handbook

Developments in SQL Server BI point to focus on self-service, users

Related Q&A; from Jeremy Kadlec, Contributor

View query execution plan in SQL Server with this method

Learn how to view a query execution plan in SQL Server with SQL Server Management Studio or Query Analyzer. Review and decrease query plan sub tree ...continue reading

Adding non-clustered indexes to SQL Server columns

Adding non-clustered indexes to SQL Server columns and tables frequently queried is possible. Learn how to add non-clustered indexes to SQL Server ...continue reading

Is my SQL Server 2000 optimized?

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our SQL Server experts

View all SQL Server questions and answers

SearchBusinessAnalytics

Sounders coach shows way ahead for analytics in sports

The Seattle Sounders fitness coach is taking sports analytics into uncharted territory and scoring some big wins along the way.

Linthicum: Cloud analytics apps need solid data architecture

At the 2014 TDWI Executive Summit in Boston, consultant David Linthicum explained the importance of a well-planned cloud ...

Big data benefits begin with business focus in analytical modeling

Big data analytics programs need a strong analytical modeling foundation to deliver the business benefits that companies are ...

SearchDataCenter

A flexible data center layout made for the new standard

Data centers aren't the center of the IT universe anymore. With plenty of resources for hosting dynamic workloads -- cloud, ...

Data center projects get lost in translation

The reasons to virtualize 80% of servers and create fault-tolerant clusters are obvious -- right? When you're explaining the ...

The processor problem: One size doesn't fit

Conventional x86 processors try to do everything, but they are power hungry and performance inefficient. Stripping down ...

SearchDataManagement

Agile data warehousing casts business light on dark process

At the TDWI Executive Summit in Boston, users talked about the benefits and challenges of incorporating the Agile methodologies ...

Varied NoSQL technologies need careful weighing and sorting

The number of NoSQL database options is vast, and distinct product categories occupy separate niches. That makes it imperative to...

MongoDB founder on dynamic schemas and caching as a crutch

The train to NoSQL rode the rails of agility and scalability, according to Dwight Merriman, MongoDB co-founder.

SearchAWS

Building applications using Auto Scaling in AWS

Learn how applying Auto Scaling to business applications can enhance organizational processes.

AWS Trusted Advisor users at odds with cloud monitoring tool costs

Amazon's move to make elements of its Trusted Advisor cloud monitoring tool available for free last week should only be the first...

AWS adds logs to CloudWatch, under-prices third-party tools

Amazon Web Service’s new CloudWatch Logs may give IT pros reason to ditch more expensive third party tools such as Splunk, Loggly...

SearchOracle

Oracle releases Solaris 11.2, Database 12c R 12.1.0.2

In a round-up of recent Oracle news events, Oracle released Oracle Database 12c Release 12.0.1.2, along with Oracle Solaris 11.2 ...

Oracle Big Data SQL brings together SQL, Hadoop, NoSQL

The new option for the Oracle Big Data Appliance, Oracle Big Data SQL, can simultaneously query Hadoop, NoSQL and SQL data and ...

Oracle joins OpenDaylight SDN project, will integrate Solaris

OpenDaylight is a project to approach software-defined networking from an open source perspective, and now Oracle is a part of it.

SearchContentManagement

Web content management systems try to do it all

Web content management systems have many roles to fill, from being the hub for company communications to personalizing ...

Yammer and Office 365: The mixed message of functionality overlaps

Microsoft's strategy has created a confusing overlap between Yammer and Office 365 functionality. Will the mixed message for ...

The perils of deleting data from the Web

The 'right to be forgotten' regulation, which allows European citizens to request that data be deleted from search engines, has ...

SearchWindowsServer

Harness the power of Microsoft Azure's PowerShell cmdlets

PowerShell isn't just for Windows Server anymore. Get the basics on PowerShell with Azure and learn some Azure-specific cmdlets.

Microsoft cloud storage may lift Azure skyward

Microsoft will continue to blare its Azure cloud next week with several cloud storage announcements. Will users listen this time?

Internet Explorer, Windows Server Service Bus receive patches

In July's Patch Tuesday update, Microsoft issued critical fixes for Internet Explorer and a lesser-known Windows program.

SQL Server stored procedures vs. CLR stored procedures: Time to migrate?

Check SQL Server database and log file size with this stored procedure

Configure SQL Server Service Broker for sending stored procedure data

Find size of SQL Server tables and other objects with stored procedure

Saying yes, no or maybe to stored procedures

Why use stored procedures?

library

trigger

Stored procedures: Create and manage standard objects

Stored procedures: Create and delete constraints and indexes

Stored procedure: Examine children

Stored procedure: Investigate related data