none
Sqlserver 2008 : adding function to master database and use call it from other database

    Question

  • hi , is there any way to create a function in master database and call it form other databases..

    i can create a function in master but how to make it system function....



    student
    • Moved by Tom PhillipsModerator Wednesday, January 27, 2010 1:45 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Tuesday, January 26, 2010 6:00 PM

Answers

  • yes.....but stored procedures can be created for global use,not functions....kinda bug in sqlserver 2008
    student

    It is by design. You can submit a "wish" at Connect: https://connect.microsoft.com/SQLServer?wa=wsignin1.0

    -- master db functions cannot be referenced without "master" in reference
    USE MASTER 
    GO 
    
    CREATE FUNCTION fn_db_name( ) 
    RETURNS VARCHAR(50) 
      BEGIN 
        DECLARE  @db VARCHAR(50) 
         
        SET @db = db_name() 
         
        RETURN @db 
      END 
    
    GO 
    
    -- 
    USE MASTER 
    
    GO 
    
    EXEC sp_ms_marksystemobject 
      'fn_db_name' 
    
    GO 
    
    -- 
    USE AdventureWorks; 
    
    SELECT dbo.fn_db_name() 
    
    GO 
    
    /* 
    Msg 4121, Level 16, State 1, Line 3 
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_db_name", or the name is ambiguous. 
    */ 
    SELECT MASTER.dbo.fn_db_name() 
    GO 
    -- master
    
    -- 
    USE MASTER; 
    
    DROP FUNCTION fn_db_name 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Monday, February 1, 2010 7:10 AM
    Moderator

All replies

  • You can use this stored proc sp_ms_marksystemobject to get a system object created .

     

     

    USE MASTER

    GO

    EXEC sp_ms_marksystemobject 'name_of_your_function' 

    GO

     


    Thanks, Leks
    Tuesday, January 26, 2010 10:06 PM
  • that only works for stored procedure and tables not functions


    student
    Tuesday, January 26, 2010 10:09 PM
  • I am pretty sure that works for Functions as well.

    Please check and let me know, I was able to create a scalar valued function in master and mark them as system objects both in SQL 2005 and 2008.


    Thanks, Leks
    Tuesday, January 26, 2010 10:15 PM
  • -following code

    use master
    go
    create function fn_db_name
    ()
    returns varchar(50)
    begin
    declare
    @db varchar(50)
    set @db= db_name()
    return @db
    end 
    go

    --
    USE MASTER
    GO
    EXEC sp_ms_marksystemobject 'fn_db_name' 
    GO

    --

    -- when i use that function in any other database it gives me following error...

    select fn_db_name()


    Msg 195, Level 15, State 10, Line 1
    'fn_db_name' is not a recognized function name.



    student
    Tuesday, January 26, 2010 10:16 PM
  • If you need some objects to be created for global use inside SQL server (without needing to use ‘use dbname’) , then i would think the object has to be created under sys schema which is not possible.


    Thanks, Leks
    Tuesday, January 26, 2010 11:16 PM
  • yes.....but stored procedures can be created for global use,not functions....kinda bug in sqlserver 2008
    student
    Tuesday, January 26, 2010 11:43 PM
  • yes.....but stored procedures can be created for global use,not functions....kinda bug in sqlserver 2008
    student

    It is by design. You can submit a "wish" at Connect: https://connect.microsoft.com/SQLServer?wa=wsignin1.0

    -- master db functions cannot be referenced without "master" in reference
    USE MASTER 
    GO 
    
    CREATE FUNCTION fn_db_name( ) 
    RETURNS VARCHAR(50) 
      BEGIN 
        DECLARE  @db VARCHAR(50) 
         
        SET @db = db_name() 
         
        RETURN @db 
      END 
    
    GO 
    
    -- 
    USE MASTER 
    
    GO 
    
    EXEC sp_ms_marksystemobject 
      'fn_db_name' 
    
    GO 
    
    -- 
    USE AdventureWorks; 
    
    SELECT dbo.fn_db_name() 
    
    GO 
    
    /* 
    Msg 4121, Level 16, State 1, Line 3 
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_db_name", or the name is ambiguous. 
    */ 
    SELECT MASTER.dbo.fn_db_name() 
    GO 
    -- master
    
    -- 
    USE MASTER; 
    
    DROP FUNCTION fn_db_name 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Monday, February 1, 2010 7:10 AM
    Moderator
  • The function LEKSS mentioned above:

    EXEC sp_ms_marksystemobject 'name_of_your_function' 

    works, but your function will probably be owned by the 'dbo' schema, so you need to use the form dbo.myFunction() to call it from other databases.
    Tuesday, February 9, 2010 12:56 PM
  • The function LEKSS mentioned above:

    EXEC sp_ms_marksystemobject 'name_of_your_function'  

    works, but your function will probably be owned by the 'dbo' schema, so you need to use the form dbo.myFunction() to call it from other databases.

    Nope, you have to use "master.dbo.name_of_your_function", or you get:

    Msg 342, Level 16, State 1, Line 3
    Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.name_of_your_function" could not be found.


    Still trying to solve this.

     

    Saturday, February 12, 2011 5:00 AM
  • Nope, you have to use "master.dbo.name_of_your_function", or you get:

    Msg 342, Level 16, State 1, Line 3
    Column "dbo" is not allowed in this context, and the user-defined function or aggregate "dbo.name_of_your_function" could not be found.


    Still trying to solve this.


    There is no supported way to do this. It may work out if you prefix the function with sp_, but I recommend against it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, February 12, 2011 12:02 PM