Re: SharePoint Collation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Art Frank (ArtFrank@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
On my SQL Server 2008 system, I just tried to create a new database for
my SharePoint farm config database (WSS 3.0) and I can create it using
the server's collation (SQL_Latin1_General_CP1_CI_AS) but SharePoint
doesn't like that, it wants Latin1_General_CI_AS_KS_WS. So, I dropped
the db, and when I tried recreating it with the collation for SharePoint
I got this error:

There is insufficient system memory in resource pool 'internal' to run
this query. CREATE DATABASE failed. The default collation of
'SharePoint_Config' cannot be set to 'Latin1_General_CI_AS_KS_WS'. The
statement has been terminated. (Microsoft SQL Server, Error: 701)

In researching this problem, it appears that running a database with
collation that is different from the server's collation is not
recommended.

Is SQL Server throwing this error to stop me from shooting myself in the
foot? Do I need to create a separate instance of SQL Server that has the
correct collation? Do ALL SharePoint farms have their own SQL Server
instance?

The error is, um, surprising. I've never seen in this context. But I can
have a guess on what is going on.

When SQL Server creates a new databases, it copies the model database.
The model database uses the server collation. Thus, if you create a
database with a different database collation, the system tables needs
to be rebuilt, and presumably this fails for some reason. Still this
is surprising since the system tables of model are small. Well, at
least they are normally. If someone has filled you model with tons of
crap there could be an issue.

If I ran into this I would
1) See that model has not been inflated.
2) Restart the server and try again.

As for the recommendation of not mixing collation on a server, this
stems from the fact that you can easily get surprises with temp tables
unless you code for it. Whether they have done so in SharePoint I
don't know.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Turkish character problem in SQL Server 2000
    ... I'm trying to migrate a Sybase SQL Anywhere database to SQL server 2000. ... have a problem when I choose "Turkish_CI_AS" as the database collation: ... When I use lowercase Ithere's no error. ...
    (microsoft.public.sqlserver.programming)
  • SharePoint Collation
    ... On my SQL Server 2008 system, I just tried to create a new database for my ... The default collation of 'SharePoint_Config' ... SharePoint farm database in my existing SQL Server instance. ...
    (microsoft.public.sqlserver.server)
  • Re: Different collations in a database
    ... The SQL Server itself is correctly setup to use Finnish_Swedish_CI_AS, ... the fields back to database default. ... database with the correct collation. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • RE: Advice on Create a SQL Server
    ... Use the default collation. ... Mode" - it depends on how you will be connecting to your database. ... domain, and running on a windows domain account, it is better to run under ... to SQL Server using a username/password. ...
    (microsoft.public.sqlserver.setup)
  • Re: Database collation error after moving to a remote sql server
    ... > I've recently my SMS 2003 database to a remote sql server (it was ... > Server]Cannot resolve collation conflict for equal to operation. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)