MS SQL file editing

[Jack_Crouch]

We have an AS/400 for our Enterprise Server. I use a file editor utility
called PFE (Passport File Editor). It is like DBU (for those AS/400
folks). In fact... you can simply use the UPDDTA command if you are
desperate.

I use these things for simple one - two record patches. For example...
numerous files get a "blank" record in them. That is records in F0115 (for
example) that have an address number of 0.


Very nice... These records reek unpredictable havoc with applications. I
have had them in F1201, F0101, F4801, etc., etc...

Anyway (I digress). I use SQL for any kind of mass update Cowboy activity.
Point is... our Central Objects are on MS SQL. I am longing for a simple
DB file editor to pull up, poke around, update, add, insert records in files
like F98761, etc. Instead, you are stuck with an unfriendly Query
Analyzer SQL product to do this.



(I say unfriendly because you have to screw around with system catalog files
to simply get field / file lists and such. Compare that with STRSQL or
Sequel on the AS/400... which give you simple F4=Prompts) And writing SQL
for simple things like this is time consuming...

Shirley.... There are tools out there to pop in and around MS SQL....
Does anyone know of any?



AS400 V4R4, B733.2, SP11.3, NT-SQL7 for CO

 

[vap]

Jack,

Regarding editing of MS SQL data:
I tend to use MS Access for this type of things. It has its problems -
you've got to link tables you want to work with, also it sometimes works
with Oracle and DB/400... in a strange way. But it has major advantages: you
can store temporary data locally, in the same database; you can use
everything you want, from Visual Basic to forms and reports; Queries are a
lot easier to build, and if you need performance, you can always convert
them to path-thru, which is basically the same as Query Analyzer; one of the
most important features - you can edit table contents directly (an issue
here, see below). And, it never corrupted data by itself (I mean it
sometimes corrupts it, when I'm wrong).There are two issues related to
manual editing of tables to be aware of: you can't add trailing spaces at
the end of string fields (however, it have never caused a problem, and in
the database trailing spaces are present - never figured out the reason) and
second, IMPORTANT - if you enter a blank value (or delete an existing
value), Access sets it to NULL. I've seen several cases when OneWorld was
not able to retrieve ANY records from a table with A (one, maybe several,
but not all) record with a NULL in one of the fields. This rare, but very
difficult to troubleshoot, because you have to 1) guess, 2) scan all fields
in all records for NULL (this is where VBA comes in handy).

There are other options (maybe 20% of my use, all taken together):
1. Query Analyzer for MS SQL 2000 is a lot nicer, and works with SQL 7.0
servers
2. It is sometimes easier to start in Enterprise Manager, open the table,
then open SQL script window and edit it to your liking (including delete or
insert).
3. There's "Cute DB Explorer", which was meant to be this user-friendly
(platform independent) editing tool. I have not found it really cute, so I
can't say where it can be found, but it is worth trying. It can be found in
Internet, shareware.
4. There's "WinSQL" (http://www.imranweb.com/), which is similar to Query
Analyzer in SQL2000, but I like it more (because it works with Oracle and
DB/400). Also a shareware.
5. If you use FAR (a file manager, www.rarsoft.com ), there are plug-ins for
it, which allow working with ODBC Data Sources, not as convenient as above,
but if you live in FAR, you can use them Those who know will know what I
mean.

Regards,
Vladimir Ponomarev

 

[Zoltan_Gyimesi]

Re: RE: MS SQL file editing

Jack, Vladimir

About using Access:
1.) Missing trailing spaces on the end of string fields sometimes could cause problems and incorrect results by our experiences.
2.) We already have some bad experiences using Access although some of our consultants use it frequently, mainly for one-time bulk upload from legacy system. (Of course, with the approriate caution and preventive checks).

On the other hand (as I am a developer
If you have recurring tasks then why do not create custom Forms, APPLs, UBEs for them. Sometimes it is not more complicated and don't take more times then using other tools (e.g. constructing SQL scripts). (sometimes for one-time tasks too)
There are several advantages of this method:
1.) You can see and/or enter special datas correctly (e.g. numbers with decimals, dates, specially indented values, etc.)
2.) You can enter the correct values using JDbase APIs (e.g. trailing spaces).
3.) You can prevent data corruption.
4.) Much more native soulution.
5.) You can also secure this appls in OW.
etc., etc., ...

You can use other tools for quick "read-only" inqueries and can use the native and safer solution for data manipulations.

These were an other 2 cents from a developer )

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[vap]

RE: RE: MS SQL file editing

Zoltan,

I'm a consultant, not a developer . And I do not want to start a flame
war, just my 5 more cents from me:
1. It drives me nuts when I need to open OneWorld, OMW, Table Design Aid
only to look at table structure (saving those files is not a good option
either). Waiting 2 days for cross-reference to be built (if you're lucky and
it build from the first attempt (and some have even complained that it never
worked for them)) - just to find out in which tables a particular field is
present - also not my understanding of fun time.
2. It would have driven me mad to write a custom OW application that would
go to Submitted Job File every minute to show me total number of UBE's
running, thereof mine. I could probably lose my sanity knowing, that it will
always fetch all records from there, using full table scans, because job
status is not an indexed file. And it would probably take it more then 1
minute to complete, if you have, say 1000 jobs, thereof 10 running. In
Access (or Visual Basic, I'm really not a programmer type) I can do this
with a single pass-thru query and twenty lines of VBA code.
3. I'm a consultant, I've gotta be running around. I had very unpleasant
experience in Packaging Tools, and even if it worked seamlessly - I would
doubt that my clients would approve if I bring my personal package with me,
just 'cause it makes things easier for me.
4. On my experience, I really rarely actually edit data open in Access
Tables, and if I do - it's normally a one-off. Once you know what broke -
you can (hopefully) fix the procedure so does not ever happen again. Even
for slightly more complex modifications I prefer to do all modifications via
queries - one thing to avoid missing blanks (never caused a problem for me,
really, but I take care) and those dreaded NULLs. To give you an example - I
would probably edit the table directly if I need to delete one or two
records, or change an amount field, but use a query to delete a number of
records based on criteria or to edit a description for a particular document
or batch. What's good for me - it's transportable AND overviewable (my Word
says the word overviewable does not exist, sorry for all native speakers out
there) - It does not happen that I change a business view connected to an
application and OW forever stays requiring that old business view to be
present.

On the other hand, for a RECURRING task - I would be reluctant to use Access
and would prefer a more native solution (sometimes as native as creating a
native C application talking directly to RDBMS, for performance reasons
) - not that I program it myself then. For recurring tasks you can
always plan, how long they are to take, and in most cases they are not time
critical. For recurring tasks you establish a project, evaluate all the
risks, prepare a specification...
For me, the main advantages of using native OneWorld means are two:
1. You can place security on them
2. They are (in theory) better suited for upgrades and you can use the same
maintenance techniques as for all other applications and custom mods.

The only reason I continued this thread is because I'm actually interested
what do other people do, when they need to do work behind the covers of
OneWorld, for example, to correct frequent corruption of database tables.
For example, I can't imagine how a site like Mark Siebenschuh's would deal
with database corruption after a failed Sales Update. Surely not in Access
(I hope).

Regards,
Vladimir Ponomarev

 

[Zoltan_Gyimesi]

Re: RE: RE: MS SQL file editing

Vladimir,

I really and totally accept your arguments, stand point and techniques.
I also use sometimes Access to manipulate/analyze data directly and our tech guys also use SQL queries frequently.
If somebody knows the possible traps and troubles and apply these techniques with caution then it is great.

I really didn't want to un-encouraged anybody to use other tools. My only goal was to suggest other ("native" )) methods too and to point to the possible risks when somebody use other tools without enough caution. I suppose, it was totally needless for most of you experts but maybe not for some beginner.

On the otherhand, as developer, I frequently create ad-hoc APPLs for temporary usage on a workstation only (under my Default project in XE) and simply delete them when they are already unnecessary.

Please, treat my previuos post as one amoung many.

Best regards,
... and read you again on other threads too.

Zoltán
//a developer//



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[dhelsley]

Load the SQL Server enterprise manager. It has QBE like capabilities and allows you to simply plug values into the returned grid.

As with other methods, it will not insure that your data is in the proper form for OneWorld. That's up to you.

Have fun.

Dave

David D. Helsley, Inc.
[email protected]
(859)466-6746

David D. Helsley, Inc.
Independent IT Consultant
[email protected]

 

[Michelle D.]

RE: RE: MS SQL file editing

It's quite a coincidence (for me) that this topic should come up for
discussion now because I'm currently faced with about 100 records missing
from the cardex and I'm trying to determine the best (and most efficient)
way to add them.

We have lots of inventory problems resulting from missing transactions
(transfers, adjustments, inventory issues) and I recently discovered that
for most of the missing cardex records, a record WAS written to the G/L.
So, using a combination of a (custom) integrity report (compares cardex to
item location file) and the records from the G/L, I can gather all of the
required information to add the missing cardex records.

As I mentioned, I have about 100 to add. Normally, I use DFU for any data
manipulation, but for me to add 100 cardex records manually .... not fun or
a good use of my time. Anyway, I imported a single record from the cardex
into Access so I could see the values in all of the fields and the
properties. I then imported a file (from a query) with all of the
information for my missing records. I had to fill in a few fields
(location, lot number, cost, terminal, unique key id). I then copied all of
the records to the table I had imported (with the single cardex record).

So now what I was going to do (in our CRP environment first) was to link to
the cardex and copy in my 100 new records. However, you guys have me
worried about NULLs and trailing spaces, etc.! Do you have any suggestions
for things to test after I add the records? (Zoltan and Vladimir, you
mentioned that you have experienced some problems ... could you give me
specifics to look for?)

As always, thanks in advance for any suggestions, comments ....

Michelle Dulay
ERP Coordinator
BFGoodrich Aerospace
Chandler Evans Control Systems
email: [email protected]

OneWorld B7322 with the MFG Mini-Cum, coexistent with World A7.3
AS400 V4R3 Enterprise Server
Windows NT SQL V6.5 Deployment Server
SynQuest V5.07.02
Oracle
NT Logic - between AS400 (OneWorld) and HP9000 (SynQuest)

 

[Guest]

RE: RE: MS SQL file editing

Personally I use the SQL Enterprise Manager and go directly to the data
table and munipulate or add records. If it is a lot of data I would get one
of our DBA's to create an SQL script to upload changes or additions.

Crawford

 

[vap]

RE: RE: MS SQL file editing

Michelle,

First thing - if you have the data from OneWorld (and you have not edited it
manually) - you are fine. For the data, which you have input yourself (or
edited), you MUST (can't stress this enough) format it according to OneWorld
rules when you insert it. For me, the easiest way to do this was to create a
very simple VBA function (which takes 4 parameters: value, type of field,
length and if it left justified or not (nasty MCU)) and use it.

Second thing - I would not recommend pasting 100 records to a linked table
in Access. Well, not to one of your main production tables. Never saw
anything bad happen, but this such things you are never too careful. I would
write a simple query, which takes the data from your table and inserts it.
It's a very good place for custom VBA function I've described in first step.
/* Personally, I would have used it in every field, because it takes seconds
and may save hours. But for me it may be a little easier, because I can
output table layout to an Excel file, list all the fields that I map and
then I just create an SQL script based on Excel sheet. I can't give you my
Excel (explanation how to use it would take a couple of pages) and can't
recommend preparing your own - you'll find it's easier to type 100 records
in DFU.*/

Third thing - after you have imported the data - check to see if you can
actually see it in OW. First in interactive application, then there is an
integrity report between F4111 and G/L. As last check I would write a small
VBA program, which can go thru all the records you have imported and check
that there are no NULLs in any of the fields. But this is just a
precaution - if OneWorld sees your data, it is highly unlikely that anything
bad will happen.

A different method, which can save you most if not all of the trouble (came
to my mind when I was typing all this, which means I have never tested it
myself):
Create a custom headerless application, with grid consisting of ALL records
in F4111. Open this application, do a paste from your table, click OK. IF it
works, it'll save you a lot of messing with SQL and VBA, because OneWorld
itself will format the data.

Disclaimer: All of the above is result of much work, experimentation and
trial and error. My personal trial and error, which means that it maybe
different at your particular site, configuration or OS level. Use this
information AS-IS and I'm not responsible for anything you do with your
database, including but not limited to drop table PRODDTA.F0911; commit;

Regards,
Vladimir Ponomarev
B733, B7331, B7332, XE (mostly XE at the moment); SQL 7.0, Oracle 8.1.5,
DB/400 (mostly SQL); Wintel, HP, AS/400 (mostly Wintel)

 

[brother_of_karamazov]

You can do most anything you want with Enterprise
Mangler.

Jeff

--- Jack_Crouch <[email protected]> wrote:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OW&Number=9298


__________________________________________________

 

[Jack_Crouch]

Thanks everyone. Enterprise Manager looks pretty good for what I was
after.

Ye - ha. Keep them updates going, going, going, going raw hide
.



AS400 V4R4, B733.2, SP11.3, NT-SQL7 for CO