How to Convert DLookUp( ) to SQL2000 UserDefinedFunction

by jeff » Sun, 06 Apr 2003 02:32:22

Hi,

I'm an Access97/2000 programmer but now I started to use
Access2002 for my Front-end using SQL2000 as Backend, I'm
having a problem on how to write a UserDefinedFunction
for SQL2000 that will work exactly as DLookUp
(<fldname>,<tablename>,<criteria>) Function of
Access97/2000 and must be able to used it in a Select
statement for SQL2000.

example:
SELECT Customers.strCustomerID, DlookUp
("CompanyName","Company","strCustomerID = '" &
Customers.strCustomerID & "'") as CompanyName FROM
Customers

Thanks,
Jeff

Top

1. Unable to retrieve the text from UserDefinedFunction object (system_function_schema)

User-defined functions that are owned by 'system_function_schema'
cannot be viewed via Enterprise Manager (or any SQL-DMO client). The
code for the UDFs is readily available in the syscomments table, yet
Enterprise Manager can't seem to retrieve it via the DMO. This
appears to be true of system UDFs as well as user UDFs. I can create
a UDF and view it using Enterprise Manager or DMO; I change the owner
to system_function_schema and an error is returned when attempting to
view it from Enterprise Manager or DMO.

I'm not sure where the 'system_function_schema' owner/user is defined,
but I do know that it can be used to set up a server-wide function --
I've used it to make my functions available to ALL databases on a
server, but I'm not sure that explains why Enterprise Manager can't
see the code? Could it be that the 'system_function_schema' is not an
actual user and that somehow confuses the server? Incidentally, the
DMO does properly return other properties associated with the function
(name, owner, etc), but not the text.

The exact text of the error message is:

Error 0: [SQL-DMO]Unable to retrieve the text from UserDefinedFunction
object 'system_function_schema.fn_trace_gettable'.

(where fn_trace_gettable represents any function owned by
system_function_schema)

Any ideas why this happens? Is it a bug in the DMO?

2. order by case??????????

3. howto convert sql2000 DB to sql7.0

4. Problem with using Parameter QueryDefs

5. Converting SQL6.5 to SQL2000

6. Progress O/S

7. SQL2000 vs. SQL 6.5 and convert

8. Sql Server to Oracle direct interface

9. Error converting MSSQL7 database to SQL2000