A developer from my company was pulling his hair out for a while this week when he was unable to create a UDF in a certain database. The script was failing with this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
So he double checked and triple checked the syntax and even ended up scripting an existing function from another database but getting the same result. He then emailed me asking what could be wrong and something in his email provided a good clue for me - he was able to create the same function in the master database. I checked the database compatibility level and sure enough, it was set to 65. Since SQL Server 7.0 and prior did not have UDFs, a create function statement fails with the “incorrect syntax...“ error. So the fix was simple - run sp_dbcmptlevel and set compatibility to 80. Now we just need to test the application and make sure nothing was relying on the 6.5 SQL behavior.
This was an old database migrated from SQL Server 6.5 to 7.0 and somehow the compatibility setting was left at 6.5. It's been a while since I've done any 6.5 to 7.0 migration so I don't remember if the setting ends up to be 65 or 70 after database upgrade. Then the 7.0 to 2000 migration didn't change the setting to 80 like it did for the 7.0 databases.