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

Jan 25, 2009 · 2 min read  ·
Share on:

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:

************** 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.