Working with SQL
This is an intermediate level course suited to DBAs, system analysts, programmers, or anyone wanting to learn how to use SQL to solve business scenarios. It will help to have an understanding of Structured Query Language syntax and functions and regular expressions, or some experience with script-based programming languages. Our learning objectives for this course are to recognize and explain use cases where Structured Query Language, SQL, can be applied in business situations, explain and apply SQL procedures to import and parse text files, define and apply automation processes to parse and sort data to specific databases. We welcome all comments and feedback. So please feel free to reach out to us at email@example.com with any questions or comments.
- [Narrator] You will use the sp_send_dbmail procedure which can be found in the msdb database to send email notifications of the orders that are being created. As with any email we need recipients, a subject, body text for the email, the format of that body, which in our case would be HTML and the level of importance. To send an email from SQL Server you need to set a profile. The profile links to an email account that has been defined within SQL Server. Under the Management folder of your SQL Server instance, in Management Studio you will find the Database Mail icon. Clicking on the Database Mail will allow you to set up your mail profile. You need to setup an account where you define your SMTP server and any authentication you need like User Name and Password. You can define whether you need a secure connection and what port number that would be on. Once you have defined your email account then you must create a profile and assign that account to the profile. It is this profile name that you will use as the profile parameter in sp_send_dbmail. We will use the cursor to loop through our email dataset essentially building HTML body by hand. For each location and each customer you'll want to send an email with all the ATMs they need to replenish for this particular order. We will have a similar procedure for notifying users of errors and also notifying them of new default transactions. When you have a look at the completed procedure. This is our dataset and put into the temporary table and we're going to loop through the dataset constructing our email body of HTML. It has table, table headers and then we construct from our dataset the table body and we're also summing up the title as we go so we can put the title in the footer of the table. You then grab the email addresses from a table and then send the email with db send mail. It is fitting that the final operation we must perform to archive the original file is to use xp_cmdshell. We'll go back to this, to issue a move command, to move our file from the source directory to the archive directory we specify it with a /y parameter to overwrite the file if it exists and that concludes our processing of one file.
About the Author
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.