SharePoint Server 2007 SP1: Incorrect syntax near 'COLLATE' When Joining a Farm

While attempting to add a new Indexing Server to my firm’s SharePoint farm using the SharePoint Products and Technologies Configuration Wizard, I ran into the following error when clicking Retrieve Database Names:

Incorrect syntax near ‘COLLATE’

************** Exception Text **************
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'COLLATE'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.PostSetupConfiguration.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.DatabaseTableWithColumnExists(String table, String column)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.GetV3WSSConfigurationDatabases()
at Microsoft.SharePoint.PostSetupConfiguration.ConnectConfigurationDbForm.GetDatabasesButtonClickEventHandler(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

The SharePoint_Config’s database and set to collation is set to Microsoft’s recommended collation for a SharePoint farm: Latin1_General_CI_AS_KS_WS while SQL Server 2005’s default collation on the master database is set to SQL_Latin1_General_CP1_CI_AS. For whatever reason, this is causing a very time consuming issue on our SharePoint farm. With the help of my SharePoint Consultant friend, Trevor, I was able to resolve it by doing the following:

  1. Ensuring the newly installed SharePoint server’s service pack matched that of the farm. Initially, the RTM was installed on the server but the farm was running SharePoint Server 2007 SP1.
  2. Typing in the Sharepoint config’s database name (SharePoint_Config) manually instead of relying on the “Retrieve Database Name” button to populate the drop-down list.

I know it’s not a solution so much as it is a workaround but after three days of failed configuration attempts, I really just wanted it to work and that did the trick.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SharePoint, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *