Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sa_compile_views failure when converting ASA 6 DB to ASA 9

8 posts in General Discussion Last posting was on 2004-08-30 23:32:59.0Z
GeoffAtDatagaard Posted on 2004-08-30 05:49:56.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.7.2) Gecko/20040803
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: sa_compile_views failure when converting ASA 6 DB to ASA 9
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: adsl-088.cust203-87-21.sa.chariot.net.au
Message-ID: <4132c182$1@forums-2-dub>
X-Original-Trace: 29 Aug 2004 22:56:18 -0700, adsl-088.cust203-87-21.sa.chariot.net.au
Lines: 27
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Aug 2004 22:39:24 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Aug 2004 22:49:56 -0700
X-Trace: forums-1-dub 1093844996 10.22.108.75 (29 Aug 2004 22:49:56 -0700)
X-Original-Trace: 29 Aug 2004 22:49:56 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3453
Article PK: 7358

I am attempting to prepare a customer's ASA 6 database for
conversion/upgrade to ASA 9.

I am using ASA 9 (Developer Latest Edition) Sybase Central to unload and
reload the ASA 6 db into a new ASA 9 db.

All goes well until the script performs CALL sp_compile_views(0).
At this point it fails with mysterious errors.

It fails with a 'more than one row' in resultset error, I understand the
normal meaning of this to be that a sub-query is returning multiple rows.
However, when I subsequently test the view by running the query in ISQL
it performs as expected. If I then edit and save the view (no changes)
sa_recompile_views gets past this view and makes the same complaint
about another view.

Now the obvious easy answer is to open-edit-save all views, but we have
several hundred and I am trying to make upgrade day as painless as
possible. ( I got bored with this approach after an hour or so )

Can anyone tell me what is happening and/or how I can avoid this.

TIA
Geoff Sutcliffe
Datagaard Pty Ltd
South Australia


Nick Elson Posted on 2004-08-30 06:16:09.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <4132c182$1@forums-2-dub>
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Original-NNTP-Posting-Host: surfec007.sybase.com
Message-ID: <4132c7a8@forums-2-dub>
X-Original-Trace: 29 Aug 2004 23:22:32 -0700, surfec007.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Aug 2004 23:05:37 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Aug 2004 23:16:09 -0700
X-Trace: forums-1-dub 1093846569 10.22.108.75 (29 Aug 2004 23:16:09 -0700)
X-Original-Trace: 29 Aug 2004 23:16:09 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3455
Article PK: 7360

ASA 9 or ASA 9.0.1 ? AND build?

And does your version contain this bug fix?
[if not the error may just turn out to be a non-issue
and your recreation efforts may not be accomplishing
anything other than validating the situation]

Versions affected: 9.0.0 and above
Versions fixed: 9.0.1(1733)
Modules affected: server, dbtoolX.dll
Description: If a view was created which referenced a ***stored
procedure*** in the FROM
clause, rebuilding the database would fail. Also, if the procedure name was
not qualified
with the owner of the procedure, users who were not members of the
procedure owner's
group would be unable to reference the view. Now, the catalog definitions of
views which
reference stored procedures in the FROM clause will have those references
qualified with
the procedure owner. An additional call to dbo.sa_recompile_views() in the
reload script
will permit views which reference procedures to be recompiled after the
procedures have
been defined.

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:4132c182$1@forums-2-dub...
> I am attempting to prepare a customer's ASA 6 database for
> conversion/upgrade to ASA 9.
>
> I am using ASA 9 (Developer Latest Edition) Sybase Central to unload and
> reload the ASA 6 db into a new ASA 9 db.
>
> All goes well until the script performs CALL sp_compile_views(0).
> At this point it fails with mysterious errors.
>
> It fails with a 'more than one row' in resultset error, I understand the
> normal meaning of this to be that a sub-query is returning multiple rows.
> However, when I subsequently test the view by running the query in ISQL
> it performs as expected. If I then edit and save the view (no changes)
> sa_recompile_views gets past this view and makes the same complaint
> about another view.
>
> Now the obvious easy answer is to open-edit-save all views, but we have
> several hundred and I am trying to make upgrade day as painless as
> possible. ( I got bored with this approach after an hour or so )
>
> Can anyone tell me what is happening and/or how I can avoid this.
>
> TIA
> Geoff Sutcliffe
> Datagaard Pty Ltd
> South Australia
>


GeoffAtDatagaard Posted on 2004-08-30 06:22:05.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.7.2) Gecko/20040803
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub>
In-Reply-To: <4132c7a8@forums-2-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: adsl-088.cust203-87-21.sa.chariot.net.au
Message-ID: <4132c90d@forums-2-dub>
X-Original-Trace: 29 Aug 2004 23:28:29 -0700, adsl-088.cust203-87-21.sa.chariot.net.au
Lines: 65
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Aug 2004 23:11:34 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Aug 2004 23:22:05 -0700
X-Trace: forums-1-dub 1093846925 10.22.108.75 (29 Aug 2004 23:22:05 -0700)
X-Original-Trace: 29 Aug 2004 23:22:05 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3456
Article PK: 7361

ASA 9.0.1.1862

Nick Elson wrote:

> ASA 9 or ASA 9.0.1 ? AND build?
>
> And does your version contain this bug fix?
> [if not the error may just turn out to be a non-issue
> and your recreation efforts may not be accomplishing
> anything other than validating the situation]
>
> Versions affected: 9.0.0 and above
> Versions fixed: 9.0.1(1733)
> Modules affected: server, dbtoolX.dll
> Description: If a view was created which referenced a ***stored
> procedure*** in the FROM
> clause, rebuilding the database would fail. Also, if the procedure name was
> not qualified
> with the owner of the procedure, users who were not members of the
> procedure owner's
> group would be unable to reference the view. Now, the catalog definitions of
> views which
> reference stored procedures in the FROM clause will have those references
> qualified with
> the procedure owner. An additional call to dbo.sa_recompile_views() in the
> reload script
> will permit views which reference procedures to be recompiled after the
> procedures have
> been defined.
>
> "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> news:4132c182$1@forums-2-dub...
>
>>I am attempting to prepare a customer's ASA 6 database for
>>conversion/upgrade to ASA 9.
>>
>>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload and
>>reload the ASA 6 db into a new ASA 9 db.
>>
>>All goes well until the script performs CALL sp_compile_views(0).
>>At this point it fails with mysterious errors.
>>
>>It fails with a 'more than one row' in resultset error, I understand the
>>normal meaning of this to be that a sub-query is returning multiple rows.
>>However, when I subsequently test the view by running the query in ISQL
>>it performs as expected. If I then edit and save the view (no changes)
>>sa_recompile_views gets past this view and makes the same complaint
>>about another view.
>>
>>Now the obvious easy answer is to open-edit-save all views, but we have
>>several hundred and I am trying to make upgrade day as painless as
>>possible. ( I got bored with this approach after an hour or so )
>>
>>Can anyone tell me what is happening and/or how I can avoid this.
>>
>>TIA
>>Geoff Sutcliffe
>>Datagaard Pty Ltd
>>South Australia
>>
>
>
>


Nick Elson Posted on 2004-08-30 06:49:23.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub> <4132c90d@forums-2-dub>
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Original-NNTP-Posting-Host: cpe0050bf17594c-cm014370028576.cpe.net.cable.rogers.com
Message-ID: <4132cf6e$1@forums-2-dub>
X-Original-Trace: 29 Aug 2004 23:55:42 -0700, cpe0050bf17594c-cm014370028576.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Aug 2004 23:38:47 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Aug 2004 23:49:23 -0700
X-Trace: forums-1-dub 1093848563 10.22.108.75 (29 Aug 2004 23:49:23 -0700)
X-Original-Trace: 29 Aug 2004 23:49:23 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3457
Article PK: 7359

Then you might want to contact support directly so they can look at
your situation. You may have hit something new.

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:4132c90d@forums-2-dub...
>
> ASA 9.0.1.1862
>
>
> Nick Elson wrote:
>
> > ASA 9 or ASA 9.0.1 ? AND build?
> >
> > And does your version contain this bug fix?
> > [if not the error may just turn out to be a non-issue
> > and your recreation efforts may not be accomplishing
> > anything other than validating the situation]
> >
> > Versions affected: 9.0.0 and above
> > Versions fixed: 9.0.1(1733)
> > Modules affected: server, dbtoolX.dll
> > Description: If a view was created which referenced a ***stored
> > procedure*** in the FROM
> > clause, rebuilding the database would fail. Also, if the procedure name
was
> > not qualified
> > with the owner of the procedure, users who were not members of the
> > procedure owner's
> > group would be unable to reference the view. Now, the catalog
definitions of
> > views which
> > reference stored procedures in the FROM clause will have those
references
> > qualified with
> > the procedure owner. An additional call to dbo.sa_recompile_views() in
the
> > reload script
> > will permit views which reference procedures to be recompiled after the
> > procedures have
> > been defined.
> >
> > "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> > news:4132c182$1@forums-2-dub...
> >
> >>I am attempting to prepare a customer's ASA 6 database for
> >>conversion/upgrade to ASA 9.
> >>
> >>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload and
> >>reload the ASA 6 db into a new ASA 9 db.
> >>
> >>All goes well until the script performs CALL sp_compile_views(0).
> >>At this point it fails with mysterious errors.
> >>
> >>It fails with a 'more than one row' in resultset error, I understand the
> >>normal meaning of this to be that a sub-query is returning multiple
rows.
> >>However, when I subsequently test the view by running the query in ISQL
> >>it performs as expected. If I then edit and save the view (no changes)
> >>sa_recompile_views gets past this view and makes the same complaint
> >>about another view.
> >>
> >>Now the obvious easy answer is to open-edit-save all views, but we have
> >>several hundred and I am trying to make upgrade day as painless as
> >>possible. ( I got bored with this approach after an hour or so )
> >>
> >>Can anyone tell me what is happening and/or how I can avoid this.
> >>
> >>TIA
> >>Geoff Sutcliffe
> >>Datagaard Pty Ltd
> >>South Australia
> >>
> >
> >
> >


GeoffAtDatagaard Posted on 2004-08-30 07:13:37.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.7.2) Gecko/20040803
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub> <4132c90d@forums-2-dub> <4132cf6e$1@forums-2-dub>
In-Reply-To: <4132cf6e$1@forums-2-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: adsl-088.cust203-87-21.sa.chariot.net.au
Message-ID: <4132d520@forums-2-dub>
X-Original-Trace: 30 Aug 2004 00:20:00 -0700, adsl-088.cust203-87-21.sa.chariot.net.au
Lines: 110
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 30 Aug 2004 00:03:07 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 30 Aug 2004 00:13:37 -0700
X-Trace: forums-1-dub 1093850017 10.22.108.75 (30 Aug 2004 00:13:37 -0700)
X-Original-Trace: 30 Aug 2004 00:13:37 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3458
Article PK: 7362

Thanks Nick,

As you suggested, I looked into contacting support directly, but found I
could not without paying for a support case.

I cant justify my company having to pay to report a bug.

I have found that I can identify the views which need to be manually
compiled (opened & saved) from the following query:

select table_name from sys.systable
where table_name not in (select tname from sys.syscolumns)

With a couple of views I had to drop and re-create them but for most I
only needed to open - add a space - save.

Unless I see a fix for this come thru in EBF's I will write a script to
complete these actions on upgrade day.

Nick Elson wrote:

> Then you might want to contact support directly so they can look at
> your situation. You may have hit something new.
>
> "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> news:4132c90d@forums-2-dub...
>
>>ASA 9.0.1.1862
>>
>>
>>Nick Elson wrote:
>>
>>
>>>ASA 9 or ASA 9.0.1 ? AND build?
>>>
>>>And does your version contain this bug fix?
>>>[if not the error may just turn out to be a non-issue
>>> and your recreation efforts may not be accomplishing
>>> anything other than validating the situation]
>>>
>>>Versions affected: 9.0.0 and above
>>>Versions fixed: 9.0.1(1733)
>>>Modules affected: server, dbtoolX.dll
>>>Description: If a view was created which referenced a ***stored
>>>procedure*** in the FROM
>>>clause, rebuilding the database would fail. Also, if the procedure name
>
> was
>
>>>not qualified
>>> with the owner of the procedure, users who were not members of the
>>>procedure owner's
>>>group would be unable to reference the view. Now, the catalog
>
> definitions of
>
>>>views which
>>>reference stored procedures in the FROM clause will have those
>
> references
>
>>>qualified with
>>>the procedure owner. An additional call to dbo.sa_recompile_views() in
>
> the
>
>>>reload script
>>>will permit views which reference procedures to be recompiled after the
>>>procedures have
>>>been defined.
>>>
>>>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
>>>news:4132c182$1@forums-2-dub...
>>>
>>>
>>>>I am attempting to prepare a customer's ASA 6 database for
>>>>conversion/upgrade to ASA 9.
>>>>
>>>>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload and
>>>>reload the ASA 6 db into a new ASA 9 db.
>>>>
>>>>All goes well until the script performs CALL sp_compile_views(0).
>>>>At this point it fails with mysterious errors.
>>>>
>>>>It fails with a 'more than one row' in resultset error, I understand the
>>>>normal meaning of this to be that a sub-query is returning multiple
>
> rows.
>
>>>>However, when I subsequently test the view by running the query in ISQL
>>>>it performs as expected. If I then edit and save the view (no changes)
>>>>sa_recompile_views gets past this view and makes the same complaint
>>>>about another view.
>>>>
>>>>Now the obvious easy answer is to open-edit-save all views, but we have
>>>>several hundred and I am trying to make upgrade day as painless as
>>>>possible. ( I got bored with this approach after an hour or so )
>>>>
>>>>Can anyone tell me what is happening and/or how I can avoid this.
>>>>
>>>>TIA
>>>>Geoff Sutcliffe
>>>>Datagaard Pty Ltd
>>>>South Australia
>>>>
>>>
>>>
>>>
>
>


Robert Waywell Posted on 2004-08-30 13:49:31.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub> <4132c90d@forums-2-dub> <4132cf6e$1@forums-2-dub> <4132d520@forums-2-dub>
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
Lines: 143
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <4133306b$1@forums-1-dub>
Date: 30 Aug 2004 06:49:31 -0700
X-Trace: forums-1-dub 1093873771 10.25.100.118 (30 Aug 2004 06:49:31 -0700)
X-Original-Trace: 30 Aug 2004 06:49:31 -0700, rwaywell-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3460
Article PK: 7363

Bug reports can be submitted at no cost through the link below my signature.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:4132d520@forums-2-dub...
> Thanks Nick,
>
> As you suggested, I looked into contacting support directly, but found I
> could not without paying for a support case.
>
> I cant justify my company having to pay to report a bug.
>
> I have found that I can identify the views which need to be manually
> compiled (opened & saved) from the following query:
>
> select table_name from sys.systable
> where table_name not in (select tname from sys.syscolumns)
>
> With a couple of views I had to drop and re-create them but for most I
> only needed to open - add a space - save.
>
> Unless I see a fix for this come thru in EBF's I will write a script to
> complete these actions on upgrade day.
>
> Nick Elson wrote:
>
> > Then you might want to contact support directly so they can look at
> > your situation. You may have hit something new.
> >
> > "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> > news:4132c90d@forums-2-dub...
> >
> >>ASA 9.0.1.1862
> >>
> >>
> >>Nick Elson wrote:
> >>
> >>
> >>>ASA 9 or ASA 9.0.1 ? AND build?
> >>>
> >>>And does your version contain this bug fix?
> >>>[if not the error may just turn out to be a non-issue
> >>> and your recreation efforts may not be accomplishing
> >>> anything other than validating the situation]
> >>>
> >>>Versions affected: 9.0.0 and above
> >>>Versions fixed: 9.0.1(1733)
> >>>Modules affected: server, dbtoolX.dll
> >>>Description: If a view was created which referenced a ***stored
> >>>procedure*** in the FROM
> >>>clause, rebuilding the database would fail. Also, if the procedure name
> >
> > was
> >
> >>>not qualified
> >>> with the owner of the procedure, users who were not members of the
> >>>procedure owner's
> >>>group would be unable to reference the view. Now, the catalog
> >
> > definitions of
> >
> >>>views which
> >>>reference stored procedures in the FROM clause will have those
> >
> > references
> >
> >>>qualified with
> >>>the procedure owner. An additional call to dbo.sa_recompile_views() in
> >
> > the
> >
> >>>reload script
> >>>will permit views which reference procedures to be recompiled after the
> >>>procedures have
> >>>been defined.
> >>>
> >>>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> >>>news:4132c182$1@forums-2-dub...
> >>>
> >>>
> >>>>I am attempting to prepare a customer's ASA 6 database for
> >>>>conversion/upgrade to ASA 9.
> >>>>
> >>>>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload
and
> >>>>reload the ASA 6 db into a new ASA 9 db.
> >>>>
> >>>>All goes well until the script performs CALL sp_compile_views(0).
> >>>>At this point it fails with mysterious errors.
> >>>>
> >>>>It fails with a 'more than one row' in resultset error, I understand
the
> >>>>normal meaning of this to be that a sub-query is returning multiple
> >
> > rows.
> >
> >>>>However, when I subsequently test the view by running the query in
ISQL
> >>>>it performs as expected. If I then edit and save the view (no changes)
> >>>>sa_recompile_views gets past this view and makes the same complaint
> >>>>about another view.
> >>>>
> >>>>Now the obvious easy answer is to open-edit-save all views, but we
have
> >>>>several hundred and I am trying to make upgrade day as painless as
> >>>>possible. ( I got bored with this approach after an hour or so )
> >>>>
> >>>>Can anyone tell me what is happening and/or how I can avoid this.
> >>>>
> >>>>TIA
> >>>>Geoff Sutcliffe
> >>>>Datagaard Pty Ltd
> >>>>South Australia
> >>>>
> >>>
> >>>
> >>>
> >
> >


Nick Elson Posted on 2004-08-30 22:21:53.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub> <4132c90d@forums-2-dub> <4132cf6e$1@forums-2-dub> <4132d520@forums-2-dub>
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
Lines: 124
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <4133a881@forums-1-dub>
Date: 30 Aug 2004 15:21:53 -0700
X-Trace: forums-1-dub 1093904513 10.25.100.143 (30 Aug 2004 15:21:53 -0700)
X-Original-Trace: 30 Aug 2004 15:21:53 -0700, nicelson-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3464
Article PK: 7369

For comparison, does not

ALTER <view_name> WITH RECOMPILE;

not also work around this?

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:4132d520@forums-2-dub...
> Thanks Nick,
>
> As you suggested, I looked into contacting support directly, but found I
> could not without paying for a support case.
>
> I cant justify my company having to pay to report a bug.
>
> I have found that I can identify the views which need to be manually
> compiled (opened & saved) from the following query:
>
> select table_name from sys.systable
> where table_name not in (select tname from sys.syscolumns)
>
> With a couple of views I had to drop and re-create them but for most I
> only needed to open - add a space - save.
>
> Unless I see a fix for this come thru in EBF's I will write a script to
> complete these actions on upgrade day.
>
> Nick Elson wrote:
>
> > Then you might want to contact support directly so they can look at
> > your situation. You may have hit something new.
> >
> > "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> > news:4132c90d@forums-2-dub...
> >
> >>ASA 9.0.1.1862
> >>
> >>
> >>Nick Elson wrote:
> >>
> >>
> >>>ASA 9 or ASA 9.0.1 ? AND build?
> >>>
> >>>And does your version contain this bug fix?
> >>>[if not the error may just turn out to be a non-issue
> >>> and your recreation efforts may not be accomplishing
> >>> anything other than validating the situation]
> >>>
> >>>Versions affected: 9.0.0 and above
> >>>Versions fixed: 9.0.1(1733)
> >>>Modules affected: server, dbtoolX.dll
> >>>Description: If a view was created which referenced a ***stored
> >>>procedure*** in the FROM
> >>>clause, rebuilding the database would fail. Also, if the procedure name
> >
> > was
> >
> >>>not qualified
> >>> with the owner of the procedure, users who were not members of the
> >>>procedure owner's
> >>>group would be unable to reference the view. Now, the catalog
> >
> > definitions of
> >
> >>>views which
> >>>reference stored procedures in the FROM clause will have those
> >
> > references
> >
> >>>qualified with
> >>>the procedure owner. An additional call to dbo.sa_recompile_views() in
> >
> > the
> >
> >>>reload script
> >>>will permit views which reference procedures to be recompiled after the
> >>>procedures have
> >>>been defined.
> >>>
> >>>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> >>>news:4132c182$1@forums-2-dub...
> >>>
> >>>
> >>>>I am attempting to prepare a customer's ASA 6 database for
> >>>>conversion/upgrade to ASA 9.
> >>>>
> >>>>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload
and
> >>>>reload the ASA 6 db into a new ASA 9 db.
> >>>>
> >>>>All goes well until the script performs CALL sp_compile_views(0).
> >>>>At this point it fails with mysterious errors.
> >>>>
> >>>>It fails with a 'more than one row' in resultset error, I understand
the
> >>>>normal meaning of this to be that a sub-query is returning multiple
> >
> > rows.
> >
> >>>>However, when I subsequently test the view by running the query in
ISQL
> >>>>it performs as expected. If I then edit and save the view (no changes)
> >>>>sa_recompile_views gets past this view and makes the same complaint
> >>>>about another view.
> >>>>
> >>>>Now the obvious easy answer is to open-edit-save all views, but we
have
> >>>>several hundred and I am trying to make upgrade day as painless as
> >>>>possible. ( I got bored with this approach after an hour or so )
> >>>>
> >>>>Can anyone tell me what is happening and/or how I can avoid this.
> >>>>
> >>>>TIA
> >>>>Geoff Sutcliffe
> >>>>Datagaard Pty Ltd
> >>>>South Australia
> >>>>
> >>>
> >>>
> >>>
> >
> >


GeoffAtDatagaard Posted on 2004-08-30 23:32:59.0Z
From: GeoffAtDatagaard <geoff@datagaard.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.7.2) Gecko/20040803
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: sa_compile_views failure when converting ASA 6 DB to ASA 9
References: <4132c182$1@forums-2-dub> <4132c7a8@forums-2-dub> <4132c90d@forums-2-dub> <4132cf6e$1@forums-2-dub> <4132d520@forums-2-dub> <4133a881@forums-1-dub>
In-Reply-To: <4133a881@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: adsl-088.cust203-87-21.sa.chariot.net.au
X-Original-NNTP-Posting-Host: adsl-088.cust203-87-21.sa.chariot.net.au
Message-ID: <4133b92b@forums-1-dub>
Date: 30 Aug 2004 16:32:59 -0700
X-Trace: forums-1-dub 1093908779 203.87.21.88 (30 Aug 2004 16:32:59 -0700)
X-Original-Trace: 30 Aug 2004 16:32:59 -0700, adsl-088.cust203-87-21.sa.chariot.net.au
Lines: 145
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3465
Article PK: 7368

I will try ALTER <view_name> WITH RECOMPILE in my next rehearsal.
I have now repaired all current problematic views.

Nick Elson wrote:
> For comparison, does not
>
> ALTER <view_name> WITH RECOMPILE;
>
> not also work around this?
>
> "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> news:4132d520@forums-2-dub...
>
>>Thanks Nick,
>>
>>As you suggested, I looked into contacting support directly, but found I
>>could not without paying for a support case.
>>
>>I cant justify my company having to pay to report a bug.
>>
>>I have found that I can identify the views which need to be manually
>>compiled (opened & saved) from the following query:
>>
>>select table_name from sys.systable
>>where table_name not in (select tname from sys.syscolumns)
>>
>>With a couple of views I had to drop and re-create them but for most I
>>only needed to open - add a space - save.
>>
>>Unless I see a fix for this come thru in EBF's I will write a script to
>>complete these actions on upgrade day.
>>
>>Nick Elson wrote:
>>
>>
>>>Then you might want to contact support directly so they can look at
>>>your situation. You may have hit something new.
>>>
>>>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
>>>news:4132c90d@forums-2-dub...
>>>
>>>
>>>>ASA 9.0.1.1862
>>>>
>>>>
>>>>Nick Elson wrote:
>>>>
>>>>
>>>>
>>>>>ASA 9 or ASA 9.0.1 ? AND build?
>>>>>
>>>>>And does your version contain this bug fix?
>>>>>[if not the error may just turn out to be a non-issue
>>>>>and your recreation efforts may not be accomplishing
>>>>>anything other than validating the situation]
>>>>>
>>>>>Versions affected: 9.0.0 and above
>>>>>Versions fixed: 9.0.1(1733)
>>>>>Modules affected: server, dbtoolX.dll
>>>>>Description: If a view was created which referenced a ***stored
>>>>>procedure*** in the FROM
>>>>>clause, rebuilding the database would fail. Also, if the procedure name
>>>
>>>was
>>>
>>>
>>>>>not qualified
>>>>>with the owner of the procedure, users who were not members of the
>>>>>procedure owner's
>>>>>group would be unable to reference the view. Now, the catalog
>>>
>>>definitions of
>>>
>>>
>>>>>views which
>>>>>reference stored procedures in the FROM clause will have those
>>>
>>>references
>>>
>>>
>>>>>qualified with
>>>>>the procedure owner. An additional call to dbo.sa_recompile_views() in
>>>
>>>the
>>>
>>>
>>>>>reload script
>>>>>will permit views which reference procedures to be recompiled after the
>>>>>procedures have
>>>>>been defined.
>>>>>
>>>>>"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
>>>>>news:4132c182$1@forums-2-dub...
>>>>>
>>>>>
>>>>>
>>>>>>I am attempting to prepare a customer's ASA 6 database for
>>>>>>conversion/upgrade to ASA 9.
>>>>>>
>>>>>>I am using ASA 9 (Developer Latest Edition) Sybase Central to unload
>
> and
>
>>>>>>reload the ASA 6 db into a new ASA 9 db.
>>>>>>
>>>>>>All goes well until the script performs CALL sp_compile_views(0).
>>>>>>At this point it fails with mysterious errors.
>>>>>>
>>>>>>It fails with a 'more than one row' in resultset error, I understand
>
> the
>
>>>>>>normal meaning of this to be that a sub-query is returning multiple
>>>
>>>rows.
>>>
>>>
>>>>>>However, when I subsequently test the view by running the query in
>
> ISQL
>
>>>>>>it performs as expected. If I then edit and save the view (no changes)
>>>>>>sa_recompile_views gets past this view and makes the same complaint
>>>>>>about another view.
>>>>>>
>>>>>>Now the obvious easy answer is to open-edit-save all views, but we
>
> have
>
>>>>>>several hundred and I am trying to make upgrade day as painless as
>>>>>>possible. ( I got bored with this approach after an hour or so )
>>>>>>
>>>>>>Can anyone tell me what is happening and/or how I can avoid this.
>>>>>>
>>>>>>TIA
>>>>>>Geoff Sutcliffe
>>>>>>Datagaard Pty Ltd
>>>>>>South Australia
>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>
>