automated email

supmktg · 06-11-2019, 07:02 PM

I have a Access app that uses an Azure SQL Server database on Sharepoint. I need to send an email at the end of each workday that informs mgmt of orders rec'd but not yet processed by the end of the day.

I began designing the function in Access, but if no user is logged in at the designated time that is end of day the email will not get sent. I believe that this functionality is available in SQL Server, but I am not sure where to begin.

Can someone point me in the right direction?

Thanks,
Sup

June7 · 06-11-2019, 07:20 PM

Read a suggestion elsewhere to automate SQLServer: "write a manual ETL script to move the data, then put that script in a SQL Agent job and schedule it to run as needed". I have no idea what ETL and Agent job are nor how to schedule. But maybe you could do some research on those items.

pbaldy · 06-11-2019, 09:04 PM

I haven't used ETL, but I have a number of automated emails sent out from SQL Server. Your requirement is a little vague, but you'll probably end up with a stored procedure run from SQL Server Agent, using Database Mail to send the email. If you need to loop records to put data in the body, a cursor can be used.

supmktg · 06-12-2019, 04:54 AM

My knowledge of SQL Server is pretty much limited to the database tables, views and stored procedures. In this case, I am connecting an Access front end and some asp.net web forms to a client's Azure database that is administered by an outside Sharepoint consultant. I am given limited SQL Server permissions, and it is likely that my permissions do not include SQL Server Agent. I do not see SQL Server Agent in Management Studio (v17.9.1, NOT Express),

Before I get into a tug of war with the Sharepoint consultant, are there other possibilities to send an automated email on a daily basis that do not include SQL Server Agent?

Thanks,
Sup

pbaldy · 06-12-2019, 07:24 AM

I don't know if it's a permissions thing or an Azure thing, as I haven't used Azure. Can the PC be locked instead of logged out of? I can't recall if the Access db will run at all if the PC is not logged in. If it will, you could use CDO to send the email, which I don't think will require a user to be logged in like Outlook automation would. I guess I'm lucky because I've always been able to lock a PC rather than log out of it and all my automated stuff runs fine.

supmktg · 06-13-2019, 01:03 PM

I've discovered that Azure SQL no longer includes SQL Agent. For others that happen upon this post, my research found 2 alternative methods to send a scheduled email from Azure:
https://vpolizzi.wordpress.com/2010/...-on-sql-azure/
https://docs.microsoft.com/en-in/azu...-to-send-email

I may try this at a later date, but for now I will most likely have my Access app send the email on the first user shutdown after 4:30pm, and log it into a sent mail table so no further emails will be sent for that day, or send it upon the first login in the morning of the next day. It is not optimal, but it is my best choice for now.

Thanks to both of you!
Sup

If someone has a better idea, I'm all ears!

Gasman · 06-13-2019, 11:49 PM

Could you not use Task Scheduler and open the DB to run that particular code.?

pbaldy · 06-14-2019, 07:06 AM

Quote:

Originally Posted by Gasman

Could you not use Task Scheduler and open the DB to run that particular code.?

I think the key bit was "if no user is logged in at the designated time that is end of day the email will not get sent.".

Gasman · 06-14-2019, 07:09 AM

Sorry, I took that to be logged in to the DB, not the network.

pbaldy · 06-14-2019, 07:12 AM

I could be the one who misunderstood.

supmktg · 06-14-2019, 11:42 AM

For clarity's sake, I meant logged in to the db.

With all of your guidance and some additional research I have decided to build a separate app that includes the required linked sql tables and module that contains my send email via outlook vba function. From there I will use a windows scheduled task to open the db, run the vba code and close the db.

Hopefully, the client will follow my instructions and leave the windows machine on 24/7.

Again, Thanks for all of your guidance,
Sup

pbaldy · 06-14-2019, 12:40 PM

Quote:

Originally Posted by supmktg

For clarity's sake, I meant logged in to the db.

With all of your guidance and some additional research I have decided to build a separate app that includes the required linked sql tables and module that contains my send email via outlook vba function. From there I will use a windows scheduled task to open the db, run the vba code and close the db.

I misunderstood too. That's why I asked if the computer could remain locked instead of logged off. I have a bunch of automated imports/exports/emails running as you describe.

June7 · 06-14-2019, 12:26 PM

Guess I misunderstood original post and caused a bit of misdirection. I have a VBScript that runs VBA procedure in db. Windows TaskScheduler runs the script. But just opening db and its code closes itself should work just as well.

I no longer work in that office and if I remember correct, had to keep computer logged into NW for TaskScheduler to work. But maybe someone will confirm that.