Hello -

I need to develop a process by which customers are notified that their 1) order was received and 2) order was shipped.

We are running Sql Server 2008 and all customers and data are located here on various servers.  It is in a Windows 7 environment. 

Has anyone ever utilized Sql Server Database Mail for this purpose?  Is it feasible?

Any information or links to information will be greatly appreciated!

Juniormint

P.S.  Anyone know how Amazon accomplishes this task?

You could use SQL Server Database Mail for this purpose. However, I would think that there are better ways to implement this. Preferably through the UI and not on the database backend.

The reason being is that you really don't want to turn your database engine into some type of mailing application.  SQL Server Database Mail is pretty basic.  I generally use it for failed job notifications or other types of administrative alerts.  SQL Server Database Mail also keeps a log table in MSDB (I believe).  If you are wanting to confirm client transactions using SQL Server Database Mail, that log may get out of hand.

Alternatively, you can always have a scheduled job that runs every so often that sends out the notifications.  That can be done through SSIS, some custom application, or 3rd party application.

• Edited by J I M B O 12 hours 46 minutes ago

Hello Jimbo -

We don't have SSIS, unfortunately.  The only tool we have other than SQL Server is Visual Studio 2013, and I don't want to have users have to go to another application when the database knows full well when an order has shipped! 

Also, you mentioned a 3rd party application.  Do you have any in particular you could suggest?

Thanks.

Juniormint

If you have SQL Server, you get SSIS, SSAS, and SSRS for free. In this type of situation, I generally have a custom package and/or executable to do this. It is pretty simple unless you are doing some amazing stuff in your email.

Since you have SQL Server and Visual Studio, you can create a SSIS package and use the Send Mail Task.  Alternatively, if you don't want to use SSIS, you can create a .NET executable (i.e. SMTPClient library) that reads all your data and sends the emails.  Both can be scheduled through the SQL Server Job Agent.

Other than that, I am sure a ton of solutions will show up on a Google search about scripting an email task.

You could use SQL Server Database Mail for this purpose. However, I would think that there are better ways to implement this. Preferably through the UI and not on the database backend.

The reason being is that you really don't want to turn your database engine into some type of mailing application.  SQL Server Database Mail is pretty basic.  I generally use it for failed job notifications or other types of administrative alerts.  SQL Server Database Mail also keeps a log table in MSDB (I believe).  If you are wanting to confirm client transactions using SQL Server Database Mail, that log may get out of hand.

Alternatively, you can always have a scheduled job that runs every so often that sends out the notifications.  That can be done through SSIS, some custom application, or 3rd party application.

• Edited by J I M B O Friday, July 17, 2015 6:21 PM

You could use SQL Server Database Mail for this purpose. However, I would think that there are better ways to implement this. Preferably through the UI and not on the database backend.

The reason being is that you really don't want to turn your database engine into some type of mailing application.  SQL Server Database Mail is pretty basic.  I generally use it for failed job notifications or other types of administrative alerts.  SQL Server Database Mail also keeps a log table in MSDB (I believe).  If you are wanting to confirm client transactions using SQL Server Database Mail, that log may get out of hand.

Alternatively, you can always have a scheduled job that runs every so often that sends out the notifications.  That can be done through SSIS, some custom application, or 3rd party application.

• Edited by J I M B O Friday, July 17, 2015 6:21 PM

We do NOT have SSIS - just bare bones Sql Server 2008 period.  That rules out SSIS packages.

I, like you, originally thought there would be tons of solutions that would show up in a Google search. Unfortunately, that is not the case; at least using the search words I have used.

Are you aware of any links to articles that would relate to this?

I just need a couple examples to follow.

Juniormint

Well, let me state it another way.  If you have a license for SQL Server 2008 Standard or higher, then you have SSIS, SSAS, and SSRS available to you.  However, your administrator may not have installed those features.  Either way, if it isn't available for you to use, then the quickest way would be to just create a .NET executable to perform the task for you.

The link below is the SMTPClient library available in the .NET framework.  You can code it up assuming that you have the .NET projects installed as part of your Visual Studio installation.  You will need to search on how to connect to SQL Server and traverse data sets, but it is pretty simple.

https://msdn.microsoft.com/en-us/library/system.net.mail.smtpclient%28v=vs.110%29.aspx