Tips for Using Other SQL Server Features

As you develop your SQL Server application, you look for ways to take advantage of features and code that already exist, rather than reinventing these features yourself. Consider the following possibilities:

• If your application involves multiple SQL Servers, there is a good chance that some data movement between servers will be required. Use the built-in replication facilities of SQL Server to accomplish this data movement. Replication can be set up and monitored through SQL-DMO programming. Replication can play an especially important role in partitioned applications that are designed to scale by adding more SQL Servers.
  
  
• If your application needs to notify users when a particular change occurs in the database, check out the SQL Mail feature. SQL Mail provides a mechanism to send and receive e-mail directly from SQL Server. For example, when inventory falls below a certain threshold, a trigger can automatically run SQL Mail to send an e-mail message to the purchasing department. Conversely, you can use e-mail as the input vehicle for data that is destined for a SQL Server database. E-mail messages can be sent to a mailbox set up for SQL Server, and a SQL Mail task can check the mailbox regularly to automatically insert the data from the messages it finds and reply with a confirmation message.
  
  
• If your application must publish existing SQL Server data to the Internet, look at the SQL Server Web Assistant. The Web Assistant and its associated stored procedures can create and maintain Web pages containing results from SQL Server queries and links to other SQL Server data. These pages can be refreshed on a scheduled or triggered basis as needed, giving you complete control over the database load generated by Web access to your server.
  
  
• If your application needs to take advantage of functions that are available in Windows NT but not available in Transact–SQL, it makes sense to call these functions directly from SQL Server by using extended stored procedures or OLE Automation stored procedures. With extended stored procedures, you can create custom functions in a C DLL that can be executed from SQL Server just like any other stored procedure. These custom functions can in turn call any function available in Windows NT, pass parameters, and return data to SQL Server.
  
  
• OLE Automation stored procedures provide a mechanism for loading and calling methods on any OLE Automation object that exists on the server computer. Since so many tools are capable of creating OLE Automation objects, this capability opens up a wide range of possibilities for extending SQL Server's functionality. For example, Visual Basic includes many financial functions, such as Net Present Value, that are not offered in Transact–SQL. Visual Basic Enterprise Edition makes it easy to package these functions as methods on your own OLE Automation object. You can then call these methods from SQL Server stored procedures and triggers by using the OLE Automation stored procedure feature.