Collation on union view - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Collation on union view

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-14 : 05:44:02

I am trying to create a union view between two tables of the same name, across two SQL databases, but the database collation is different on both, even though they are on the same instance of SQl Server.
I keep getting a collation conflict because of this and am not that up on collation to know how to solve the problem.
Is there anybody that could assist?

I can supply sample code if necessary.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-14 : 09:15:50

convert the collation from one to another.

a.col1 = b.col2 COLLATE <collation name of a>

-----------------
'KH'
if you can't beat them, have someone else to beat them

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-14 : 12:57:37

I understand how to convert the collation when comparing two values in an '=' to operation, but this is a simple union view involving
two select queries.

Where do I set the 'collate' expression?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-14 : 18:00:56

Post your query

-----------------
'KH'
if you can't beat them, have someone else to beat them

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-15 : 17:31:33

View as requested

CREATE VIEW test_SL_TRANSACTIONS
AS
SELECT ST_ALOC_POINTER,
'c' + ST_HEADER_KEY AS ST_HEADER_KEY
FROM ABC.dbo.SL_TRANSACTIONS

UNION ALL

SELECT ST_ALOC_POINTER,
ST_HEADER_KEY
FROM SL_TRANSACTIONS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 17:56:15

same concept. Convert the collation from one to another

CREATE VIEW test_SL_TRANSACTIONS
AS
SELECT ST_ALOC_POINTER COLLATE <collation_name>,
'c' + ST_HEADER_KEY COLLATE <collation_name> AS ST_HEADER_KEY
FROM ABC.dbo.SL_TRANSACTIONS

UNION ALL

SELECT ST_ALOC_POINTER,
ST_HEADER_KEY
FROM SL_TRANSACTIONS

Collation Name is the collatino of the 2nd query in the union

-----------------
'KH'

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-16 : 04:45:02

I take it that you have to specify the collation name, for every field in the query, where the field type is affected by collation.

In this case both fields are VARCHAR()

Many thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-16 : 04:58:18

Yes. Only those field with collation that you need to specify. Others data type like datetime, int are not required

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources