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.

Same SQL in a procedure is behaving differently than in SQL Batch

4 posts in General Discussion Last posting was on 2011-07-22 15:27:26.0Z
VKumar Posted on 2011-07-21 10:13:28.0Z
Sender: 50f5.4e27fa4f.1804289383@sybase.com
From: VKumar
Newsgroups: sybase.public.ase.general
Subject: Same SQL in a procedure is behaving differently than in SQL Batch
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e27fbc8.5125.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jul 2011 03:13:28 -0700
X-Trace: forums-1-dub 1311243208 10.22.241.41 (21 Jul 2011 03:13:28 -0700)
X-Original-Trace: 21 Jul 2011 03:13:28 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30361
Article PK: 72540

Dear Sybase team,

I am using Sybase version:
Adaptive Server Enterprise/12.5.4/EBF 16800
ESD#10/P/x86_64/Enterprise Linux/ase1254/2159/64-bit/OPT/Mon
Nov 2 03:14:39 2009.

I am facing a difference in SQL query.
When a query is run inside a procedure, it is giving
different value for a column in the result-set. (Null value
).

But if the same SQL is run outside in a separate batch, it
is giving different value (Non-null value)

I ran the proc with recompile, but still same difference.

But i have not re-created the proc, as it is in production
environment.

Is there any reason for this behaviour ?

Thanks in advance
VKumar


jobless Posted on 2011-07-21 18:47:34.0Z
Sender: 5f23.4e286308.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Same SQL in a procedure is behaving differently than in SQL Batch
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e287446.612e.1681692777@sybase.com>
References: <4e27fbc8.5125.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jul 2011 11:47:34 -0700
X-Trace: forums-1-dub 1311274054 10.22.241.41 (21 Jul 2011 11:47:34 -0700)
X-Original-Trace: 21 Jul 2011 11:47:34 -0700, 10.22.241.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30363
Article PK: 72542


> Dear Sybase team,
>
> I am using Sybase version:
> Adaptive Server Enterprise/12.5.4/EBF 16800
> ESD#10/P/x86_64/Enterprise
> Linux/ase1254/2159/64-bit/OPT/Mon Nov 2 03:14:39 2009.
>
> I am facing a difference in SQL query.
> When a query is run inside a procedure, it is giving
> different value for a column in the result-set. (Null
> value ).
>
> But if the same SQL is run outside in a separate batch, it
> is giving different value (Non-null value)
>
> I ran the proc with recompile, but still same difference.
>
> But i have not re-created the proc, as it is in production
> environment.
>
> Is there any reason for this behaviour ?
>
> Thanks in advance
> VKumar

check few stupid things - just to keep billing your hours :D

Is it a simple proc? Since you can re-run it in prod i
believe it is safe for you to run it anytime in prod. Did
you try creating a NEW procedure with SAME source code you
are using when running as BATCH and running the NEW proc -
what did you seeeeee?

Now Serious Stuff: what is the query, what are the
parameters passed, what other dependencies this query has on
other values computed in the proc? Are you sure the batch
you are running is exactly identical to the procedure code?


VKumar Posted on 2011-07-22 14:09:15.0Z
Sender: 50f5.4e27fa4f.1804289383@sybase.com
From: VKumar
Newsgroups: sybase.public.ase.general
Subject: Re: Same SQL in a procedure is behaving differently than in SQL Batch
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e29848b.1c9.1681692777@sybase.com>
References: <4e287446.612e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jul 2011 07:09:15 -0700
X-Trace: forums-1-dub 1311343755 10.22.241.41 (22 Jul 2011 07:09:15 -0700)
X-Original-Trace: 22 Jul 2011 07:09:15 -0700, 10.22.241.41
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30364
Article PK: 72543

Hi,

Thanks for your reply.
Actually, the procedure was corrupted.
A Cloned proc was working fine.
So, re-creating the same proc by dropping old proc worked.

Thanks
VKumar

> > Dear Sybase team,
> >
> > I am using Sybase version:
> > Adaptive Server Enterprise/12.5.4/EBF 16800
> > ESD#10/P/x86_64/Enterprise
> > Linux/ase1254/2159/64-bit/OPT/Mon Nov 2 03:14:39 2009.
> >
> > I am facing a difference in SQL query.
> > When a query is run inside a procedure, it is giving
> > different value for a column in the result-set. (Null
> > value ).
> >
> > But if the same SQL is run outside in a separate batch,
> > it is giving different value (Non-null value)
> >
> > I ran the proc with recompile, but still same
> difference. >
> > But i have not re-created the proc, as it is in
> > production environment.
> >
> > Is there any reason for this behaviour ?
> >
> > Thanks in advance
> > VKumar
>
> check few stupid things - just to keep billing your hours
> :D
>
> Is it a simple proc? Since you can re-run it in prod i
> believe it is safe for you to run it anytime in prod. Did
> you try creating a NEW procedure with SAME source code you
> are using when running as BATCH and running the NEW proc -
> what did you seeeeee?
>
> Now Serious Stuff: what is the query, what are the
> parameters passed, what other dependencies this query has
> on other values computed in the proc? Are you sure the
> batch you are running is exactly identical to the
> procedure code?


jobless Posted on 2011-07-22 15:27:26.0Z
Sender: 6459.4e288ac5.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Same SQL in a procedure is behaving differently than in SQL Batch
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e2996de.3fd.1681692777@sybase.com>
References: <4e29848b.1c9.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jul 2011 08:27:26 -0700
X-Trace: forums-1-dub 1311348446 10.22.241.41 (22 Jul 2011 08:27:26 -0700)
X-Original-Trace: 22 Jul 2011 08:27:26 -0700, 10.22.241.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30365
Article PK: 72544


> Hi,
>
> Thanks for your reply.
> Actually, the procedure was corrupted.
> A Cloned proc was working fine.
> So, re-creating the same proc by dropping old proc worked.
>
> Thanks
> VKumar
>
> > > Dear Sybase team,
> > >
> > > I am using Sybase version:
> > > Adaptive Server Enterprise/12.5.4/EBF 16800
> > > ESD#10/P/x86_64/Enterprise
> > > Linux/ase1254/2159/64-bit/OPT/Mon Nov 2 03:14:39
> > 2009. >
> > > I am facing a difference in SQL query.
> > > When a query is run inside a procedure, it is giving
> > > different value for a column in the result-set. (Null
> > > value ).
> > >
> > > But if the same SQL is run outside in a separate batch
> > > , it is giving different value (Non-null value)
> > >
> > > I ran the proc with recompile, but still same
> > difference. >
> > > But i have not re-created the proc, as it is in
> > > production environment.
> > >
> > > Is there any reason for this behaviour ?
> > >
> > > Thanks in advance
> > > VKumar
> >
> > check few stupid things - just to keep billing your
> > hours :D
> >
> > Is it a simple proc? Since you can re-run it in prod i
> > believe it is safe for you to run it anytime in prod.
> > Did you try creating a NEW procedure with SAME source
> > code you are using when running as BATCH and running the
> > NEW proc - what did you seeeeee?
> >
> > Now Serious Stuff: what is the query, what are the
> > parameters passed, what other dependencies this query
> > has on other values computed in the proc? Are you sure
> > the batch you are running is exactly identical to the
> > procedure code?

shame, you just reported the proc as corrupt. I would have
renamed it, re-created the proc and grilled the re-named
proc code till I would have had some clue other than 'proc
is corrupt';