Urgent: Strange error when using a function to return table

Urgent: Strange error when using a function to return table

Post by MagicGur » Wed, 12 Mar 2003 09:21:13



I am using a function listed below, several days ago it shows a error msg

function and recreate it, another error msg said there is a syntax error
near FUNCTION!!!
But in another backup database, the same function works just fine.
Can anybody give your 2 cents on this?

Thanks a lot!

------------------
CREATE FUNCTION TF_GetHotProduct ()

AS
begin

Declare cur_BC CURSOR for select bc_id from bigCategory

open cur_BC


begin
  -- for each BigCategory, select one latest new product
  begin

products P
      join SmallCategory SC on P.Smallclassid=SC.SC_id join MiddleCategory
MCon SC.mc_id=MC.mc_id
      join c_tek C on P.product_id = C.Product_id

C.Approved = 'Y'
      order by P.CreateAt desc
  end

end
deallocate cur_BC
return
end

 
 
 

Urgent: Strange error when using a function to return table

Post by Narayana Vyas Kondredd » Wed, 12 Mar 2003 09:28:05


Check your database compatibility level using sp_dbcmptlevel and make sure
your database compatiblity is 80. See Books Online for more info.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


I am using a function listed below, several days ago it shows a error msg

function and recreate it, another error msg said there is a syntax error
near FUNCTION!!!
But in another backup database, the same function works just fine.
Can anybody give your 2 cents on this?

Thanks a lot!

------------------
CREATE FUNCTION TF_GetHotProduct ()

AS
begin

Declare cur_BC CURSOR for select bc_id from bigCategory

open cur_BC


begin
  -- for each BigCategory, select one latest new product
  begin

products P
      join SmallCategory SC on P.Smallclassid=SC.SC_id join MiddleCategory
MCon SC.mc_id=MC.mc_id
      join c_tek C on P.product_id = C.Product_id

C.Approved = 'Y'
      order by P.CreateAt desc
  end

end
deallocate cur_BC
return
end

 
 
 

Urgent: Strange error when using a function to return table

Post by oj » Wed, 12 Mar 2003 09:36:58


"Create Function" is allowed on SQL2K with compatibility level set to 80. If
you run

exec sp_dbcmptlevel 'your_database_name', 80

you should be able to execute the script successfully.

--
-oj
http://www.rac4sql.net


> I am using a function listed below, several days ago it shows a error msg

> function and recreate it, another error msg said there is a syntax error
> near FUNCTION!!!
> But in another backup database, the same function works just fine.
> Can anybody give your 2 cents on this?

> Thanks a lot!

> ------------------
> CREATE FUNCTION TF_GetHotProduct ()

> AS
> begin

> Declare cur_BC CURSOR for select bc_id from bigCategory

> open cur_BC


> begin
>   -- for each BigCategory, select one latest new product
>   begin

from
> products P
>       join SmallCategory SC on P.Smallclassid=SC.SC_id join MiddleCategory
> MCon SC.mc_id=MC.mc_id
>       join c_tek C on P.product_id = C.Product_id

> C.Approved = 'Y'
>       order by P.CreateAt desc
>   end

> end
> deallocate cur_BC
> return
> end

 
 
 

Urgent: Strange error when using a function to return table

Post by MagicGur » Wed, 12 Mar 2003 11:30:31


It works! Thanks!
 
 
 

1. Strange error using a user defined function to return a string

We have created an activex dll containing a function that returns a string.
When using this function in an MDX calculation we get the error "infitie
recursion detected...."
In the MDX if the function is replaced with a string in quotations e.g.
"sales" the MDX works correctly.
This has been tried in a cube specific calcualted measure and also in an
adhoc query using the MDX sample application
The platform details are as follows:
AS2K
Delphi is used to construct the dll
We return a wide string but have also tried it with all the other possible
string types that delphi will allow.
Any pointers  would be much appreceiated.

Regards,
Andreas Cambitsis

2. Bi-directional Replication over the Internet

3. Urgent: strange error with #temporarily table

4. Is postgresql the right choice for this setup?

5. Using a User defined function returning a table in a subquery

6. TDBEdit.DataSource?

7. Calling table functions using named notation returns ORA-00907 (Oracle 9i)

8. hash indexing taking a lot of time.

9. strange table access error using PHP

10. Strange ODBC errors using DAO to access linked tables in Access .MDB

11. Select statement return error when creating a table using ADOX

12. Join to view that uses COALESCE returns strange results with NULLS