| Author |
 Topic  |
|
|
Smart-Girl
Starting Member
15 Posts |
 Posted - 07/22/2013 : 07:21:11
|
hi every body
please help me to change the collation of my database. it's give me below error
 |
Edited by - Smart-Girl on 07/22/2013 12:36:30
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 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
India
52326 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 08:13:08
|
| excellent!!! THANK U |
|
|
|
Smart-Girl
Starting Member
15 Posts |
Posted - 07/22/2013 : 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
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 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
India
52326 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 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
Sweden
30421 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 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
Sweden
30421 Posts |
Posted - 07/22/2013 : 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 - 07/22/2013 : 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.
|
Edited by - Smart-Girl on 07/22/2013 12:12:15 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 07/22/2013 : 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
India
52326 Posts |
Posted - 07/22/2013 : 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
|
Edited by - visakh16 on 07/22/2013 12:21:50 |
|
|
|
Smart-Girl
Starting Member
15 Posts |
Posted - 07/22/2013 : 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
India
52326 Posts |
Posted - 07/22/2013 : 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
|
|
|
| |
Topic |
|
[SOLVED] change sql server collation- error: 5075