how do i change the code page in sql server

I want to change the code page on a database to work

The effective code page is defined on column level, the DB CP ist just a default.

Means you have to touch every tbale/column.

Changing the code page in SQL Server can mean different things depending on your specific context.

Excel files are typically Unicode (UTF-8 or UTF-16) encoded. SQL Server handles encoding through collations, not "code pages" per se. However, when you import data using tools like SSIS, BULK INSERT, or OPENROWSET, code page issues can show up.

If you're using Import Wizard or SSIS, make sure your columns in SQL Server are set to NVARCHAR, not just VARCHAR. NVARCHAR handles Unicode.

• If you're using SSIS, set the code page of the flat file connection (for example) to 65001 (UTF-8) if your data is UTF-8.
• SSIS Data Conversion components can be used to force proper conversion if needed.

If you're using OPENROWSET to import Excel data

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=C:\path\to\your.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]');

Make sure your SQL Server is configured to allow Ad Hoc Distributed Queries.

If you're using BULK INSERT with a CSV (exported from Excel), you can specify a code page in BULK INSERT:

BULK INSERT YourTable
FROM 'C:\YourFile.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    CODEPAGE = '65001',  -- UTF-8
    FIRSTROW = 2
)

If you really need to change code page of a column/table, SQL Server uses collations, not code pages, to determine character encoding and sorting. You can change a column's collation like this:

ALTER TABLE YourTable
ALTER COLUMN YourColumn NVARCHAR(100) COLLATE Latin1_General_CI_AS;

Or for Unicode:

ALTER TABLE YourTable
ALTER COLUMN YourColumn NVARCHAR(100); -- Unicode support

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin