Tell me this is a BAD idea 4

[Sheco]

We've got an application which generates letters to clients.

I've got a new requirement to "catch" any HIPAA letters going to a non-USA mailing addresses.

The standard HIPAA letter is stored in RTF format on a network share, but this letter may be modified at any time.

I have a SQLServer 2000 stored procedure that inserts/updates letters.

I was thinking that, within the stored procedure, I could attempt to compare the letter body with this standard HIPAA file by maybe using sp_OACreate to create an instance of the FileSystemObject COM object and using it to read the standard letter into a stored procedure variable.

Then I was thinking this would be a terrible idea.

Performance is probably not a big concern since we are talking maybe 1000 letters per day. Permissions are probably not a concern since users access SQLServer with their AD permissions and all also access to the standard HIPAA letter.

Thoughts?

A GoogleFight:
sp_OACreate bad idea yielded 272 hits.
sp_OACreate good idea yielded 799 hits.

 

[mrdenny]

I think that more information needs to be provided. I'm not clear on why you have the SQL Server reading the files.

Although I do generally like to stay away from the sp_OA procedures.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Sheco]

Basically the users want me to intercept any INSERTs or UPDATEs that are LIKE the %<contents>% of a specific text file... a file which they may update at any time.

I suggested keeping the master text in a table and providing a web interface for users to edit the text but this idea was shot down. They want to keep using the file sitting out there on the file system.

 

[RiverGuy]

Without knowing any more specifics, I say code the validation into the application before it hits the database.

 

[Sheco]

I agree the application seems a better place for opening external files. The negative to this approach is that rolling out a new version of the EXE to all the desktops is a much more involved process... compared to changing a stored procedure on the db.

 

[mrdenny]

I'd have to agree. Modifing the EXE is the way to go.

It may be a more involved process, but I'd have to say that it's the better process.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ESquared]

Let me get this straight. You want an external system (in this case SQL Server) to perfectly police a document that is freely editable by anyone?

You may as well as your SQL Server to check people's badges at the door. You might be able to come up with a system that does a reasonably good job, but that's not really the best usage of such a thing.

Instead, make the letter something in the way of a mail merge document, with address and body data coming from the database. Then every time someone generates a letter going to a non-USA address, it's easy: your SP that provides the data will be able to examine the data on its way to the letter.

Trying to detect stuff that's in another domain entirely (such as an rtf) is goofy.

If you have to do this externally, write a standalone .exe file that lives on the server where the file is stored, that does the job. The program can still be data-driven (and in fact that's best) by reading its search criteria from the database, but everyone needs to know that the detection will only be as good as the human-chosen search criteria and no better.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[Sheco]

Not exactly.

We only release new EXEs every six months so users must wait a long while for new features unless it can be done entirely in the database.

The question posed to me was "Is it possible" to catch certain the letters in the database before they go off to the mail room to be printed by a separate mail-merge type app. As many of you have probably noticed, savy users are keen to express their desires in terms of "Is it possible?" because the answer is more likely to be "yes." Strictly speaking, this supposition is possible.... although it may also be unwise... even goofy!

The idea of using another external app to remove letter rows after they are created, but before they are printed, wouldn't really work because an error needs to be raised immediately in the application that created the letter.

But this idea gives me another goofy idea:
1. An RTF is just a text file with markup so copy the current letter contents into a new table.
2. Build a windows service that polls for changes to the RTF, updating the table if the file is modified. This could also be a VBS triggered by windows scheduler or even a DTS job. (I forget the new name of DTS)

This would give the users basically what they want except with a polling delay between the time the file is changes and the table is updated. It is still freaking goofy!

I can't feel good about going back to them with "Sorry, it is just too goofy, you'll need to wait for the next EXE." I need to figure out some good words for how to express "goofy" without saying it.

 

[ESquared]

not best practice.
not reliable.
unable to guarantee compliance with the law.

 

[Sheco]

unable to guarantee compliance with the law

Boom! Sold!

Thanks!

 

[chrissie1]

bad idea!!

Christiaan Baes
Belgium

My Blog

 

[agent0047]

Noncompliance with the law as I read this post is the easy sale to departmant heads to change to a more stable setup to handle what your wanting. Hipaa (Health Insurance Pobiblity and Accountabilties Act) has strict gidelines concerning data. Have a great week sorry I missed this one.