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
