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 Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. 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 *

*