Changing collation in SQL 2000

I have a database in SQL 2000 on which I am trying to convert the collation to case insensitive from case sensitive. I am trying to use DTS to do this. The master database is already case insensitive. I have created a new database on the same server with the proper collation. I am then trying to use the DTS to import the data by replacing all objects. I am essentially trying to copy all the database objects changing only the collation. Everytime I try the process, I get errors and the process stops. It does not tell me what the errors are.

Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?

Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.

Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.

Thank you.|||

Thanks Shankar,

I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.

Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?

Thank you,
Garry

|||How are you ensuring that the two database collations are the same? You could try to copy some of the data into a temp table and see whether you get the behavior of the old collation. This would be a verification.

Changing the server's collation would be the more reliable way if that is a choice.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights|||

Hi Shankar,

Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.

Thanks,

Roshan.