Skip to Content
author's profile photo Former Member
Former Member

Table user defined functions in 16 SP02

I have created a function and would like to join to another table. Following the approach shown in the reference manual I am getting a stack trace if I select more than one row from the other table.

Here is a simplified example using system tables so it should be easy to try out anywhere:

if object_id('GetColumns') is not NULL drop function dbo.GetColumns

go

CREATE function dbo.GetColumns(@ObjId int)

returns

@cols table(ColumnName varchar(999))

as

begin

insert @cols(ColumnName)

select name from syscolumns where id = @ObjId

end

go

select * from dbo.GetColumns(1)

select * from dbo.GetColumns(NULL)

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 2

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

The first four selects work fine, but the last one fails spectacularly with a stack trace.

Is this a known problem or am I using it incorrectly?

Thanks

Mark

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Posted on Aug 10, 2016 at 03:02 PM

    The query returns results and doesn't stack for me on 16.0 SP02 PL04 Linux.

    Could you post your exact @@version and a copy of the full stacktrace error?

    -bret

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Avinash Kothare

      Hi Avinesh,

      Thanks for your help,

      I can confirm my Aqua connection settings are correct.

      That is an interesting error message you are getting and is one I can see sometimes with another function I wrote. I wrote this simple one to try to demonstrate that error message, but it just stack traces instead for me!

      Mark

  • Posted on Aug 10, 2016 at 08:45 PM

    Do you have access to any other ASE 16 dataservers where this set of queries either a) works or b) also stack traces?

    Has this set of queries worked in the past (eg, query worked prior to upgrading your current dataserver to ASE 16 SP02 PL04)?

    What happens if you run your test via the isql command line tool? [Want to rule out any sort of funky behavior by Aqua Data Studio.]

    --------------------

    FWIW, I'm able to successfully run your tests, via isql, against both user-defined and temporary databases in ASE 16 SP02 PL03 ... on both solaris/x86 and linux.

    NOTE: Don't have access to a PL04 version of ASE at the moment.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Bret

      In my case literal autoparam was off

      1> sp_helpconfig "enable literal autoparam"

      2> go

      The configuration parameter 'enable literal autoparam' enables/disables the auto-parameterization of literals in ad-hoc queries. The default value is 1 (enabled)

      Minimum Value Maximum Value Default Value Current Value Memory Used Unit Type

      --------------- --------------- --------------- --------------- ------------- ------ -------

      0 1 0 0 0 switch dynamic

      (return status = 0)

      1>

      Query outcome seems to be OS specific.

      As noted above for the last query I get a different error on AIX !


      Msg 201, Level 16, State 2:

      Server 'PHI5_SB_16', Procedure 'GetColumns':

      Procedure GetColumns expects parameter @ObjId, which was not supplied.

      I have copy-pasted the code Mark provided to run the queries.

      My ASE @@version is :

      Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26120 SMP/P/RS6000/AIX 7.1/ase160sp02pl04x/2587/64-bit/FBO/Sat Jun 11 17:09:12 2016

      Avinash

  • author's profile photo Former Member
    Former Member
    Posted on Aug 15, 2016 at 08:32 AM

    I have tried the test in some more databases and can add some more information, although it only seems to confuse matters even further!

    I have found some user databases on the same server where the test did not cause a stack trace but returned rows as expected. The databases have the same db_options set.

    I then tried running the query from the bad database but using the sysobjects table from the good database:

    use BadDb

    go

    select o.id, o.name, c.ColumnName from GoodDb..sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2


    and that works fine.


    The other way around:


    use GoodDb

    go

    select o.id, o.name, c.ColumnName from BadDb..sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2


    stack traces.


    If I copy the sysobjects table into another table in the bad database and then use that in the query:


    use BadDb

    go

    select * into MySysobjects from sysobjects

    go

    select o.id, o.name, c.ColumnName from MySysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2


    works fine.


    SO it looks like there may be something wrong/different with the sysobjects tables on the bad databases. DBCC checktable returns no errors though.


    Mark

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 15, 2016 at 09:07 AM

    Rewriting the query as a left outer join seems to fix the problem - I don't know why.

    For example this works in all of the databases I've tried:

    select o.id, o.name, c.ColumnName

    from sysobjects o

    left join dbo.GetColumns(o.id) c on 1 = 1 /* deliberate cartesian join */

    where o.id = 1 or o.id = 2

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.