Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Lightbulb Unanswered: Computed columns in temp tables

    I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
    IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
    DROP FUNCTION dbo.fn_test
    GO
    CREATE FUNCTION dbo.fn_test( @x int, @y int)
    RETURNS INT AS
    BEGIN
    DECLARE @z INT
    SET @z = @x + @y
    RETURN @z
    END
    GO

    CREATE TABLE #X
    (
    x INT,
    y INT,
    z AS (dbo.fn_test(x,y))
    )
    I receive the following error:

    Server: Msg 208, Level 16, State 1, Line 2
    Invalid object name 'dbo.fn_test'.

    I do not get this error if I use a regular table.
    HELP!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The function you are creating resides in your user database, where as the temporay table is actually created in tempdb database, where your function does not exist.

    To test this out I created the function in tempdb and then went into my user database (ex. testdb) and created the temporay table using your script and it worked fine.

    The problem with creating a function in tempdb is that the next time SQL Server stops and then restarts the function will be gone. The way around this is to also create the function in model database, since each time SQL Server starts up it takes a copy of model to create tempdb.
    MCDBA

  3. #3
    Join Date
    Jun 2004
    Posts
    3

    Question can you post your script please

    Can you post your script please. I have tried creating the function in the master database and then use it in the temp table creation like this:

    z AS (master.dbo.fn_test(x,y))

    and still got the error.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

    Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:
    Code:
    CREATE TABLE #X 
    (
    x INT,
    y INT,
    z AS (foo.dbo.fn_test(x,y))
    )
    -PatP

  5. #5
    Join Date
    Jun 2004
    Posts
    3

    Unhappy

    I did just that and still got the same error. Previous poster mentioned that he was able to do it but haven't posted his version of the script.

    Steve


    Quote Originally Posted by Pat Phelan
    Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

    Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:
    Code:
    CREATE TABLE #X 
    (
    x INT,
    y INT,
    z AS (foo.dbo.fn_test(x,y))
    )
    -PatP

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I have a database called Test, where I'm creating the temporay table.


    set nocount on
    go
    use tempdb
    go
    IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
    DROP FUNCTION dbo.fn_test
    GO
    CREATE FUNCTION dbo.fn_test( @x int, @y int)
    RETURNS INT AS
    BEGIN
    DECLARE @z INT
    SET @z = @x + @y
    RETURN @z
    END
    GO

    use test
    go
    CREATE TABLE #X
    (
    x INT,
    y INT,
    z AS (dbo.fn_test(x,y))
    )
    go
    insert #x values (5,6)
    go
    select * from #x
    go
    drop table #x
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •