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 issue still exists, tried out almost everything....

2 posts in General Discussion Last posting was on 2007-03-05 22:30:37.0Z
EY Posted on 2007-03-01 13:22:19.0Z
Sender: 43bd.45e33bcd.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Same issue still exists, tried out almost everything....
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45e6d38b.15d5.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Mar 2007 05:22:19 -0800
X-Trace: forums-1-dub 1172755339 10.22.241.41 (1 Mar 2007 05:22:19 -0800)
X-Original-Trace: 1 Mar 2007 05:22:19 -0800, 10.22.241.41
Lines: 82
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5859
Article PK: 1846

Below I had started a thread about having some rows not get
updated during an import routine, randomly.

I've broken the original procedure into 5 steps, and thus 5
procedures (IMPORT_1, IMPORT_2...) for testing.

Steps 2 and 3 are in charge of updating location data of
patients within the organization, and also their dietary
needs.

I perform a rather simple update statement in my
procedure....

UPDATE PATIENTS inner join Stagetable on
patients.billing_number = stagetable.billingnumber
SET Patients.Unit = Stagetable.Area,
Patients.Room = Stagetable.Room,
Patients.Status = 'CHANGE',
Patients.onServiceReason = 'Location Change',
Patients.Update_Date = Date(Now())
where (patients.Unit != Stagetable.Area OR Patients.Room !=
Stagetable.Room) and patients.status != 'Discharged';

Statement works fine if I log into the DB through ISQL,
through ODBC, and I copy/paste the command, click run, and
all is well.

If I run it through a batch file, scheduled task, event,
anything else automatic, it doesn't run completely, I always
have a few rows left that are not updated.

I have even tried putting the above statement in a while
loop, that checks to see if there are >0 rows that meet the
criteria of having unmatched locations, and if so, then run
the update statement (almost like recursion), and it STILL
misses some rows.

In the procedure I placed in a few SQLSTATE and SQLCODE
checks, and it runs fine, no issues, it just isn't updating
some rows for some reason that is beyond my knowledge.

Steps for the entire process are like this -

File is exported from another system to a network drive
overnight
5:00 AM, my first batch file runs and does an input into
STAGETABLE, and formats some data, nothing fancy. Always
works fine.

First step is to discharge anyone from the list that is no
longer there. Same deal, does a simple procedure with an
update where not exists. I once in a while have the same
issue with this procedure, where an occassional row is
missed. Called from a batch file/scheduled task, no errors
reported, just misses.

Next step is to do the location changes (code above), batch
file triggered with dbisql command, and same deal, misses
rows.

Same deal for step 3, updates diet information. Misses a
few rows.

Etc....

However, if I connect to the DB, through my ODBC, through
dbisql, and run the procedures, it runs completely and gets
all of the rows every time. I check the row counts where
items are mismatched right after the run, and it never says
"0" unless, like I said, I run it myself.

I use only one ODBC for everything from my pc. I have tried
running the batch files from both the server itself, and
from my pc, and like I said, anything automatic does not run
entirely, I need to physically do it.

I have no idea what the issue can be. I'm lost and
incredibly frustrated.

Fire back some ideas....

EY


Stephen Rice Posted on 2007-03-05 22:30:37.0Z
From: "Stephen Rice" <sriceATianywhereDOTcom>
Newsgroups: ianywhere.public.general
References: <45e6d38b.15d5.1681692777@sybase.com>
Subject: Re: Same issue still exists, tried out almost everything....
Lines: 103
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: srice-xp2.sybase.com
X-Original-NNTP-Posting-Host: srice-xp2.sybase.com
Message-ID: <45ec9a0d$1@forums-1-dub>
Date: 5 Mar 2007 14:30:37 -0800
X-Trace: forums-1-dub 1173133837 10.25.98.210 (5 Mar 2007 14:30:37 -0800)
X-Original-Trace: 5 Mar 2007 14:30:37 -0800, srice-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5863
Article PK: 1847

I think you've pretty much exhausted the ability of the folks who hang out
here to help without actually touching your database

A couple of questions/thoughts before I fall back on "open a support case"..
- is there *anything* different about the connection from dbisql? (e.g.
userid?)
- I'd add a statement (before you run the problem statement) that selects
exactly the same set of rows and dumps them to the log so you can see who is
in the result set. Is it everyone you expect? If they are not (and I
expect them to be missing) dump the entire table to the log to confirm the
rows are actually there when the statement is executing
--
/steve

Stephen Rice
iAnywhere (a Sybase Company)

<EY> wrote in message news:45e6d38b.15d5.1681692777@sybase.com...
> Below I had started a thread about having some rows not get
> updated during an import routine, randomly.
>
> I've broken the original procedure into 5 steps, and thus 5
> procedures (IMPORT_1, IMPORT_2...) for testing.
>
> Steps 2 and 3 are in charge of updating location data of
> patients within the organization, and also their dietary
> needs.
>
> I perform a rather simple update statement in my
> procedure....
>
> UPDATE PATIENTS inner join Stagetable on
> patients.billing_number = stagetable.billingnumber
> SET Patients.Unit = Stagetable.Area,
> Patients.Room = Stagetable.Room,
> Patients.Status = 'CHANGE',
> Patients.onServiceReason = 'Location Change',
> Patients.Update_Date = Date(Now())
> where (patients.Unit != Stagetable.Area OR Patients.Room !=
> Stagetable.Room) and patients.status != 'Discharged';
>
> Statement works fine if I log into the DB through ISQL,
> through ODBC, and I copy/paste the command, click run, and
> all is well.
>
> If I run it through a batch file, scheduled task, event,
> anything else automatic, it doesn't run completely, I always
> have a few rows left that are not updated.
>
> I have even tried putting the above statement in a while
> loop, that checks to see if there are >0 rows that meet the
> criteria of having unmatched locations, and if so, then run
> the update statement (almost like recursion), and it STILL
> misses some rows.
>
> In the procedure I placed in a few SQLSTATE and SQLCODE
> checks, and it runs fine, no issues, it just isn't updating
> some rows for some reason that is beyond my knowledge.
>
> Steps for the entire process are like this -
>
> File is exported from another system to a network drive
> overnight
> 5:00 AM, my first batch file runs and does an input into
> STAGETABLE, and formats some data, nothing fancy. Always
> works fine.
>
> First step is to discharge anyone from the list that is no
> longer there. Same deal, does a simple procedure with an
> update where not exists. I once in a while have the same
> issue with this procedure, where an occassional row is
> missed. Called from a batch file/scheduled task, no errors
> reported, just misses.
>
> Next step is to do the location changes (code above), batch
> file triggered with dbisql command, and same deal, misses
> rows.
>
> Same deal for step 3, updates diet information. Misses a
> few rows.
>
> Etc....
>
> However, if I connect to the DB, through my ODBC, through
> dbisql, and run the procedures, it runs completely and gets
> all of the rows every time. I check the row counts where
> items are mismatched right after the run, and it never says
> "0" unless, like I said, I run it myself.
>
> I use only one ODBC for everything from my pc. I have tried
> running the batch files from both the server itself, and
> from my pc, and like I said, anything automatic does not run
> entirely, I need to physically do it.
>
> I have no idea what the issue can be. I'm lost and
> incredibly frustrated.
>
> Fire back some ideas....
>
> EY