sql server 2005 varchar(max) and coldfusion cfprocparam cfsqltype

For the applications that my team supports and builds, we are finally making the move from SQL Server 2000 to SQL Server 2005. We have been running in SQL 2000 compatibility mode for a while and are now making the switch.

We wanted to use the new varchar(max) data type which is new to SQL Server 2005, but we didn't know what cf_sql_type in the cfprocparam tag mapped to varchar(max). So, I did some testing.

I created a datasource to our new 2005 database I'll call tempworks. In tempworks we have a table person with columns: personID INT IDENTITY, userName VARCHAR(100), and personNotes VARCHAR(max). We use stored procedures to access the database for all transactions. So I built an insert proc with a call that looks like this:

This worked with a cfsqltype of CF_SQL_CLOB, but really VARCHAR(max) is not a CLOB or TEXT in SQL Server. So, I used CF_SQL_LONGVARCHAR in place of CF_SQL_CLOB and that worked as well. I was inserting personNotes with a length of over 140,000 characters to give it a real test. I also changed my datasource settings to "Enable long text retrieval (CLOB)." and it worked with both cfsqltypes with that setting on and off. I am using ColdFusion 8.0.1 developer edition.

I could not find any documentation for this mapping, so I thought I would post it for others who are looking for this information as well. Oh yeah, and so I don't forget this either!