SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [SOLVED] change sql server collation- error: 5075
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  07:21:11  Show Profile  Reply with Quote
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
52309 Posts

Posted - 07/22/2013 :  07:35:22  Show Profile  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  07:58:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/22/2013 :  08:08:27  Show Profile  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  08:13:08  Show Profile  Reply with Quote
excellent!!! THANK U
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  08:24:12  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 07/22/2013 :  08:56:44  Show Profile  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  11:29:10  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/22/2013 :  11:38:07  Show Profile  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  11:52:53  Show Profile  Reply with Quote
wOw!! it's interesting code!! :O

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 07/22/2013 :  11:55:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  12:02:17  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30121 Posts

Posted - 07/22/2013 :  12:05:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do not alter internal Microsoft tables, please.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  12:11:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/22/2013 :  12:18:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/22/2013 :  12:19:53  Show Profile  Reply with Quote
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
Go to Top of Page

Smart-Girl
Starting Member

15 Posts

Posted - 07/22/2013 :  12:23:45  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/22/2013 :  12:27:28  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000