Wednesday, June 9, 2010

External Content Types Error Message

Using SharePoint Designer 2010, whiling trying to create a new External Content Types, I got the following error message while trying to expand the Tables/Views under the database:

"Can't complete refresh"
"Skipping the following table because it is not configured for use"
"Line 1: Incorrect syntax near '('".

The Routines folder expands fine.

The data source type is SQL Server,  For SQL Server Connection, I have "Connect with User's Identity"
What could be wrong? Is there a minimum SQL Server version requirement? The version for this SQL Server is 8.0.2050, SQL Server 2000 SP4. I can connect to another sql server with version "9.0.3054", SQL Server 2005 SP2.

I unerstand that there is a minimum requirement for SQL Server for SharePoint. SharePoint Server 2010 requires that its database server must be a 64-bit version of one of the following: Microsoft SQL Server 2008 R2, SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2, or SQL Server 2005 with SP3 and Cumulative Update 3.

It does look like the minimum requirement for SQL Server for BCS purpose is SQL 2005 or higher, according to this post. Also you  can work around this problem by using stored procedure or web services wrapper.

Or maybe it is time to upgrade that sql server.