June 2, 2009 at 10:33 am
Maybe you guys can give me a quick answer on this one. On all my system and user databases on my SQL Server 2008 box my database collation is SQL_Latin1_General_CP1_CI_AS (English) which is just fine. However, after installing Reporting services, the 2 ReportServer dbs it added now have a collation of Latin1_General_CI_AS_KS_WS (Sharepoint 2007?). Why does SQL install a different database collation for just these 2 Report dbs on my server? Will this cause future collation conflicts (468 errors) on my db server if I ever need to do cross database joins between them and other dbs? Also, would it hurt changing those to SQL_Latin1_General_CP1_CI_AS so that all dbs on this box have the same collation? Thanks ahead of time. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 2, 2009 at 1:39 pm
However, after installing Reporting services, the 2 ReportServer dbs it added now have a collation of Latin1_General_CI_AS_KS_WS (Sharepoint 2007?).
That is the standard collation for the ReportServerDB for both SQL Server 2005 and 2008 and I don't think you will run into issues because you are not accessing ReportServerDB directly. I have used both and I am not aware of any known issues.
Kind regards,
Gift Peddie
June 2, 2009 at 4:05 pm
Thanks kindly. Is that a Sharepoint 2007 collation for use with SQL 2008 SSRS? I did not find it in BOL 2008 however. Thanks again. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 2, 2009 at 4:16 pm
That may be the reason for using that collation for SSRS because it is required for SharePoint.
http://support.microsoft.com/kb/843578
Kind regards,
Gift Peddie
June 3, 2009 at 8:15 am
Thanks again for the info. I wouldn't be surprised how many people do not know about this. That collation is probably needed in those dbs to publiish SQL 2008 SSRS reports to the web I'll bet. Otherwise, why would SQL Server need that collation? It isn't even mentioned in BOL 2008.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 3, 2009 at 9:33 am
I am surprised you could not find it in BOL but it is always in the online MSDN docs check the link below for the collation that includes the collation used for SSRS and SharePoint.
http://msdn.microsoft.com/en-us/library/ms144250.aspx
Kind regards,
Gift Peddie
June 3, 2009 at 10:20 am
I did not find this collation referenced (Latin1_General_CI_AS_KS_WS) in BOL 2008, nor did I find it in the above link you just supplied.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 3, 2009 at 10:31 am
My bad I gave you the wrong link it is related to using Windows collation, so here is the correct link.
http://msdn.microsoft.com/en-us/library/ms143515.aspx
Kind regards,
Gift Peddie
June 3, 2009 at 10:42 am
I still do not see that specific collation I mentioned above or SharePoint 2007 referenced in that document you just resent.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 3, 2009 at 10:59 am
It is there you just need to read the whole page to understand it, but here is the relevant part of that page I just posted because the latin general just means the latin alphabet because Chinese, Japanese and Korean use alphabets that is more than 2000 characters.
_CI_AS_KS_WSCase-insensitive, accent-sensitive, kana-sensitive, width-sensitive
This is the collation you posted and below is the explanation of what it is so go back and read the page and you will understand. And post again I can explain most things on this issue. CI is case insensitive AS is accent sensitive.
Accent-sensitive (_AS)
Distinguishes between accented and unaccented characters. For example, 'a' is not equal to '?'.
If this option is not selected, SQL Server considers the accented and unaccented versions of letters to be identical for sorting purposes.
Kana-sensitive (_KS)Distinguishes between the two types of Japanese kana characters: Hiragana and Katakana.
If this option is not selected, SQL Server considers Hiragana and Katakana characters to be equal for sorting purposes.
Width-sensitive (_WS)Distinguishes between a single-byte character and the same character when represented as a double-byte character.
If this option is not selected, SQL Server considers the single-byte and double-byte representation of the same character to be identical for sorting purposes.
Kind regards,
Gift Peddie
June 3, 2009 at 3:02 pm
Understand the breakdown of the codes, but how does all that tell me or anyone else it is a SharePoint 2007 specific collation?
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 3, 2009 at 4:04 pm
I understand your question but SharePoint collation requirements are documented in TechNet because SharePoint unlike SQL Server which is both MSDN and TechNet product SharePoint is a TechNet only product. So here is the documentation of SharePoint database requirements collation included.
http://technet.microsoft.com/en-us/library/cc288606.aspx
Kind regards,
Gift Peddie
June 3, 2009 at 4:16 pm
Understand, but if SQL Server 2008 SSRS is going to use that Sharepoint 2007 collation automatically when it creates its ReportServer dbs on a SQL Server db server, you would think that would bear a mention in SQL 2008 BOL and I still cant find any reference to that there. But thanks for the Technet link anyway...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 3, 2009 at 5:28 pm
Microsoft leaves out a lot of relevant information in SQL Server so I just help as needed, but it maybe related to SSRS being released in 2004 while the first SharePoint was 2003 so the documentation time could be the issue. You could add this comment to the link with the Windows collation explanation page.
Kind regards,
Gift Peddie
June 4, 2009 at 10:09 am
Thanks very much. You really helped me out filling in the hole here..Microsoft does need to spell this out in BOL 2008 in my opinion, particularly if people start doing cross database queries into those dbs in the future and they start getting 468 errors....If database creation is NOT always using the default server collation, then that needs to be documented in BOL.....
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply