Re: Computed columns in temp tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steven Yampolsky (syampolsky_at_eagleinvsys.com)
Date: 06/25/04 

Date: Fri, 25 Jun 2004 08:38:17 -0400

I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:

use master
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
 RETURN @x + @y
END
GO

CREATE TABLE #X
(
 x INT,
 y INT,
 z AS (master.dbo.fn_test(x,y))
)

"Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
news:O52L6fqWEHA.2544@TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Steven Yampolsky" <syampolsky@eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@TK2MSFTNGP10.phx.gbl...
> 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!
>
>
>

Relevant Pages

  • Need help with multi-dimensional arrays and functions
    ... I'm attempting to write a program to read in database files. ... parameters telling me the dimensions of the array. ... int i,j,nrecords,nfields,nchars; ... printf; ...
    (comp.lang.c)
  • Re: Computed columns in temp tables
    ... Looks like the UDF is being looked up in the tempdb database. ... your table creation will succeed. ... CREATE FUNCTION dbo.fn_test(@x int, @y int) ...
    (microsoft.public.sqlserver.datamining)
  • Re: Computed columns in temp tables
    ... Looks like the UDF is being looked up in the tempdb database. ... your table creation will succeed. ... CREATE FUNCTION dbo.fn_test(@x int, @y int) ...
    (microsoft.public.sqlserver.server)
  • Re: Computed columns in temp tables
    ... Looks like the UDF is being looked up in the tempdb database. ... your table creation will succeed. ... CREATE FUNCTION dbo.fn_test(@x int, @y int) ...
    (microsoft.public.sqlserver.programming)
  • Re: Inheritance & static members
    ... > int id; ... Perhaps your design is more obvious to those with more database experience ... Ensuring that each child of Name_Lookup has ... of the static member: ...
    (comp.lang.cpp)