Author |
Topic |
Smart-Girl
Starting Member
15 Posts |
Posted - 2013-07-22 : 07:21:11
|
hi every bodyplease 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 MVPhttp://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;GOALTER DATABASE accSET SINGLE_USERWITH ROLLBACK IMMEDIATE;GOALTER DATABASE accCOLLATE Persian_100_CI_AIGOALTER DATABASE accSET MULTI_USER;GO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 belowSELECT '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.COLUMNSWHERE 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 collationSelect 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 MVPhttp://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!! :OI have below error:Msg 156, Level 15, State 1, Line 1Incorrect 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 queryvisakh16'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 guesslike belowlike thisSELECT '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 cINNER JOIN sys.objects oON o.name = c.TABLE_NAMEAND o.type = 'u'AND o.is_ms_shipped=0WHERE DATA_TYPE IN ('CHAR','VARCHAR','NVARCHAR') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Many Thanks!I found it NOT NULL should be used at the end of statement.
ah yesthe order should be swappedas inSELECT '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 ' ENDFROM INFORMATION_SCHEMA.COLUMNS cINNER JOIN sys.objects oON o.name = c.TABLE_NAMEAND o.type = 'u'AND o.is_ms_shipped=0WHERE DATA_TYPE IN ('CHAR','VARCHAR','NVARCHAR') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|