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.
| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-01-11 : 10:50:55
|
m not sure how collation works exactly.After checking we found that the text column in syscomments is 29 times (meaning i guess in 29 databases) of collation Latin1_General_BIN and 13 times (in 13 databases) of collation SQL_Latin1_General_CP1_CI_AS .So this is my question pls:as you can see that in my script I have the columns sk.LoginName and o.text many times. If I choose to cast both columns to the collation SQL_Latin1_General_CP1_CI_AS. Will I have to put COLLATE SQL_Latin1_General_CP1_CI_AS after each occurence of those 2 columns in my script or just in one occurence.Tell me please if this is correct. I put COLLATE instruction in all the sk.loginname and o.text occurences in the script (I mean whenever there is loginname or o.text in the script I put COLLATE SQL_Latin1_General_CP1_CI_AS ) as in the following:use mosaikDB737exec sp_MSforeachdb'use  insert into mosaikDB737.dbo.SearchOutput1 select sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS as searchedTxt , object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line,PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text COLLATE SQL_Latin1_General_CP1_CI_AS) AS Position, '''' as dbName from syscomments as o inner join mosaikDB737.dbo.LoginListInput as sk on o.text like ''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%''ORDER BY searchedTxt,ProcName, Line, Position'select * from mosaikDB737.dbo.SearchOutput1 order by dbName, ProcNameI just chose to cast every thing to the collation SQL_Latin1_General_CP1_CI_AS. I hope that s Ok.Muchas Gracias |
|
|
|
|
|
|
|
collate usage question (sql 2000)