Messages sent to SQL Database

[Mark McDaid]

Looking for any advice, suggestions, or recommendations on this one.  We have a thread that uses a tcl proc to write messages to a SQL Database located on another server.  Right now it’s setup where the messages are routed to a thread that is file protocol NUL, so the messages just disappear, and on the route there is a Raw Route Detail TPS proc that has the code that writes the messages to the SQL Database.  Now, I’ve only been here a few months, and this was already in place, but my question is, if the server with the SQL Database goes down, the messages will be lost, correct?  The messages would not be kept in the recovery db because the messages are successfully sent to file: NUL, but the tcl proc would be unable to write the messages to the SQL database.  I hope this makes sense.  Is there a better way to handle this?  Anyone have any experience with this type of situation?

Topic

[John Hamilton]

I have had several times when I send thing too a SQL server.

Where ever you do that you need to within your proc keep track of things.

I would have never put this where I think you are describing it.

But everyone will have there way to do things.

I would hope who ever set that up would have thougth about message recovery.  But my guess is you would not be asking about this unless you had seen a problem.

I don’t know that there is a quick easy answer too your question.  Or if there is it is beyond what I can see based on your email.

So not very helpful.

I would at least dump the message too the error database on falure of the tcl procs and shut the inbound thread.  But each person will handle it there on way.

[Mark McDaid]

John,

I appreciate your response.  I’ve been doing some searching through old posts and have a few ideas now as to how to proceed.  I probably should have done that before I posted.  😳 I’m going to do some testing and figure out which setup works best.  As far as I can tell, however, the way it’s currently setup does not handle message recovery if the SQL server goes down, so I’m kind of in a hurry to get this fixed.  Thanks again.

[Mark Perschbacher]

Just a thought for the short term at least, you could turn on SMAT for the inbound messages.  At least you would have some record.

[Mark McDaid]

I thought about doing that, but decided I didn’t want another SMAT file to have to deal with.  I’ve put code in to write the message to the error database if it can’t connect to the SQL database.  That way I can just resend any messages that are in the error database.

[David Barr]

In your searches through the forum, hopefully you found this topic:

https://usspvlclovertch2.infor.com/viewtopic.php?t=2082&highlight=recover33

It should explain how to fix your problem.  Let me know if you have any questions.

[Mark McDaid]

Hadn’t seen that one.  Thanks for posting………more good ideas to try!

[Author]

Replies