June 8, 2007 at 4:09 pm
i've got this function that i want to reference a table i have just created...it refuses to, saying the object doesn't exist (while outside the function i can insert, query, delete, etc.) any ideas?
if object_id('testFunction') is not null drop function dbo.testFunction
if object_id('testTable') is not null drop table testTable
go
create function dbo.testFunction() returns int as begin
declare @i int
select @i=count(*) from testTable
return @i
end
go
create table testTable (id_col int IDENTITY (1,1),a int)
INSERT INTO testTable(a) VALUES(42)
--this works:
select count(*) from testTable
--this doesn't...function errors saying testTable does not exist
select dbo.testFunction()
thanks.
June 8, 2007 at 4:54 pm
Hmmmmm. I did a copy and paste of the code you gave and it worked fine for me. Are you sure that the testTable table was created with the correct owner?
June 8, 2007 at 9:44 pm
I would check the owner of TestTable, and make sure it is dbo.
June 11, 2007 at 11:11 am
I typed the function back up this morning, and it now works perfectly. No idea what i did differently, probably a stupid mistake somewhere. The user i'm using has full ownership over the database, and the function had no problems referencing other tables, so i must've screwed something up in table creation.
i did try writing the thing originally at about 6:00 friday evening. could have something to do with it...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply