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 for .cfm executable ------------------------- <!--- Problem with nvarchar(max) in system views. ColdFusion MX7 & SQL Server 2005 ---> <!--- In the following query, d.name is of type sysname d.type_desc is of type nvarchar(60) d.definition is of type nvarchar(max) ---> <cfquery name="GetConstraintDefinition" datasource="sqlchap"> select d.name as COL1_SYSNAME, d.type_desc as COL2_NVARCHAR60, isnull(d.definition, 'NULL') as COL3_NVARCHARMAX from sys.default_constraints d join sys.columns c on c.object_id = d.parent_object_id and c.column_id = d.parent_column_id where d.parent_object_id = OBJECT_ID(N'dbo.dtproperties', N'U') </cfquery> <cfdump var='Selecting directly from system view "sys.default_constraints:'> <cfdump var=#GetConstraintDefinition#> <br> <!--- The results from the above <cfdump> statement are as follows: COL1_SYSNAME COL2_NVARCHAR60 COL3_NVARCHARMAX ------------------------------ ------------------ ---------------- DF__dtpropert__versi__6E022284 DEFAULT_CONSTRAINT NULL However, if the query is pasted into Query Analyzer and run, the result is: COL1_SYSNAME COL2_NVARCHAR60 COL3_NVARCHARMAX ------------------------------ ------------------ -------------- DF__dtpropert__versi__6E022284 DEFAULT_CONSTRAINT (0) This result is saying that one of the fields in the table has a default value of 0. The problem is that the nvarchar(max) field in the system view is not correctly displayed by ColdFusion, even though the nvarchar(60) field IS displayed correctly. ---> <!--- Now create a temp table with the same variable types. ColdFusion is able to successfully retrieve the nvarchar(max) column if it is filled from a literal, but not if it comes from a system view! ---> <cfquery name="GetTempTableRecord" datasource="sqlchap"> set nocount on create table ##foo (COL1_SYSNAME sysname, COL2_NVARCHAR60 nvarchar(60), COL3_NVARCHARMAX nvarchar(max) ) insert ##foo values ('TheSysname',N'TheNvarchar60',N'TheNvarcharMax is correctly displayed here.') insert ##foo select d.name as COL1_SYSNAME, d.type_desc as COL2_NVARCHAR60, isnull(d.definition, 'NULL') as COL3_NVARCHARMAX from sys.default_constraints d join sys.columns c on c.object_id = d.parent_object_id and c.column_id = d.parent_column_id where d.parent_object_id = OBJECT_ID(N'dbo.dtproperties', N'U') set nocount off select * from ##foo drop table ##foo </cfquery> <cfdump var='Selecting directly from table in tempdb:'> <cfdump var=#GetTempTableRecord#> <br> <!--- Retry the prior experiment using varchar(4000) rather than an nvarchar(max). ---> <cfquery name="GetVarchar4000" datasource="sqlchap"> set nocount on create table ##foo (COL1_SYSNAME sysname, COL2_NVARCHAR60 nvarchar(60), COL3_VARCHAR4000 varchar(4000) ) insert ##foo values ('TheSysname',N'TheNvarchar60',N'TheNvarcharMax is correctly displayed here.') insert ##foo select d.name as COL1_SYSNAME, d.type_desc as COL2_NVARCHAR60, isnull(convert(varchar(4000), d.definition), 'NULL') as COL3_VARCHAR4000 from sys.default_constraints d join sys.columns c on c.object_id = d.parent_object_id and c.column_id = d.parent_column_id where d.parent_object_id = OBJECT_ID(N'dbo.dtproperties', N'U') set nocount off select * from ##foo drop table ##foo </cfquery> <cfdump var='Selecting directly from table in tempdb using varchar(4000) not nvarchar:'> <cfdump var=#GetVarchar4000#> <br> <br> <cfdump var='All three of the above queries work correctly when pasted into Query Analyzer or Management Studio.'> <cfabort> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268255 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4