November 23, 2009 at 4:02 am
Hi,
I don't undestand the behavior of SQL Server in the following situation:
I have made a view, this view is in a database. this database as the collation "Latin1_General_CI_AS"
When i select this view from other database (through query window) it gives me the folowing error:
"Cannot resolve collation conflict for replace operation"
This is a collation problem.
My other database(from where i'm calling the view) as the collation "SQL_Latin1_General_CP1_CI_AS".
Do you get collation problems when calling an object from other database that as diferente collation, if it has a replace? if yes, why?
I usually get collation conflit when i'm comparing a column from a database with a column from other database and the two character data columns have different collations.
I mean, when i compare two columns that have diferent collations, then i receive the error, but just to call an object from other database... (what am i comparing ?)
Do you Masters know why this happens?
Thank you.
THE CODE OF THE VIEW:
alter view teste43
as
select
a.NIF+isnull(filial_number,'') as NIF,a.COD_MET_TRIBUTARIO,a.NOME,a.STATUS,a.COD_PROVINCIA,
a.COD_REP_FISCAL,a.COD_MUNICIPIO, a.NIF_ANTIGO as RGC, replace(convert(varchar(10),a.dt_inicio ,120),'-','') DT_INICIO,b.COD_CAE
from contribuintes as a
left join singulares as b
on a.nif_antigo = b.nif_antigo
where
(a.status ='a' or a.status ='c') and a.nif is not null and a.nif_antigo is not null
and a.cod_rep_fiscal is not null and (a.cod_met_tributario ='tp1' or a.cod_met_tributario ='tp2')
November 23, 2009 at 5:39 am
Hello,
i found this in BOL
REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
Examples
The following example replaces the string cde in abcdefghi with xxx.
Copy Code
SELECT REPLACE('abcdefghicde','cde','xxx');
GO
Here is the result set.
Copy Code
------------
abxxxfghixxx
(1 row(s) affected)
The following example uses the COLLATE function.
Copy Code
SELECT REPLACE('Das ist ein Test' COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO
i hope this might help you
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply