Emailing 1

[lilboi]

Hi guys,

I need your opinion on a design. What I need to do is make a program, lets call it Emailer, that will take the messages found in a tblMsgOutbound in an sql and send it out as emails.

There will be programs from the client side that will be placing these messages into tblMsgOutbound.

The Emailer will be a vb program that will constantly check this tblMsgOutbound and take the new ones, send it out and place a copy into another table for history record.

Now I understand that it's not good for the Emailer program to check that tblMsgOutbound every 5 seconds cuz it'll constantly do a lock on that table. What would be the best approach for this?

I have thought of making an insert trigger where tblMsgOutbound will put the message into a text file and the Emailer program will be scanning that directory for any text files. Except, I'm having troubles getting the SQL to do that trigger.

Any other efficient way of doing this?

Thanks y'all!!

 

[dilettante]

This is what MSMQ is for.

 

[lilboi]

awesome! I'll look into it. Thanks!

 

[lilboi]

dilettante: I got the MSMQ working. I got SQL to insert messages into MSMQ which are both in the same server. The only problem now is, I want SQL to insert this into our emailing server(different computer).

So what I did was install MSMQ in the emailing server, created a queue as well.

Thing is, how do i set up the domain name or computer name so that the comp with the SQL server can place it in the MSMQ of the emailing server?

I've tried [local ip]\private$\msgout and [computer name]\private$\msgout.

Both do not work. SQL server comp is setup with a domain. The emailing server comp was on a workgroup. I then changed it to a domain, different form that of SQL server comp. Am I doing something wrong here?

When I try to do a "ping [email server name]", it won't work.

Any ideas on how I can set this up right?

 

[dilettante]

Are you using "direct format names" like:

DIRECT=TCP:157.18.3.1\PRIVATE$\MyQueue

 

[lilboi]

dilettante: that just worked like a charm!

I had mine as DIRECT=OS:[computer name]\PRIVATE$\MyQueue

I didn't know u could do TCP. Thank you so much!

 

[dilettante]

You're quite welcome. I'm glad you got this resolved, I've been frustrated trying to assist others with MSMQ remotely like this and I was really hoping you could work it out.

I hate to give anyone bad advice.

If only Microsoft made the MSMQ documentation easier to find and navigate on MSDN, I think more people would have success with it then. There are many small details to work out but basic use of the facility is pretty easy once you follow the right trail of breadcrumbs.

 

[lilboi]

it's also thanks to the MSMQ dll program that some1 made for SQL. That saved me a lot of time.

But thanks so much for putting me in the right direction and fixing the problem. hehe