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.

comments on tables/columns

11 posts in Product Futures Discussion Last posting was on 2002-03-15 03:41:42.0Z
George Saylor Posted on 2002-03-11 12:59:59.0Z
From: "George Saylor" <gmsayloriii@email.msn.com>
Subject: comments on tables/columns
Date: Mon, 11 Mar 2002 07:59:59 -0500
Lines: 14
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
Message-ID: <ZeHAhyPyBHA.219@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: tow9dhcp209.towson01.md.comcast.net 68.33.9.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:700
Article PK: 94230

At best a "nice to have",

CREATE TABLE x
(
col1 int not null comment "My column",
col2 varchar(30) null
)
GO
COMMENT ON x "My table"
GO
COMMENT ON x(col2) "My other column"
GO


Anthony Mandic Posted on 2002-03-12 03:39:47.0Z
Message-ID: <3C8D7883.26083267@start.com.au>
Date: Tue, 12 Mar 2002 13:39:47 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: comments on tables/columns
References: <ZeHAhyPyBHA.219@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 9
NNTP-Posting-Host: DC-25-108.bpb.bigpond.com 203.40.25.108
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:695
Article PK: 94221


George Saylor wrote:
>
> At best a "nice to have",
>
> CREATE TABLE x
> (
> col1 int not null comment "My column",
> col2 varchar(30) null
> )
> GO
> COMMENT ON x "My table"
> GO
> COMMENT ON x(col2) "My other column"
> GO

What's wrong with having comments in your source
scripts or your modelling app?

-am © 2002


George Saylor Posted on 2002-03-12 04:09:10.0Z
From: "George Saylor" <gmsayloriii@email.msn.com>
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au>
Subject: Re: comments on tables/columns
Date: Mon, 11 Mar 2002 23:09:10 -0500
Lines: 14
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
Message-ID: <xn7XouXyBHA.318@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: tow9dhcp209.towson01.md.comcast.net 68.33.9.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:692
Article PK: 94216


> What's wrong with having comments in your source
> scripts or your modelling app?

nothing

>
Whats wrong with having comments on tables?

It would allow the data dictionary to be stored with the system catalogs
making it more easily queried


Anthony Mandic Posted on 2002-03-12 13:53:17.0Z
Message-ID: <3C8E084D.B007A95@start.com.au>
Date: Tue, 12 Mar 2002 23:53:17 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: comments on tables/columns
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 14
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:688
Article PK: 94220


George Saylor wrote:
>
> > What's wrong with having comments in your source
> > scripts or your modelling app?
>
> nothing
>
> >
> Whats wrong with having comments on tables?

Nothing :-)

> It would allow the data dictionary to be stored with the system catalogs
> making it more easily queried

As I've said elsewhere more than once, the database
shouldn't act as the repository for your source code.
This, of course, applies more to sprocs - some of which
can't be reverse engineered correctly.

-am © 2002


Jim Egan Posted on 2002-03-13 06:20:12.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: comments on tables/columns
Date: Tue, 12 Mar 2002 23:20:12 -0700
Message-ID: <MPG.16f89d8c3a754f598bbc5@forums.sybase.com>
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 13
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:677
Article PK: 94205


sp_am_block@start.com.au wrote...
> This, of course, applies more to sprocs - some of which
> can't be reverse engineered correctly.
>

Boy, you got that right! I'm trying to track down why in 90,000 + lines of reverse
engineered stored procedures there are so many formatting errors. I even tried DEFNCOPY
and the formatting errors were still there. This was exceptionally frustrating.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Anthony Mandic Posted on 2002-03-13 09:55:55.0Z
Message-ID: <3C8F222B.78B19A25@start.com.au>
Date: Wed, 13 Mar 2002 19:55:55 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: comments on tables/columns
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 24
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:675
Article PK: 94202


Jim Egan wrote:

> > This, of course, applies more to sprocs - some of which
> > can't be reverse engineered correctly.
>
> Boy, you got that right! I'm trying to track down why in 90,000 + lines of reverse
> engineered stored procedures there are so many formatting errors. I even tried DEFNCOPY
> and the formatting errors were still there. This was exceptionally frustrating.

Well, I wasn't thinking of formatting problems (which is another
issue to consider when reverse engineering as is running
sp_hidetext!) so much as statements in the source script that
may appear before the 'create procedure' statement. If one
had a 'create table' statement for a temp table in the script
before the 'create proc' statement there's no way to find that
and reverse engineer it. The system tables sprocs can be
extracted from only contain the actual sproc code and
nothing else.

-am © 2002


Roger Broadbent Posted on 2002-03-13 12:12:00.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com> <3C8F222B.78B19A25@start.com.au>
Subject: Re: comments on tables/columns
Date: Wed, 13 Mar 2002 12:12:00 -0000
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <EBH86koyBHA.322@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:673
Article PK: 94201

My impression was that the ASE 12 upgrade effectively re-scripted the stored
procedures from syscomments? We routinely use the technique you describe -
creating a temp table then accessing it from a stored procedure - but our
procedures (mostly) upgraded from ASE 11.9.2 to ASE 12. The only problems
were to do with outer joins in subqueries and HAVING clauses.

I assumed that the upgrade process must reverse-engineer the temporary table
DDL from the stored procedure query tree on disk.

If this is the case, could this be supplied, perhaps as an option to
defncopy, as a general solution?

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

Anthony Mandic <sp_am_block@start.com.au> wrote in message
news:3C8F222B.78B19A25@start.com.au...
> Jim Egan wrote:
>
> > > This, of course, applies more to sprocs - some of which
> > > can't be reverse engineered correctly.
> >
> > Boy, you got that right! I'm trying to track down why in 90,000 + lines
of reverse
> > engineered stored procedures there are so many formatting errors. I even
tried DEFNCOPY
> > and the formatting errors were still there. This was exceptionally
frustrating.
>
> Well, I wasn't thinking of formatting problems (which is another
> issue to consider when reverse engineering as is running
> sp_hidetext!) so much as statements in the source script that
> may appear before the 'create procedure' statement. If one
> had a 'create table' statement for a temp table in the script
> before the 'create proc' statement there's no way to find that
> and reverse engineer it. The system tables sprocs can be
> extracted from only contain the actual sproc code and
> nothing else.
>
> -am © 2002


Anthony Mandic Posted on 2002-03-13 14:12:00.0Z
Message-ID: <3C8F5E30.45F21A1B@start.com.au>
Date: Thu, 14 Mar 2002 00:12:00 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: comments on tables/columns
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com> <3C8F222B.78B19A25@start.com.au> <EBH86koyBHA.322@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 36
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:672
Article PK: 94200


Roger Broadbent wrote:
>
> My impression was that the ASE 12 upgrade effectively re-scripted the stored
> procedures from syscomments?

Recreated would be a more appropriate description.

> We routinely use the technique you describe -
> creating a temp table then accessing it from a stored procedure - but our
> procedures (mostly) upgraded from ASE 11.9.2 to ASE 12. The only problems
> were to do with outer joins in subqueries and HAVING clauses.

Yes, but that isn't a problem. The method you use allows the
object to exist when the sproc is loaded. This is fine and
it can then be recompiled. When you reverse engineer it, the
create table statement isn't known. Your other problems aren't
related to any of this - just the usual change in optimiser
behaviour. Unless you mean you had problems in the sproc
code that showed up when they were upgraded?

> I assumed that the upgrade process must reverse-engineer the temporary table
> DDL from the stored procedure query tree on disk.

Well, it needs the sproc code to be in syscomments and
not deleted. I think it Bret who's mentioned this from
time to time as well. I think not having sp_hidetext
run over it is a requirement as well, but I'm not
familiar with the actual process. Someone from Sybase
would be the most appropriate person to elaborate.
There might also be something in Sybase's tech docs.

-am © 2002


George Saylor Posted on 2002-03-13 12:08:22.0Z
From: "George Saylor" <gmsayloriii@email.msn.com>
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com> <3C8F222B.78B19A25@start.com.au>
Subject: Re: comments on tables/columns
Date: Wed, 13 Mar 2002 07:08:22 -0500
Lines: 17
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
Message-ID: <TFXkGfoyBHA.318@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: tow9dhcp209.towson01.md.comcast.net 68.33.9.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:674
Article PK: 94203

One more scenario, corruption that takes out syscomments. Not a bad dream
either, I hit this 2 weeks ago when ALLOCATION was corrupted in one of our
databases. This could only be repaired with bcp of user data and DDL(which
could not be reliably extracted from the server).

Fortunately, this is not what I meant. Right now, in our <weak little>data
repository we have tables that look quite a bit like sysobjects, sysindexes
and syscolumns minus the status info and added a varchar(255) to describe
the attribute or object. It would just be lower maintenance to have that
info stored in the database. ASA, ASIQ and a couple *other* vendors already
have this

Our source code goes into Visual Source Safe or SCCS.

George


Anthony Mandic Posted on 2002-03-13 14:16:59.0Z
Message-ID: <3C8F5F5B.502598EB@start.com.au>
Date: Thu, 14 Mar 2002 00:16:59 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: comments on tables/columns
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com> <3C8F222B.78B19A25@start.com.au> <TFXkGfoyBHA.318@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 36
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:671
Article PK: 94199


George Saylor wrote:
>
> One more scenario, corruption that takes out syscomments. Not a bad dream
> either, I hit this 2 weeks ago when ALLOCATION was corrupted in one of our
> databases. This could only be repaired with bcp of user data and DDL(which
> could not be reliably extracted from the server).

Or a load of a dump?

> Fortunately, this is not what I meant. Right now, in our <weak little>data
> repository we have tables that look quite a bit like sysobjects, sysindexes
> and syscolumns minus the status info and added a varchar(255) to describe
> the attribute or object. It would just be lower maintenance to have that
> info stored in the database. ASA, ASIQ and a couple *other* vendors already
> have this

It might be nice then, to be able to do an "alter table ... add ..."
to add a comment field to sysobjects etc. As an alternative, rather
than coping the bulk of the fields, just copy the key and do a
join between them. Should be easy enough to modify the appropriate
system sprocs in the installmaster script too (but the same setup
would need to exist in sybsystemprocs ... and I just remembered
that non-system tables don't get the same object ids - even if
you install them in model! Damn! Oh well, so much for that idea).

> Our source code goes into Visual Source Safe or SCCS.

Good! I prefer RCS to SCCS but that's not important right now.

-am © 2002


George Saylor Posted on 2002-03-15 03:41:42.0Z
From: "George Saylor" <gmsayloriii@email.msn.com>
References: <ZeHAhyPyBHA.219@forums.sybase.com> <3C8D7883.26083267@start.com.au> <xn7XouXyBHA.318@forums.sybase.com> <3C8E084D.B007A95@start.com.au> <MPG.16f89d8c3a754f598bbc5@forums.sybase.com> <3C8F222B.78B19A25@start.com.au> <TFXkGfoyBHA.318@forums.sybase.com> <3C8F5F5B.502598EB@start.com.au>
Subject: Re: comments on tables/columns
Date: Thu, 14 Mar 2002 22:41:42 -0500
Lines: 11
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
Message-ID: <#a1yZN9yBHA.333@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: tow9dhcp209.towson01.md.comcast.net 68.33.9.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:665
Article PK: 94193


> Or a load of a dump?
>

dbcc checkstorage was run weekly, corruption then was "soft" and got quickly
worse, the dump after the last "clean" dbcc was too old for comfort...so bcp
bcp bcp

which leads me to dbcc on archive, any chance this will come before ASE
15???