Hi All,

Here is my setup

• LLBLGen Pro Version = 2.6 Final ( June 6th 2008 ) • RunTime File version = 2.6.08.0624 • Adapter Templates .NET 2.0 • SQL Server 2005 and • SQL CE 3.1 (9.0.242.0)

We ran into a small issue today with SQL CE 3.1 and SQL Server 2005. We have generated a SQL CE project to act as a local cache for a SQL 2005 database. In the 2005 db we set some of our fields to be of type varchar(max) converting them from text / ntext as outlined in BOL http://msdn.microsoft.com/en-us/library/ms143729.aspx They say that TEXT, NTEXT and IMAGE are Deprecated and should be replaced.

Textpointers TEXT, NTEXT and IMAGE data types Use varchar(max), nvarchar(max), and varbinary(max) data types

When we generated the CE Database from the SQL Server database we converted Varchar(max) to ntext. This caused a problem with some of the queries that the DQE was emitting. The query was "SELECT DISTINCT XYZ FROM ABC" where XYZ was a ntext on CE but varchar(max) on Sql05. The error that gets raised is that "Distinct" can not be used against a column of type ntext.

It makes sense that the DQE would emit this as it was generated against a varchar(max) column.

We solved the issued by changing the types back to text on the server and regenerated. The only reason I am posting is that the LLLBLGen Pro docs say this about CE

SqlServer type SmallMoney is converted to Money in parameters. SqlServer type Char is converted to NChar in parameters. SqlServer type VarChar is converted to NVarChar in parameters. SqlServer type Text is converted to NText in parameters.

I don't know you would want to update the DQE to also convert SqlServer type Varchar(max) to Ntext in parameters as well.

--Chris

First of all, I think it's wise for you to upgrade to SQLServer CE 3.5, as that version is IMHO much better.

Now, about the problem you ran into. The type conversion list you refer to is for supporting the types at all, as CE doesn't have a 'varchar' type for example

In your case, that's not necessary, CE supports ntext and image. If we'd change the type, it would give a problem for people who have a CE db with ntext fields.

So rule of thumb: types of fields have to be the same if you want to share code between a sqlserver server version and ce version. As CE doesn't support some types, like varchar, the dqe makes conversions for them so the equivalent type, i.e. nvarchar for varchar, should be used as type in the CE table.

That these types are 'deprecated' is a bit too much I think, they're still supported in 2008 for example.

I'll see if it's possible to have a varchar(max) field on the server and use the same code on a CE db, because if not, the list you quoted is incomplete.

(edit) CE 3.1 at least doesn't have nvarchar(max) nor varbinary(max), so equivalents have to be used, which indeed should be ntext and image in CE. I'll see if I can update the DQE for this.

Could you see if the attached DQE fixes your problem? (compatibility mode has to be set to ce3x or ce35)

Otis wrote:

Could you see if the attached DQE fixes your problem? (compatibility mode has to be set to ce3x or ce35)

Frans,

The DQE worked fine. Thanks for the quick update.

Also I agree with you that we should upgrade to 3.5. I'm working that issue already!

Chris