Incorrect syntax near COLLATE. Error When Trying to Join a SharePoint Farm

Problem Description

I encountered another nasty bug while rebuilding our Microsoft Office SharePoint Server (MOSS) 2007 Development environment (DEV). Since the time I originally created DEV, I installed SQL Server SP2 and also restored several legacy databases to support the development of the next version ("v2") of our solution. After running the SharePoint Products and Technologies Configuration Wizard on the SSP server to create the farm, I ran the wizard on the first front-end Web server in order to join it to the new farm. On the second step of the configuration wizard, I left the default option selected (Yes, I want to connect to an existing server farm) and then clicked Next. On the Specify Configuration Database Settings step, I typed the name of the SQL Server and then clicked Retrieve Database Names. At this point, I was presented with the "Unhandled exception" dialog displayed by the .NET Framework when something very bad happens. Error message: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature.

See help for the stored procedure sp_dbcmptlevel. Details: See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box. ************** Exception Text ************** System.Data.SqlClient.SqlException: Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. 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)

Solution Description

whenever we try to join the server farm, the web server scans for the databases in the SQL server. If it finds any legacy database in that instance of SQL server, the connection breaks throwing this error. It is important the the compatibility level of these databases are raised to the recent most before we try to join the sharepoint to a farm.

Check for the compatibility using the queries mentioned below. And once we find the database we upgrade it to the latest compatibility level.

sp_dbcmptlevel [ [ @dbname = ] dbname ] [ , [ @new_cmptlevel = ] version ]

example- suppose the db DBTEST is 6.0 compatible then we are changing its compatibility by using the query

sp_dbcmptlevel [ [ @dbname = ] DBTEST ] [ , [ @new_cmptlevel = ] 80 ]
version

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008