ACC2002: Empty Result Set When You Run a Scalar Function Before You Close It (287463)

SYMPTOMS

When you create or modify a scalar function, and you then try to run the function without first closing it, the scalar function returns an empty column in the result set and displays the scalar function name in quotation marks as the column name. After you close the function and then run it from the Database window, the result set is populated, and the scalar function name, no longer with quotation marks, appears as the column name.

CAUSE

With scalar functions that have not been closed after initially being created or modified, Access improperly creates and displays the function column name, including quotation marks. This problem occurs when either a new scalar function has just been created or when an existing scalar function has been modified, and you are transitioning from Design to Datasheet view.

MORE INFORMATION

Steps to Reproduce the Behavior

Open the sample project NorthwindCS.adp connected to Microsoft SQL Server 2000.
On the Queries tab, click New. Click Create Text Scalar Function, and then click OK. Type or paste the following code:
```
CREATE function fn_scalar_1 (@parm int)
  returns bigint
as begin 
  return @parm * 5000 
end
					
```
On the Query menu, click Run.
Click Yes to accept the default function name.
When you are prompted, enter 2 for the parameter value. Note that a datasheet that contains no value appears. Additionally, the column heading for the function is "fn_scalar_1", including the quotation marks, instead of only the name, fn_scalar_1.
Save and then close the function.
Run the function to ensure it executes correctly.
Switch the function to Design view.
Change the multiplier in the function from 5000 to 50000.
Run the function again. Click OK to save the function.
Enter a value for the parameter. Note that again the datasheet is blank. However, if you close and re-run the function, it executes properly.

REFERENCES

For more information about scalar functions, refer to SQL Server Books Online at the following Microsoft Web site:

http://msdn.microsoft.com/library/psdk/sql/getstart_4fht.htm