2011-02-24

List of collations

I would like to see a list of available collations on a given SQL Server database installation, and a combination of fn_helpcollations() and COLLATIONPROPERTY() gives a quick answer.
SELECT
 [name] 
 ,COLLATIONPROPERTY([name], 'CodePage') AS [CodePage]
 ,COLLATIONPROPERTY([name], 'LCID') AS [LCID]
 ,COLLATIONPROPERTY([name], 'ComparisonStyle') AS [ComparisonStyle]
 ,COLLATIONPROPERTY([name], 'Version') AS [Version]
 ,[description]
FROM ::fn_helpcollations();
On a SQL Server 2008 R2 (10.50.1600) I get 2397 collations.
I do miss the sort order on the SQL collation as the table in "Selecting a SQL Server Collation".

A search for collation objects points at the stored procedures sys.sp_tablecollations, that uses the view sys.spt_tablecollations_view. It looks like this view is placed in the database "mssqlsystemresource", but this I cannot access direct.

Reference

MSDN Library: "Selecting a SQL Server Collation".
MSDN Library: "Collation and Unicode Support".
Alexander Barkov: "MS SQL Server collation charts".
The Unicode Consortium: "Unicode Collation charts".
G-Productions: "sys.sp_tablecollations".

2011-02-10

Windows 2008 Firewall

There are several guides on how to configure the Windows Server 2008 (R2) firewall to allow SQL Server access.
I would like to add some items to the rule definition:

  • Specify both program (sqlservr.exe) and service.
  • Specify the interface type, typically Local area network.
Also I think other issues like the scope should be considered to make the rule as tight as possible.

Reference

MSDN: "How to: Configure a Windows Firewall for Database Engine Access"
Shawn Hernan: "SQL Server and the Windows Server 2008 Firewall"
Ashish Kumar Mehta: "Configure Windows Firewall for SQL Server 2008 Database Engine in Windows Server 2008 R2"