CREATE DATABASE failed. COLLATE clause cannot be used with the FOR ATTACH option.

Microsoft.SQLServer.2000.CREATE_DATABASE_failed__COLLATE_clause_cannot_be_used_with_the_FOR_ATTACH_option (Rule)

Knowledge Base article:

Causes

Your CREATE DATABASE statement included both the COLLATE clause and the FOR ATTACH option. These options cannot be used together in a CREATE DATABASE statement. When you use the FOR ATTACH option, you are attaching existing database files, and the COLLATION of those files will be applied to the new database.

Resolutions

Do not specify both the COLLATE clause and the FOR ATTACH option in the CREATE DATABASE statement.

External

If you need to change the collation of the database you attached, see "How to transfer a database from one collation to another collation in SQL Server" in Microsoft Knowledge Base article 325335.

Element properties:

TargetMicrosoft.SQLServer.2000.DBEngine
CategoryEventCollection
EnabledTrue
Event Source$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
RemotableTrue
Alert Message
CREATE DATABASE failed. COLLATE clause cannot be used with the FOR ATTACH option.
{0}
Event LogApplication
CommentMom2005ID='{AB2977C6-56D9-45DD-947E-772F6934F469}'

Member Modules:

ID Module Type TypeId RunAs 
EventDS DataSource Microsoft.Windows.EventProvider Default
GenerateAlert WriteAction System.Health.GenerateAlert Default

Source Code:

<Rule ID="Microsoft.SQLServer.2000.CREATE_DATABASE_failed__COLLATE_clause_cannot_be_used_with_the_FOR_ATTACH_option" Target="SQL2000Core!Microsoft.SQLServer.2000.DBEngine" Enabled="true" Comment="Mom2005ID='{AB2977C6-56D9-45DD-947E-772F6934F469}'" Remotable="true">
<Category>EventCollection</Category>
<DataSources>
<DataSource ID="EventDS" TypeID="Windows!Microsoft.Windows.EventProvider">
<ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
<LogName>Application</LogName>
<Expression>
<And>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery>PublisherName</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDisplayNumber</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005RegularExpression</Operator>
<Pattern>^(17052|17055)$</Pattern>
</RegExExpression>
</Expression>
<Expression>
<RegExExpression>
<ValueExpression>
<XPathQuery>EventDescription</XPathQuery>
</ValueExpression>
<Operator>MatchesMOM2005BooleanRegularExpression</Operator>
<Pattern>((^.*1812 and (not ^.*\n.*1812)) or (^.*1812.*\n.*1812)) AND (NOT ^.*[0-9]+.*1812) AND (NOT ^.*1812[0-9]+)</Pattern>
</RegExExpression>
</Expression>
</And>
</Expression>
</DataSource>
</DataSources>
<WriteActions>
<WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">
<Priority>1</Priority>
<Severity>1</Severity>
<AlertOwner>$Data/PublisherName$</AlertOwner>
<AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2000.CREATE_DATABASE_failed__COLLATE_clause_cannot_be_used_with_the_FOR_ATTACH_option.AlertMessage"]$</AlertMessageId>
<AlertParameters>
<AlertParameter1>$Data/EventDescription$</AlertParameter1>
</AlertParameters>
<Suppression>
<SuppressionValue/>
</Suppression>
</WriteAction>
</WriteActions>
</Rule>