NVARCHAR(MAX) not returned from system views

[parmstrong]

I have a problem with SQL Server 2005 system views. In SQL 2005, system tables (e.g. sysobjects, syscolumns) are now hidden and their contents are made available through special "system views". We have a CF data dictionary application here we have been using for years with SQL 2000 and now need to port it to SQL 2005.

While converting my <cfquery> tags, I have found that SELECTs against ordinary tables return nvarchar(max) values just fine, but SELECT's against "system views" always return NULL from an nvarchar(max).

The enclosed .cfm page has several queries intended to be run in SQL 2005. Each one works perfectly when it is pasted into Query Analyzer or Management Studio, and each one returns NULL when run in ColdFusion. I am running CFMX developer version 7.0.2.

Can anyone get these queries to run with ColdFusion? I am REALLY hoping to not have to re-write this application in some other language!

---------------------------tear here--------------------------------------------

[paross1]

Does it make a difference if you try to CAST the column to a different data type?

Phil

[parmstrong]

The funny thing is, no! You would think that it would help to cast, but it doesn't. The third example tries to use the convert() function.

The even weirder thing is that it doesn't even help to select the nvarchar(max) into another table's varchar(4000) field, as long as the query is running under ColdFusion.