I came across a very strange and unexpected problem with a SQL Server 2005 UDF yesterday that continues to puzzle me. First, the cast of players:

  • ColdFusion MX 7
  • SQL Server 2005
  • A table-valued function
  • Puzzled developers

In case you are not familiar with table-valued functions, they are functions in SQL that return a table, as opposed to the more traditional scalar value.

We had to change the data type for one of the columns in the returned table from decimal to text to fix a bug. Nothing a simple ALTER FUNCTION statement could not handle.

Script, run, test, same error, what?

Maybe something is cached on the SQL Server side. Let's try dropping and recreating the function.

Script, run, test, same error, ???

Something very odd was happening. Only after restarting the ColdFusion Application Server service did the error go away.

I am still searching for an answer. Surely ColdFusion does not cache database object information...?