change collation in a view 1

[djj55]

Hello,
I would like to change the collation of a column in a view from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS. The view is from another database (same server) but has a different collation.

Currently we are using SQL 2000 but will be moving to SQL 2008.

Also is there a way to prevent edits? If I use triggers in 2008 do I need three INSTEAD OF triggers (one each insert, update, delete)?

Thank you,


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

changing the collation is a view is pretty simple. Just use the collate clause. Like this.

Create View MyViewName
As
Select IntCol,
       VarCharCol Collate Latin1_General_CI_AS
From   OtherDatabase.dbo.TableName

What do you mean by 'Prevent Edits'? Do you want certain logins to have read-only access to certain tables?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

Changing collaction can be done using COLLATE:

SELECT 
TABLE_NAME COLLATE Latin1_General_CI_AS,
TABLE_NAME COLLATE Albanian_BIN
FROM INFORMATION_SCHEMA.TABLES
[code]

preventing edits can easily be done by either limiting users permissions, depending on how your security model is implemented or by putting the database in read only mode.

 

[djj55]

Thank you for the answer. By playing around I came up with

SELECT strCol COLLATE Latin1_General_CI_AS AS strCol

.

As to the 'prevent edits' I want the view to be read only for everyone.

Thanks again,


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[djj55]

Jamfool, the obvious is always the best. I should have thought to put the database into read only as are plan is to have a separate database for the views.

Thank you both,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!