We are using SA10 EBF 3805(Linux) and 3807(Windows) for our product.
Using follow stored procedure(SP) -
create procedure sp_adjust_required_table
( @dev_id integer
,@data_type tinyint
,@time_type tinyint
,@req_start_time datetime
,@req_end_time datetime
)
as
begin

select table_name,
(if sp_rt.start_time = sp_rt.end_time then
udf_time_adjust(sp_rt.start_time, 1, @time_type, 0)
else
udf_time_adjust(sp_rt.start_time, 1, @time_type, 1)
endif) as start_time,
(if sp_rt.start_time = sp_rt.end_time then
udf_time_adjust(sp_rt.end_time, 2, @time_type, 1)
else
udf_time_adjust(sp_rt.end_time, 2, @time_type, 0)
endif) as end_time,
use_time,
@time_type
from sp_required_table( @dev_id,
@data_type,
@time_type,
@req_start_time,
@req_end_time ) as sp_rt
where start_time < end_time

end
In some installations running follow SQL statement -
select max(end_time) from sp_adjust_required_table( 4,0,0,'2009-08-27
15:16:31', '2009-08-27 15:21:30')
SQL Error - 'Derived table 'sp_adjust_required_table ' has no name for
column 2 SQLCODE=-163' appears.
After reloading this SP from source or simple in Sybase Central add
space and save this SP. This SQL statement begin to work properly.
What is the problem , how this can be explaned!?
Thanks in advance , Hanan Brener

It sounds like this has something to do with
an undefined or broken result set specification
for the procedure. The historical view
SYSPROCPARM records with parm_type=1
for result set columns. It is probable, the
next time you hit this there is a mismatch
there.

You actions are simply causing a new set
of corrected values to get recorded.

Knowing how that might come about is a little
hard to say. If the database was recently upgrade
from an older version, it could have it's source
in a bug with an older version of the software.

Since you do not explicitly define a result set
in that procedure, that omission requires the
server to determine this information on first
usage.

Since that column relies heavily upon a
user defined function, it may also be possible
that function's definition was unavailable at
a critical time.

HTH

"Hanan Brener" <hananbrener (AT) gmail (DOT) com> wrote

Thanks Nick for your answer.

Really after product install in table SYSPROCPARM we get only one
output parameter with name - 'expression', but after reloading SP we
get 5(five) output parameters and SP begin to work properly.
We have change order in install SP and UDF first create UDF and second
SP (before order was versus). In Linux install that solve the problem,
in Windows install not. Only after running SP install additonal than
it solve the problem. Can this point on some differences between Linux
and Windows db server implementation or another problem?
Another question - if in SYSPROCPARM table parm_name is 'expression'
it points on some problem in SP!?

Thanks in advance,Hanan Brener

There should be no platform differences concerning this
behaviour. Especially given the two builds are so close.
[I checked all 4 bug fixes in that range and none of them
would seem to apply to your case]

It is more likely something different in the way these
objects are created. Does you installer run a different
SQL tool to play your scripts into the server? DBISQL[g]
vs DBISQLC maybe? [switching those around to operate
on the other platform should help define the failure pattern
better]

I would be interested in seeing the two databases created
on these two platforms [before the first execution of the
procedure]. An unload of just the schemas and the script
used to create them should nail this down; along with
knowing which utilities were used.

"Hanan Brener" <hananbrener (AT) gmail (DOT) com> wrote

Thanks Nick for your answer.

For database instalation we are used Script-file for Linux and C-exe
for Windows; For loading SP and UDF used the same mechanism - load
file by file from dedicated directory using DBISQL :
Examples -
Linux - ${SQLANY10}/bin32/dbisql -nogui -onerror exit -c "links=tcpip
(host=localhost;port=${port};dobroadcast=none;veri fy=no)\
;eng=a;uid=nms;pwd=allot" read /opt/allot/db_inst_conf/
collect_procs/sp_adjust_required_table.sql

Windows -
OS_Functions::runProgram
Program name - D:\Allot\Sybase\Sql Anywhere
10\win32\dbisql.exe
Program
arguments:

dbisql
-
nogui
-
c
eng=server10-xp_allot_stc; uid=nms; pwd=allot ; CommLinks=tcpip
(PORT=50001)
-
onerror

exit
read 'D:\Allot\db_inst_conf\stc\procs
\sp_adjust_required_table.sql'

You ask about unload schema from both platform, I have prepared them
using follows command -
${SQLANY10}/bin32/dbunload -c "DBF=/opt/sybase/data/db/stc/
allot_stc.db;UID=nms;PWD=allot" -n -r /opt/admin/reload.sql
How can I send to you these files?


Thanks in advance,Hanan Brener

I just sent you an email you can respond to; with attachements . . .

"Hanan Brener" <hananbrener (AT) gmail (DOT) com> wrote

You two scripts are significantly different.

Are they supposed to be identical?
If so they are not.

Ignoring the newline differences all comment lines
and all comments to preserve format and just
focussing on the prefix lines beginnint with
CREATE <something> I find that while all
your create functions are grouped together
and all your create procedures are grouped
together, almost none of them are created
in the same order (within each group).

That is T-H-E recipe for the kind of problem
you are reporting.

Since 'the fix' for the problem was always to
reorder the create <object> statements to avoid
this problem ... and since you seem to have a
good template for this in the Windows script,
I would just copy the create functions and create
procedure statements from the good one and
replace the same section in the problematic
script with that.

That would also help to normalize your schemas
for your two platforms.

And since we are on the 'managed' soap box,
you will probably want to kick the tires on
our PowerDesigner product [the 'Physical
Data Modeller' teaser being available with
most of our GA installs].

HTH

"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4aa18819$1 (AT) forums-1-dub (DOT) ..

The short causative difference is that in the
windows script
sp_adjust_required_table( )
is defined before
sp_calc_required_tables( )
which calls the first one and allows
the result set to be known.

In contrast the linux script has those the other
way around and when sp_calc_required_tables
is seen for the first time there is no definition
for sp_adjust_required_table and thus
no information on what the result set will
look like.

BUT the cause is the general order differences
in those two scripts and that may impact other
procedures and functions in more subtle ways
so I would consolidated both and manage
them as one set of definitions with only the
platform differences addressed when the
script for the 'other' platform gets 'cut'.

"Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4aa18819$1 (AT) forums-1-dub (DOT) ..

Thanks Nick for your answers.

Consolidate all SP and UDF in one common script seems problematic for
future maintenance this script. PowerDesigner we are using for
database schema (tables, indexes and dependency) maintenance and
script generation. For loading SP ,UDF and Trigger used the same
mechanism - load file by file from dedicated directories using DBISQL.
I didn't find or understand dependence logic between SP and UDF used
one inside another. The working combination in this case is first
loading udf_time_adjust, second sp_calc_required_tables and third
sp_adjust_required_table.
The current solution is on database installation process - run SP and
UDF loading process twice.

Thanks in advance,Hanan Brener