Hello, all. First, I'm not any kind of database administrator or other professional like many readers here. I work in an Information Technology Department as an Information Analyst. My duties primarily call on me to run existing reports pertaining to various information needs of my company.
Occasionally, my duties extend to consorting with our excellent Database Administrator on tasks in which we each have a role. Like most DBA's, Gary is exceptionally bright; not being professionally schooled or having relevant database experience myself, I always defer to his judgement about database issues and just about all other computer related issues as well.
Lately, however, I'm having doubt as to a few comments he made related to a problem we're having at work.
Essentially, the problem is this: Our company uses EPS--an eligibility procesing system to keep track of members from various groups that we maintain. Some of our groups require us to print membership cards for their members. Consequently, there is a table within one of the databases in which new member records, which are scheduled to have new cards produced, are placed.
On the user-frontend in the EPS application, there is an icon which, when selected, assembles all the members from the card production table (in the EPS Database?)and creates a .DAT file which is supposed to contain the data according to the sort proscriptions selected by the user from dropdown menus on the front-end. But, upon the creation of the .DAT file and when the sort flags on the user end are by last name, alphabetically, and by group, the sort order is sometimes not followed. That is, on the .DAT file there seems to be groups of names out of alphabetical order. In fact, Some groups are listed out of order and then names within that group are out of order as well.
Essentially, I'm wondering about the stored procedure in the EPS application that provides for the assembly of the members from the card production table, sorts them according to the frontend-user's specs, then creates a .DAT file based on the sort.
We use SQL 7.0 for our DB administration functions. We have noticed that the remedy for the sorting problem is to reset our Main server (the one on which the EPS application resides). Gary (Our DBA, in case you forgot)says that the problem is that RAM on the Main server is "lost" or "leaking" because of application usage thereon. He says that, consequently, this affects the sorting process in EPS and that, when the server is rebooted, the old memory is "flushed out" or replaced by "new memory." That's why, he contends, rebooting fixes the Sort issue.
I suspect a different conclusion. I suspect the code in the stored procedure for the assembly of the .dat file is the culprit. I thought that I read on Microsoft's homepage that, in SQL 7.0 it was difficult or even impossible to BCN(Bulk Copy. . .something)data into flat files AND keep the sorting in tact using the stored procedure from the previous version (6.5?). I believe that Gary is using the older "Sort" stored procedure (i.e., NOT the new code "workaround" recommended by Microsoft on their homepage for SQL 7.0). Gary indicates that he "BCN"s the member data to flat files and that the sorting is off, not as a coding erro, but because of the "memory leak" on the Main server.
Isn't there a new stored procedure (code) to use in SQL 7.0 when trying to Sort information into .DAT files?
I know that this is a crude e-mail and the problem will be difficult to solve. But anybody who has a solution and can get that to me by 12 P.M. EST tomorrow (Friday, December 21) I'll owe drinks to. I'd like to believe that GAry's right. He probably is, but I just would like to be sure. Any ideas? Thanks.
