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!Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"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!
>|||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!|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
--
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "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!
> >
> >
>|||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!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> 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!
> >
> >
> >
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge! :)
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge! :)
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > As Vyas pointed out, the issue is that you have to create the function
in
> > tempdb. I think he is right in suggesting that you can't use UDFs that
> > reside in a different database in the definition of a table.
> >
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > How can I create a function in tempdb while inside a stored procedure? I
> > don't think I can do that.
> >
> > Lets assume I can use sp_executesql to create the function inside a SP.
> What
> > will be a lifespan of it? Will it get dropped once the session is
closed?
> > will it be accessible from other sessions? Will there be a name conflict
> > when two sessions will try and execute the same SP?
> >
> > The more I get into it, the more questions I get. Hopefully I'll come
out
> > with good knowledge! :)
> >
> > Steve
> >
> > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > As Vyas pointed out, the issue is that you have to create the function
> in
> > > tempdb. I think he is right in suggesting that you can't use UDFs that
> > > reside in a different database in the definition of a table.
> > >
> >
> >
>|||You can either add the function to the model database (on the basis of which
all databases are created) or you can create a start up stored procedure to
create the function in tempdb whenever SQL Server is started. The function
will not just disappear from tempdb once you have created it, only when SQL
Server is restarted, because tempdb gets recreated then.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:ufdhcNTXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Won't that function get dropped/cleaned up and will require constant
> checking for its existance?
> Steve
> "Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
> news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> > Create a permanent function in tempdb, like
> >
> > USE tempdb
> > GO
> >
> >
> > CREATE FUNCTION dbo.fnTest
> > (@.x int, @.y int)
> > RETURNS int
> > AS
> > BEGIN
> > RETURN (@.x+@.y)
> > END
> > GO
> >
> > Now using another database u can call this function during temp table
> > creation
> >
> > eg.
> >
> >
> > USE Pubs
> >
> >
> > CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
> >
> >
> > INSERT INTO #temp VALUES(1,2)
> > INSERT INTO #temp VALUES(3,2)
> >
> > SELECT * FROM #temp
> >
> >
> >
> > --
> > Roji. P. Thomas
> > SQL Server Programmer
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > > How can I create a function in tempdb while inside a stored procedure?
I
> > > don't think I can do that.
> > >
> > > Lets assume I can use sp_executesql to create the function inside a
SP.
> > What
> > > will be a lifespan of it? Will it get dropped once the session is
> closed?
> > > will it be accessible from other sessions? Will there be a name
conflict
> > > when two sessions will try and execute the same SP?
> > >
> > > The more I get into it, the more questions I get. Hopefully I'll come
> out
> > > with good knowledge! :)
> > >
> > > Steve
> > >
> > > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> > wrote
> > > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > As Vyas pointed out, the issue is that you have to create the
function
> > in
> > > > tempdb. I think he is right in suggesting that you can't use UDFs
that
> > > > reside in a different database in the definition of a table.
> > > >
> > >
> > >
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment