Sending E-mail from SQL Server 200X

Sending E-mail from SQL Server 200X

ending email from SQL Server can be a useful way to keep yourself or the system’s administrator informed of any errors and alerts, but depending on your version of SQL Server, the setup can be quite involved or relatively simple. With SQL Server 2000, the setup requires a heavy email client, such as Microsoft Outlook, or a rather convoluted stored procedure. SQL Server 2005 vastly improved the process by adding email functionality to Management Studio. (The feature remains in SQL Server 2008.)

This article explains the processes for sending email from SQL Server 2000 and from SQL Server 2005/2008.

Send Email from SQL Server 2000

To send email from SQL Server 2000, you can use Outlook (or another email client) or Blat, a command-line executable SMTP email client that allows you to forego Outlook and a POP3 account (if you’re not using Exchange). Blat is flexible enough to handle attached reports and formatting, and it’s free.

To use the Blat option, you would wrap the call to the command line in a function or stored procedure. For instance, you could use the following statement to call the stored procedure in Listing 1:

Exec stp_BlatMail 'ServerName', '[email protected]',  'System Maintenance: Low Memory', 'D:DataTempFilesMemoryRpt.txt'

The stored procedure in Listing 1 contains simple error handling to prevent Blat from wasting CPU cycles. For example, if an @To variable is not populated, the process aborts. Although this particular code doesn’t validate the email address, you could easily add code to do so. The @cmd variable contains the constructs of the command-line utility, as shown in Table 1.

Table 1. The Constructs of the Command-Line Utility
Construct Description
The body of the message is at the end of the parameter.
-t To email address
-sender A newer syntax that works well for authenticated connections
-from From email address
-f Display name for your email address
-org Organization name; a great place to identify the name of your company
-x Email header information
-noh Prevents your header information from displaying Blat’s homepage
-noh2 Prevents a header entirely
-server Email server IP or name
-port Typical SMTP port is 25.
-u User ID
-pw Password
-body Your email message; for small reports, you could use this part instead of an attachment.

If you have no attachment, just leave that field empty and the procedure will ignore the construct in the command line.

Author’s Note: Running this process involves using xp_cmdshell. That means your average users can’t execute it. Consider queuing messages in a table and then having an alert job run against the table. Another method is to set up a SQL Server proxy agent user account that allows users to execute xp_cmdshell. Be careful with this method because it could compromise your server’s security.

Send Email from SQL Server 2005/2008

You don’t need a complex stored procedure in SQL Server 2005 or SQL Server 2008. You can set up most of the process in Management Studio as follows:

  1. In Management Studio, expand the Management folder and right-click Database Mail.
  2. Choose Configure Database Mail to launch the Database Mail Configuration Wizard and click Next.
  3. If the Database Mail service isn’t running, the wizard will display the message in Figure 1. Click Yes. If the service is running, continue to Step 4.
  4. At this point, the wizard helps you create a new profile, as shown in Figure 2. You can add as many SMTP accounts as you need. Try to give each account a meaningful name and a comprehensive description.

    Figure 1. Enable Database Mail Service: The Database Mail service isn’t running.
    ?
    Figure 2. Specify a New Email Profile: The wizard helps you create a new profile.

  5. Click Add to display the New Database Mail Account dialog box shown in Figure 3. Enter the account’s email address, display name, and a ‘reply to’ email address. Usually, the two email accounts will be the same, but it isn’t necessary to use the same account.
    Figure 3. Specify Your Connection Details: Here is the New Database Mail Account dialog box.
  6. Check the This Server Requires a Secure Connection (SSL) option if your email server uses SSL security. Most often, Windows Authentication is the way to go when choosing security. It’s the least complicated route. If you use the local system account, you must know the user name and password. Avoid Anonymous unless you know what you’re doing.
  7. After setting all the options, click OK to return to the New Profile dialog box. It should display your new account, as shown in Figure 4. Continue adding accounts as necessary.
  8. When you’re ready to continue, click Next. The default settings are adequate (see Figure 5), but you may consult with your email administrator to get the right settings. Click Next and then Finish.

    Figure 4. Add Accounts as Needed: The New Profile dialog box displays your new account.
    ?
    Figure 5. Changing the Default Settings: Change the necessary settings as determined by your environment.

Now that you have a profile up and running, you can easily access the built-in email functionality using the following code:

@squery = 'SELECT * from sysfiles'EXEC msdb.dbo.sp_send_dbmail    @profile_name = 'DBMail',    @recipients = '[email protected]',    @query = @squery,    @subject = @message,    @attach_query_result_as_file = 1 ;

When you call the stored procedure, pass the appropriate Transact-SQL statement or use a default, as shown. Be sure to update the parameter text to suit your needs. Notice that @recipients appears to reference a list. That’s an easy way to send the same information to a group. Set @attach_query_result_as_file = 1 to have your results sent as an attachment, and set @attach_query_result_as_file = 0 if you are sure that the results are not too long.

Mail’s Up!

You now have a valid method for sending email in SQL Server 2000?for those legacy systems?and a clear method for configuring those SQL Server 2005 and SQL Server 2008 machines to send out mail by the dozens.

devx-admin

devx-admin

Share the Post:
Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security.

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists

AI Tool

Unleashing AI Power with Microsoft 365 Copilot

Microsoft has recently unveiled the initial list of Australian clients who will benefit from Microsoft 365 (M365) Copilot through the exclusive invitation-only global Early Access Program. Prominent organizations participating in

Microsoft Egnyte Collaboration

Microsoft and Egnyte Collaboration

Microsoft has revealed a collaboration with Egnyte, a prominent platform for content cooperation and governance, with the goal of improving real-time collaboration features within Microsoft 365 and Microsoft Teams. This

Best Laptops

Top Programming Laptops of 2023

In 2023, many developers prioritize finding the best laptop for programming, whether at home, in the workplace, or on the go. A high-performing, portable, and user-friendly laptop could significantly influence

Renaissance Gaming Magic

AI Unleashes A Gaming Renaissance

In recent times, artificial intelligence has achieved remarkable progress, with resources like ChatGPT becoming more sophisticated and readily available. Pietro Schirano, the design lead at Brex, has explored the capabilities

New Apple Watch

The New Apple Watch Ultra 2 is Awesome

Apple is making waves in the smartwatch market with the introduction of the highly anticipated Apple Watch Ultra 2. This revolutionary device promises exceptional performance, robust design, and a myriad

Truth Unveiling

Unveiling Truths in Bowen’s SMR Controversy

Tony Wood from the Grattan Institute has voiced his concerns over Climate and Energy Minister Chris Bowen’s critique of the Coalition’s support for small modular nuclear reactors (SMRs). Wood points

Avoiding Crisis

Racing to Defy Looming Financial Crisis

Chinese property developer Country Garden is facing a liquidity challenge as it approaches a deadline to pay $15 million in interest associated with an offshore bond. With a 30-day grace

Open-Source Development

Open-Source Software Development is King

The increasingly digital world has led to the emergence of open-source software as a critical factor in modern software development, with more than 70% of the infrastructure, products, and services

Home Savings

Sensational Savings on Smart Home Security

For a limited time only, Amazon is offering massive discounts on a variety of intelligent home devices, including products from its Ring security range. Running until October 2 or while