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.

Execute immediate error variable

8 posts in Product Futures Discussion Last posting was on 2002-01-22 20:15:58.0Z
Carl Kayser Posted on 2002-01-18 15:23:43.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Subject: Execute immediate error variable
Date: Fri, 18 Jan 2002 10:23:43 -0500
Lines: 11
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <HZJXvTDoBHA.140@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:916
Article PK: 94440

Currently if one uses this feature [execute ("string" | char_variable)]
@@error will be 0 regardless as to whether the argument works. (It's 0
because "execute" executed.) A new global variable equivalent to the
@@error value of the (last) statement within "string" | char_variable would
be useful for me.

Instead of sprinkling print statements and recompiling new code I could
permanently have an error checking block and print "string" | char_variable
when appropriate.


Bret Halford Posted on 2002-01-18 16:27:56.0Z
Message-ID: <3C484D0C.22FD5394@sybase.com>
Date: Fri, 18 Jan 2002 09:27:56 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Execute immediate error variable
References: <HZJXvTDoBHA.140@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 19
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:915
Article PK: 94441


Carl Kayser wrote:

> Currently if one uses this feature [execute ("string" | char_variable)]
> @@error will be 0 regardless as to whether the argument works. (It's 0
> because "execute" executed.) A new global variable equivalent to the
> @@error value of the (last) statement within "string" | char_variable would
> be useful for me.
>
> Instead of sprinkling print statements and recompiling new code I could
> permanently have an error checking block and print "string" | char_variable
> when appropriate.

Hi Carl,

As a workaround, how about having a datarow locked table with columns (spid,
error), as the last
statement in every execute immediate you update the appropriate row with value
of @@error ?

-bret


Carl Kayser Posted on 2002-01-18 18:24:24.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com>
Subject: Re: Execute immediate error variable
Date: Fri, 18 Jan 2002 13:24:24 -0500
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <a2bAo4EoBHA.271@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:913
Article PK: 94438

Technically this would appear to work with either temporary or permanent
tables. The drawbacks (compared to the original request) would be:

(1) Assuming a permanent table ... it's doubtful that there would be a
performance hit from contention. I haven't thought through the tradeoffs of
a single, global table versus one per database.

(2) Assuming a temporary table ... this would probably only work for stored
procedures. This was my scope of interest anyway. So I would have to
always create the temp table at the beginning of the driver procedure.

(3) Some form of cleanup (delete) would have to be done after each execute
immediate since there could be a number of execute immediate statements
within a session.


So I agree that your workaround would do the job (and I may implement this
technique with temp tables). But I would prefer a (session) global
variable. (I wonder if other people have need for this?)

"Bret Halford" <bret@sybase.com> wrote in message
news:3C484D0C.22FD5394@sybase.com...
>
>
> Carl Kayser wrote:
>
> > Currently if one uses this feature [execute ("string" | char_variable)]
> > @@error will be 0 regardless as to whether the argument works. (It's 0
> > because "execute" executed.) A new global variable equivalent to the
> > @@error value of the (last) statement within "string" | char_variable
would
> > be useful for me.
> >
> > Instead of sprinkling print statements and recompiling new code I could
> > permanently have an error checking block and print "string" |
char_variable
> > when appropriate.
>
> Hi Carl,
>
> As a workaround, how about having a datarow locked table with columns
(spid,
> error), as the last
> statement in every execute immediate you update the appropriate row with
value
> of @@error ?
>
> -bret
>
>
>


Bret Halford Posted on 2002-01-18 18:58:00.0Z
Message-ID: <3C487037.E2A5AD3D@sybase.com>
Date: Fri, 18 Jan 2002 11:58:00 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Execute immediate error variable
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com> <a2bAo4EoBHA.271@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 37
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:911
Article PK: 94436


Carl Kayser wrote:

> Technically this would appear to work with either temporary or permanent
> tables. The drawbacks (compared to the original request) would be:
>
> (1) Assuming a permanent table ... it's doubtful that there would be a
> performance hit from contention. I haven't thought through the tradeoffs of
> a single, global table versus one per database.
>
> (2) Assuming a temporary table ... this would probably only work for stored
> procedures. This was my scope of interest anyway. So I would have to
> always create the temp table at the beginning of the driver procedure.
>
> (3) Some form of cleanup (delete) would have to be done after each execute
> immediate since there could be a number of execute immediate statements
> within a session.
>
> So I agree that your workaround would do the job (and I may implement this
> technique with temp tables). But I would prefer a (session) global
> variable. (I wonder if other people have need for this?)
>
>

Hi Carl,

What version of ASE are you running on? I just experimented a little and I
find that on
ASE 12.0.0.3 ESD #4 I get these results:

1> select @@error
2> go

-----------
0

(1 row affected)
1> execute ('select foobar')
2> go
Msg 207, Level 16, State 4:
Server 'REL120_bret_sun', Line 1:
Invalid column name 'foobar'.
1> select @@error
2> go

-----------
207

(1 row affected)


-bret


Carl Kayser Posted on 2002-01-18 19:44:14.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com> <a2bAo4EoBHA.271@forums.sybase.com> <3C487037.E2A5AD3D@sybase.com>
Subject: Re: Execute immediate error variable
Date: Fri, 18 Jan 2002 14:44:14 -0500
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <9HJVPlFoBHA.374@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:910
Article PK: 94434

You are right Bret. I'm on 12.0.0.3 EBF 9626 and I got the same results as
you with either a variable or a string. I thought that I had tested this
before ... which gave me reason to post this item.

Sorry for creating the nuisance.

>
> Hi Carl,
>
> What version of ASE are you running on? I just experimented a little and
I
> find that on
> ASE 12.0.0.3 ESD #4 I get these results:
>
> 1> select @@error
> 2> go
>
> -----------
> 0
>
> (1 row affected)
> 1> execute ('select foobar')
> 2> go
> Msg 207, Level 16, State 4:
> Server 'REL120_bret_sun', Line 1:
> Invalid column name 'foobar'.
> 1> select @@error
> 2> go
>
> -----------
> 207
>
> (1 row affected)
>
>
> -bret
>
>
>


Bret Halford Posted on 2002-01-18 21:31:23.0Z
Message-ID: <3C48942B.5465D355@sybase.com>
Date: Fri, 18 Jan 2002 14:31:23 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Execute immediate error variable
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com> <a2bAo4EoBHA.271@forums.sybase.com> <3C487037.E2A5AD3D@sybase.com> <9HJVPlFoBHA.374@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 26
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:908
Article PK: 94432

Not at all. If you do find any cases where it doesn't work, let me know. I
know there
have been a few bugs where @@error didn't get set in certain circumstances.

-bret

Carl Kayser wrote:

> You are right Bret. I'm on 12.0.0.3 EBF 9626 and I got the same results as
> you with either a variable or a string. I thought that I had tested this
> before ... which gave me reason to post this item.
>
> Sorry for creating the nuisance.
>
> >
> > Hi Carl,
> >
> > What version of ASE are you running on? I just experimented a little and
> I
> > find that on
> > ASE 12.0.0.3 ESD #4 I get these results:
> >
> > 1> select @@error
> > 2> go
> >
> > -----------
> > 0
> >
> > (1 row affected)
> > 1> execute ('select foobar')
> > 2> go
> > Msg 207, Level 16, State 4:
> > Server 'REL120_bret_sun', Line 1:
> > Invalid column name 'foobar'.
> > 1> select @@error
> > 2> go
> >
> > -----------
> > 207
> >
> > (1 row affected)
> >
> >
> > -bret
> >
> >
> >


Carl Kayser Posted on 2002-01-22 20:15:58.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com> <a2bAo4EoBHA.271@forums.sybase.com> <3C487037.E2A5AD3D@sybase.com> <9HJVPlFoBHA.374@forums.sybase.com> <3C48942B.5465D355@sybase.com>
Subject: Re: Execute immediate error variable
Date: Tue, 22 Jan 2002 15:15:58 -0500
Lines: 79
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <MRMJoJ4oBHA.373@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:896
Article PK: 94422

I don't receive the @@error value of 4622 (although I do receive the message
text) for:


declare @cmdstring varchar (255)
select @cmdstring = 'revoke select on sysobjects from public'
execute (@cmdstring)
select @@error

(1 row affected)

-----------
0

Some or all of the specified privileges were not revoked because, they were
not granted, or they were granted by some other user.

"Bret Halford" <bret@sybase.com> wrote in message
news:3C48942B.5465D355@sybase.com...
> Not at all. If you do find any cases where it doesn't work, let me know.
I
> know there
> have been a few bugs where @@error didn't get set in certain
circumstances.
>
> -bret
>
>
> Carl Kayser wrote:
>
> > You are right Bret. I'm on 12.0.0.3 EBF 9626 and I got the same results
as
> > you with either a variable or a string. I thought that I had tested
this
> > before ... which gave me reason to post this item.
> >
> > Sorry for creating the nuisance.
> >
> > >
> > > Hi Carl,
> > >
> > > What version of ASE are you running on? I just experimented a little
and
> > I
> > > find that on
> > > ASE 12.0.0.3 ESD #4 I get these results:
> > >
> > > 1> select @@error
> > > 2> go
> > >
> > > -----------
> > > 0
> > >
> > > (1 row affected)
> > > 1> execute ('select foobar')
> > > 2> go
> > > Msg 207, Level 16, State 4:
> > > Server 'REL120_bret_sun', Line 1:
> > > Invalid column name 'foobar'.
> > > 1> select @@error
> > > 2> go
> > >
> > > -----------
> > > 207
> > >
> > > (1 row affected)
> > >
> > >
> > > -bret
> > >
> > >
> > >
>


Rob Verschoor Posted on 2002-01-18 18:56:59.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <HZJXvTDoBHA.140@forums.sybase.com> <3C484D0C.22FD5394@sybase.com> <a2bAo4EoBHA.271@forums.sybase.com>
Subject: Re: Execute immediate error variable
Date: Fri, 18 Jan 2002 19:56:59 +0100
Lines: 80
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <RGFsA8HoBHA.123@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: p1614.asi.euronet.nl 194.134.8.158
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:906
Article PK: 94446

I support Carl's request for a better way error status handling from
exec immediate; a session-specific global variable (along the lines of
@@error) would by far be the most convenient solution, as it follows
the existing logic of @@error.

Rob V.

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:a2bAo4EoBHA.271@forums.sybase.com...
> Technically this would appear to work with either temporary or
permanent
> tables. The drawbacks (compared to the original request) would be:
>
> (1) Assuming a permanent table ... it's doubtful that there would be
a
> performance hit from contention. I haven't thought through the
tradeoffs of
> a single, global table versus one per database.
>
> (2) Assuming a temporary table ... this would probably only work for
stored
> procedures. This was my scope of interest anyway. So I would have
to
> always create the temp table at the beginning of the driver
procedure.
>
> (3) Some form of cleanup (delete) would have to be done after each
execute
> immediate since there could be a number of execute immediate
statements
> within a session.
>
>
> So I agree that your workaround would do the job (and I may
implement this
> technique with temp tables). But I would prefer a (session) global
> variable. (I wonder if other people have need for this?)
>
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:3C484D0C.22FD5394@sybase.com...
> >
> >
> > Carl Kayser wrote:
> >
> > > Currently if one uses this feature [execute ("string" |
char_variable)]
> > > @@error will be 0 regardless as to whether the argument works.
(It's 0
> > > because "execute" executed.) A new global variable equivalent
to the
> > > @@error value of the (last) statement within "string" |
char_variable
> would
> > > be useful for me.
> > >
> > > Instead of sprinkling print statements and recompiling new code
I could
> > > permanently have an error checking block and print "string" |
> char_variable
> > > when appropriate.
> >
> > Hi Carl,
> >
> > As a workaround, how about having a datarow locked table with
columns
> (spid,
> > error), as the last
> > statement in every execute immediate you update the appropriate
row with
> value
> > of @@error ?
> >
> > -bret
> >
> >
> >
>
>