[SOLVED] change sql server collation- error: 5075

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[SOLVED] change sql server collation- error: 5075

Author

Topic

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 07:21:11

hi every body

please help me to change the collation of my database. it's give me below error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 07:35:22

The reason is obvious you've an object aplitvarchar which is based on current database default. I think you need to remove the dependency on it by removing the schema bound criteria (using ALTER FUNCTION). then you'll be able to change the database collation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 07:58:49

quote:
Originally posted by visakh16

The reason is obvious you've an object aplitvarchar which is based on current database default. I think you need to remove the dependency on it by removing the schema bound criteria (using ALTER FUNCTION). then you'll be able to change the database collation.

I deleted all of my functoin but error changed to 5030

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 08:08:27

quote:
Originally posted by Smart-Girl

quote:
Originally posted by visakh16

The reason is obvious you've an object aplitvarchar which is based on current database default. I think you need to remove the dependency on it by removing the schema bound criteria (using ALTER FUNCTION). then you'll be able to change the database collation.

I deleted all of my functoin but error changed to 5030

Try making database in single user mode and then doing the change.


USE master;
GO
ALTER DATABASE acc
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE acc
COLLATE Persian_100_CI_AI
GO

ALTER DATABASE acc
SET MULTI_USER;
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 08:13:08

excellent!!! THANK U

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 08:24:12

I have an other question too:

the type of my fields are NVARCHAR and won't change after edit the collation.
what should I do to change the type of fields with their contents?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 08:56:44

quote:
Originally posted by Smart-Girl

I have an other question too:

the type of my fields are NVARCHAR and won't change after edit the collation.
what should I do to change the type of fields with their contents?

That the columns are NVARCHAR is probably what you still want.

What you might be observing is that the collation of the columns still remain the same as it originally was even though you changed the collation of the database. When a table/column is created, it takes on the default collation of the database (unless you specify otherwise). Collation of an existing column won't change automatically even if you change the default collation of the database. To change the collation of a column, you would need to alter the column - see here: http://msdn.microsoft.com/en-us/library/ms190920.aspx

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 11:29:10

thank you.

but it's boring to edit all the collation of columns in a database with alot of tables that have NVARCHAR type.
Do you know a query that alter the collation of columns where their type is NVARCHAR?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 11:38:07

quote:
Originally posted by Smart-Girl

thank you.

but it's boring to edit all the collation of columns in a database with alot of tables that have NVARCHAR type.
Do you know a query that alter the collation of columns where their type is NVARCHAR?

you can do a query like below


SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')' + CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END+  'COLLATE Persian_100_CI_AI'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('CHAR','VARCHAR','NVARCHAR')

Choose results as text option. The above query will generate the ALTER COLUMN query for each of character based columns to change its collation

Select the output of the above query, copy and paste to a new query window and execute it to change collation of all character based columns to your desired collation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 11:52:53

wOw!! it's interesting code!! :O

I have below error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'COLLATE'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 11:55:59

Yes, but not very useful. If there are indexes on the column, you would have to drop the indexes too...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 12:02:17

fortunately I didn't use any index for columns. but it's not bad to know the best and efficient query

visakh16's query:
ALTER TABLE sysdiagrams ALTER COLUMN name nvarchar(128) NOT NULL COLLATE Persian_100_CI_AI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:05:49

Do not alter internal Microsoft tables, please.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 12:11:34

quote:
Originally posted by SwePeso

Do not alter internal Microsoft tables, please.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

it's only one of the results of his/her query.
OK i'll remove it from a query with no successful run. ;-) but I'd like to know your reason. could you tell me pls.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:18:28

quote:
Originally posted by Smart-Girl

quote:
Originally posted by SwePeso

Do not alter internal Microsoft tables, please.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

it's only one of the results of his/her query.
OK i'll remove it from a query with no successful run. ;-) but I'd like to know your reason. could you tell me pls.

He meant to exclude system tables I guess
like below

like this


SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')' + CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END+  'COLLATE Persian_100_CI_AI'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN sys.objects o
ON o.name = c.TABLE_NAME
AND o.type = 'u'
AND o.is_ms_shipped=0
WHERE DATA_TYPE IN ('CHAR','VARCHAR','NVARCHAR')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:19:53

quote:
Originally posted by SwePeso

Yes, but not very useful. If there are indexes on the column, you would have to drop the indexes too...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

You could extend the same approach for indexes too and follow it up with this code. Didn't understand whats the issue with that.

Much better than applying a looping logic through the columns and then using a dynamic sql over it IMO especially for one time changes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Smart-Girl
Starting Member

15 Posts

Posted - 2013-07-22 : 12:23:45

quote:
Originally posted by visakh16

quote:
Originally posted by SwePeso

Yes, but not very useful. If there are indexes on the column, you would have to drop the indexes too...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

You could extend the same approach for indexes too and follow it up with this code. Didn't understand whats the issue with that.

Much better than applying a looping logic through the columns and then using a dynamic sql over it IMO especially for one time changes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Many Thanks!
I found it NOT NULL should be used at the end of statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:27:28

quote:
Originally posted by Smart-Girl

quote:
Originally posted by visakh16

quote:
Originally posted by SwePeso

Yes, but not very useful. If there are indexes on the column, you would have to drop the indexes too...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

You could extend the same approach for indexes too and follow it up with this code. Didn't understand whats the issue with that.

Much better than applying a looping logic through the columns and then using a dynamic sql over it IMO especially for one time changes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Many Thanks!
I found it NOT NULL should be used at the end of statement.

ah yes

the order should be swapped

as in


SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ') COLLATE Persian_100_CI_AI ' + CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN sys.objects o
ON o.name = c.TABLE_NAME
AND o.type = 'u'
AND o.is_ms_shipped=0
WHERE DATA_TYPE IN ('CHAR','VARCHAR','NVARCHAR')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources