locked
SQL 2005 System UDF function and Procedures RRS feed

  • Question

  • In SQL 2000 for Procedures
    this was done by writing procs in Master db prefixing the name with sp_
    and using exec sp_MS_marksystemobject procname

    In SQL 2000 for functions
    we wrote function in Master db
    like so system_function_schema

    Create Function system_function_schema.fn_v_filler
     (

    How would one go about it in 2005

    Regards.

    Friday, July 15, 2005 10:54 PM

Answers

  • Hi - sp_MS_marksystemobject and  system_function_schema were undocumented and unsupported APIs.  Code based on them will not work in SQL 2005.

    We are aware that customers want to create TSQL "libraries" and we have an internal DCR (design change request) to address this in the next product release.

    The best advise going foward is to avoid undocumented APIs, regardless of what you read in books or magazines.   If the API you wish to use has a reference page in the SQL Server BOL it is OK.

    Regards,
    Clifford Dibble
    Friday, July 22, 2005 3:17 AM

All replies

  • Hi - sp_MS_marksystemobject and  system_function_schema were undocumented and unsupported APIs.  Code based on them will not work in SQL 2005.

    We are aware that customers want to create TSQL "libraries" and we have an internal DCR (design change request) to address this in the next product release.

    The best advise going foward is to avoid undocumented APIs, regardless of what you read in books or magazines.   If the API you wish to use has a reference page in the SQL Server BOL it is OK.

    Regards,
    Clifford Dibble
    Friday, July 22, 2005 3:17 AM
  • At this point in time, it appears that sp_MS_marksystemobject still works in SQl2005 SP1(I've just tested it, and it runs, and does what it needs to, allowing an SP created in master, to be used as a system stored procedure), but naturally, as pointed out by Clifford, we should not base code on these, since they may be changed/removed at any point in time (read SP, even hotfix?).
    Tuesday, August 8, 2006 1:09 PM
  • Would have nice if was included in the current product! :(
    Tuesday, September 5, 2006 1:49 PM

  • I am having the same requirement. I need to write a function and want to make it available for all the databases.

    I know that we are not allowed to create any database objects in sql2005. Then how to solve this problem ?

     

    What is equivalent of system_function_schema or sp_MS_marksystemobject in sql 2005

     

    Regards,

    Subbu

    Friday, August 17, 2007 1:37 AM