sp_attach_db 'metal' , 'c:mssql7datametal_Data.MDF','c:mssql7datametal_log.lDF'But when I execute the above procedure, I recieve the message below.
Server: Msg 1816, Level 16, State 1, Line 1 Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server. Server: Msg 1816, Level 16, State 1, Line 1 Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server. Warning: sort order 52 in database differs from server sort order of 146. Warning: sort order 52 in database differs from server sort order of 146.I don't know why. Please advise.
Under SQL Server 7.0 all databases must have the same sort order as that which you selected when you installed it. And it appears your servers have been installed with different sort orders. To check use sp_helpsort on each server. To fix you may need to script the objects and transform the data from server to server using DTS.
This has changed in SQL Server 2000 and now databases can have differing sort orders.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in September 2002