Hi,

I'm getting some wrong data types when generating a Typed View.

My view (A) is composed of two other views (B & C). The two sub-views generate the correct data types in LLBLGEN. When I check the data types for A in Visual Studio.Net and Enterprise, they are correct.

The two columns (named Brand and SubBrand) are supposed to be strings, but they are generated as Datetime and Integer, respectively.

They are generated incorrectly because LLBLGEN thinks they are date and integers in the properties screen. Obviously, this causes errors when retrieving data.

I have refreshed the catalog several times, deleted and re-added the Typed View, but nothing corrects the problem.

I don't know how to get LLBLGEN to use the correct data types in this case.

I'm using LLBLGEN Pro 1.0.2003.1 Final (April 30th 2004). Let me know if you need more information.

Thanks, Dave Anderson.

I don't recall a bug fix after that date which would influence the view field retrieval, so I'm a little puzzled why this would go wrong. Below I've pasted the query I use to retrieve all field information for a view.

SELECT  INFORMATION_SCHEMA.COLUMNS.*,  
    (SELECT COLUMNPROPERTY(OBJECT_ID(@sFullViewName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'Precision')) AS ColumnLength
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=@sViewName 
    AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='<schema>'
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ASC

@sFullViewName is something like 'database.dbo.viewname'. @sViewName is the viewname, and '<schema>' is something like 'dbo' (this isn't a parameter because it doesn't change for the loop)

If wrong types are reported, information_schema.columns has a problem.

Otis, Thank you for the quick response. You are right about the problem. I ran the query and the datatypes were wrong there too.

What is strange is that both Enterprise manager and Visual Studio.Net report the correct data types. Where are they getting there information? Does SQL Server store this info in two different places? And are they out of sync?

I'll keep you posted, but this definitely appears to be a SQL Server problem.

Thanks again, Dave Anderson.

OK, all is well now.

I edited the query, removed the offending columns, added them back. At that point the datatypes looked right using your handy query, so I figured everything else would be right.

In LLBLGEN, I retrieved the schema again, which revealed the new correct data types were recognized. Then regenerated and ran my program. Voila! It all works again.

I can't thank you enough for you rapid, concise, and accurate advice.

Regards, Dave Anderson.

Thanks for the update, Dave, and you're welcome

VS.NET uses OleDB and the OleDbConnection.GetOleDbSchemaTable(). This is a very buggy approach, as that routine is often not reporting the right info. Enterprise manager uses SQLDMO and OleDB, more accurate but not usable with LLBLGenPro as not everyone has SQLDMO installed