Please help: how to convert database to unicode?

I already posted this question some time ago but I didn't get any answer, so please forgive for posting it again.

We plan to support Unicode in our next software version. This means that we will need to upgrade many (> 200) databases from our customers from single byte collation to Unicode. Is there an easy way to do this with SQL Server?

With Oracle we can dump the current database, create a new instance that supports Unicode and then import the dump and all char fields get automatically converted to unicode.

As I see it, it doesn't seem to be so easy with SQL Server. At the moment my solution would be to create a new database schema with all the columns changed to the N types (nvarchar, ntext, ...) and then use a DTS Package to transfer the "old" data to the new database. This seems like a lot of work, so that I would like to know if someone has a better idea.

Thank you in advance for your ideas!!

Raul

Im not sure but try changing the collation type. It should change the underlying data to the new collation type.. jst a thought.

with smiles

santhosh

|||Raul

If you are merely changing columns from CHAR to NCHAR and VARCHAR to NVARCHAR, you should be able to just perform ALTER TABLE ALTER COLUMN statements to change the columns to unicode.

CREATE TABLE dbo.UTest
( C CHAR(10) NOT NULL,
VC VARCHAR(10) NOT NULL
)
GO
INSERT INTO dbo.UTest(C, VC) VALUES ('Test', 'Test')
GO
ALTER TABLE dbo.UTest
ALTER COLUMN C NCHAR(10) NOT NULL
GO
ALTER TABLE dbo.UTest
ALTER COLUMN VC NVARCHAR(10) NOT NULL
GO
SELECT * FROM dbo.UTest