Scripting SQL: VBScript Interfaces in SQL Server 2000 Let You Transform Data and Provide Reports to Your Users

Article
10/23/2019

MSDN Magazine

Scripting SQL

VBScript Interfaces in SQL Server 2000 Let You Transform Data and Provide Reports to Your Users

Alok Mehta and Daniel Williams

This article assumes you're familiar with SQL Server 2000 and VBScript

Level of Difficulty 1 2 3

Download the code for this article: VBScriptAndSQLServer2000.exe (38 KB)

SUMMARY Application service providers often must send information to clients automatically rather than on-demand. For example, a manufacturer may want to know each day how many of their products were sold by a retail chain. While SQL Server is ideal for maintaining this type of database, you have to write scripts to get the data out in a client-friendly format. Here you will see how you can use Data Transformation Services (DTS), a powerful tool in SQL Server, to automate the retrieval and formatting of data from SQL Server 2000 and make the process of pushing data to your users a lot easier.

ush, also known as Web-casting, automates the search and retrieval of data. It is initiated by Web or database servers rather than users. Based on some defined criteria, a push application will automatically search a database and deliver the desired information when and where the application directs (frequently straight to a user's desktop via e-mail). This is not only a convenient means of receiving important information that a user may not otherwise get easily, it's substantially changing the way in which day-to-day business is conducted.
      Push applications generally come in two forms. One type, which is often offered by large news and database groups, includes e-mail, listserves, and direct delivery services. Push applications can also enable developers and users to create profiles and record their preferences so they can receive relevant information from multiple sources. The sample application we'll describe here is based upon the first type of push application. However, our application is generic enough to support a custom profiling functionality as well.
      Organizations that need to centralize and consolidate data can use Data Transformation Services (DTS) tools in SQL Server™ 2000 to retrieve and transform data from different sources into a channeled destination. Using these tools, you can perform a simple one-time data transfer or develop complex workflow-driven packages. The DTS tools also provide a graphical user interface and an object model that can be programmed relatively easily.
      A DTS package is a combination of connections, tasks, transformations, and workflow constraints. Each package can contain one or more steps or tasks that can be executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version. You can easily create a package using the DTS Designer tool that we will describe later in this article.
      With VBScript or JScript® you can create a task that performs functions that are not available in the other tasks in DTS. For example, you can:

Create and use ADO connections, commands, recordsets, and other objects to access and manipulate data
Format and transform the data using functions, subroutines, and COM objects
Create, use, and modify the values stored in DTS global variables and ActiveX® script constants
Integrate other DTS tasks and workflows

Sample Application

Our sample app will show you how to use a VBScript task to perform functions that are not available in the other DTS tasks and how VBScript scripts can function as scheduled tasks. We will not discuss DTS global variables, workflows, or integration with other tasks because our purpose is to show the power of VBScript within SQL Server 2000.
Figure 1 outlines the steps we followed to develop our sample DTS application. The VBScript task in the DTS package e-mails periodic information to the authors regarding sales of their books. This data is formatted into user-friendly reports. We are using the Pubs database from SQL Server 2000 as our data source, but the technique is generic and can be applied to any data model.

Figure 1 Workflow

Our first step was to populate the e-mail field with the author's e-mail address. To do this, we selected the Pubs database and extended the authors table by adding a varChar field of size 20, naming it au_email, as you can see in Figure 2. We assume that a separate GUI or process would be responsible for populating this field in a production application, but for demonstration purposes you can enter these addresses manually.

Figure 2 Adding an E-mail Field

Next we created a new local DTS package in SQL Server 2000. To open the DTS Designer to create the package, you need to start Enterprise Manager in SQL Server, right-click on Data Transformation Services in the console tree, and choose New Package, as shown in Figure 3. With the DTS Designer GUI you can build and configure packages by dragging and dropping methods and by completing property sheets on the various DTS objects composing the package.

Figure 3 Create New Package

Our third step was to add a VBScript task by dragging and dropping the ActiveX Script Task from the Task toolbar to the design surface, as shown in Figure 4. You can change the name of a task from "ActiveX Script Task: undefined" to whatever is appropriate for your application.

Figure 4 ActiveX Task Properties

      Our next step was to develop functions within the VBScript task to retrieve, transform, and e-mail the data to the users. To make it easy to explore our sample application, just copy the functions from our sample code (see the link at the top of this article). Within the ActiveX Script Task properties window, make sure that the language is set to VBScript Language. You can set the language by clicking on the Language Button (see Figure 4). Then copy our source code into the designer, deleting the default main function code you see in the designer window.
      You will need to change constants in SQL Server to make the sample application work for you since these constants will vary (explained later).
      Make sure you save the task when you're finished, as shown in Figure 5. We named our package BookSales.

Figure 5 Saving the Package

You can run the sample application in several ways. First, click on the Parse button to make sure that you do not have any syntax errors. This button is available within the ActiveX Script Task properties window. Then click on the Go button in the toolbar at the top of the DTS Designer. Alternatively, you can execute the task by right-clicking on whichever task you choose and selecting Execute step from the context menu. Or you can execute the BookSales package as shown in Figure 6 within the Enterprise Manager. Upon successful completion of the task, the e-mail (see Figure 7) will be sent to the authors.

Figure 6 Run BookSales

Our BookSales package can automatically be scheduled to run by selecting the Schedule Package menu item shown in Figure 6. Enter the necessary frequency and duration parameters.

Figure 7 E-mail Scheduling

To report order number, quantity, pay terms, and title, the BookSales package will run daily at 11:00 pm starting from 1/1/2002. Note that SQL Server Agent must be loaded and configured to schedule DTS packages. You should see the BookSales package as a job within the Enterprise Manager under Management | SQL Server Agent | Jobs.

The VBScript Code

All our sample code is in the sourcecode.txt file available for download with this article. After we cover details on the constants that need to be set and quickly look at the main function, we'll explain how the code manages the sales data, accesses the database, formats the recordset, and sends reports.
The following three constants must be adjusted according to your system and network settings:

  Const SMTP_SERVER = "exchange.afs-link.com"
  

  Const SENDER_E-MAIL = """Book Sales Reporting Service""" & _
  

  "<amehta@afs- link.com>"
  

  Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data " & _
  

  "Source=(local);Initial Catalog=Pubs;user id = 'sa';password=''"

The first constant, Const SMTP_SERVER = "exchange.afs-link.com," is the SMTP server, the DNS name of your mail server. The e-mail sender function needs this information to send out the e-mail. Your network administrator will know this setting.
Const SENDER_E-MAIL = ""Book Sales Reporting Service" <amehta@afs-link.com>" is the e-mail address of the sender. It usually is something similar to system@domainname.com. The first part of this constant (Book Sales Reporting) allows you to type in any text; typically this will be the name of the department sending the daily sales report. The actual e-mail address must be entered within the <>. Again, your network administrator should be able to tell you what to enter here.
The last constant is the database connection string that provides all the necessary information for connecting to the database. The ADO connection string looks like this:

     Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data " & _
  

     "Source=(local); Initial Catalog=Pubs;user id = " & _
  

     "'sa';password=''"

We assume that readers have a basic knowledge of ADO, so we have included only a brief summary of what each part of the connection string means.
      The database driver section of the string is the part that reads "Provider=SQLOLEDB.1;". This defines the type of ODBC driver needed to connect to the database. We are using SQL Server; if that is not the case for your application, you can find the necessary ODBC driver information at Microsoft Open Database Connectivity.
      The Data Source specifies the location of the server you are searching for. We have specified "local" because SQL Server is on our local machine. You may need to specify an IP address or the name of the machine on which the database resides.
      Initial Catalog contains the name of the database; in our code, this is the Pubs database that comes with SQL Server. And finally, user id specifies the name of the user and password for the user.
      By default, the entry function to the ActiveX task is named Main, but you can change it. The Main function consists of only two lines. First it calls the Process_Daily_Sales subroutine, and then it returns an ActiveX script constant (DTSTaskExecResult_Success). In the Package Object Browser shown in Figure 8, you can view all of the project constants and global variables.

Figure 8 Project Constants

These constants and global variables can be used to control execution of steps within a DTS package. For the sake of simplicity, we will not describe these constants and global variables in great detail. It is important that the Main function returns the ActiveX script constant as DTSTaskExecResult_Success because our example is just a one-step VBScript task.

Managing the Sales Data

The Process_Daily_Sales function is the guts of the application. It retrieves in recordset format the list of authors whose books have been sold today. Then it formats the recordset into an HTML table, and finally it takes this HTML table and e-mails it to each of the appropriate authors.
This function has three local variables: two ADO recordsets and one date:

  Dim rstAuthors
  

  Dim rstSales
  

  Dim Todays_Date

Since not all Pubs databases are populated with sales data for today, the following two lines of code generate the report based upon 9/14/1994, a date we chose that will work with almost all Pubs databases. We simply uncomment the line of code to be processed and comment the other line:

  'Todays_Date = "'" & Date() & "'"
  

  Todays_Date = "'9/14/1994'"

Next, we get the authors with sales. By using simple SQL joins, we select authors whose books have been ordered today:

  strAu_Sales = "Select Distinct Authors.* from " & _
  

  "Authors,Sales, TitleAuthor Where " & _
  

  "TitleAuthor.au_id = Authors.au_id   and " & _
  

  "TitleAuthor.Title_id = Sales.Title_id   and " & _
  

  "Sales.ord_date = " & Todays_Date

Now we convert the SQL statement into an ADO recordset with the following call:

  Set rstAuthors = ExecuteSQL(strAu_Sales)

Next, we make sure that rstAuthors is in fact not empty before we iterate through to send notifications to the authors:

  If Not (rstAuthors.Eof and rstAuthors.Bof ) Then
  

     While Not rstAuthors.Eof

We extract other information, such as the store where the order was placed, order number, quantity, payment terms, and the title of the books from various tables such as Stores, Sales, TitleAuthors, and Titles. This is a simple join, as you can see:

  strAu_Sales = "SELECT distinct stores.stor_name as [Store Name], " & _
  

  "sales.ord_num as [Order Number], sales.qty as [Quantity], " & _
  

  "sales.payterms as [Pay Terms], Titles.Title FROM Stores, Sales, " & _
  

  "TitleAuthor, Titles " & "Where TitleAuthor.au_id = '" & _
  

  "rstAuthors("au_id")  &  "' and Sales.ord_date = " & Todays_Date " & _
  

  " & "and Sales.Title_id = Titles.Title_id  and sales.stor_id = " & _
  

  "stores.stor_id "

We convert the SQL statement into the ADO recordset by calling the ExecuteSQL statement:

    Set rstSales = ExecuteSQL(strAu_Sales )

Now we construct the body of the message with the author's name and address. The HTML tag <br> is used because we will embed the message as HTML in the e-mail:

  strTable = rstAuthors("au_fname") & " " &
  

  rstAuthors("au_lname") & "<br>" & rstAuthors("Address")
  

  & "<br>" & rstAuthors("city") & ", " &
  

  rstAuthors("state") & " " & rstAuthors("Zip")

Now that the body of the message is ready, we extend this body with an embedded HTML table by including the sales information previously extracted. FormatRecordset will convert the rstSales ADO recordset into an HTML table:

  strTable = strTable & FormatRecordset(rstSales)

Then we call the send_e-mail function that accepts the subject, the author's e-mail address, and the HTML message body:

  Call send_e-mail("Book Sales Report For: " & _
  

  & Todays_Date, rstAuthors("au_email"), strTable)

We then move on to the next author:

  rstAuthors.movenext
  

  Wend

The Execute_SQL function connects directly to the database. Inside this function we access the database, execute the SQL string provided by the caller, and return the results, if there are any. First we create an ADO connection:

  Set myConn = CreateObject("ADODB.Connection")

Then we create an ADO recordset:

  set myRecordset = CreateObject("ADODB.Recordset" )

In the next step, we open the connection using the DB_CONNECT_STRING constant:

  myConn.Open = DB_CONNECT_STRING

After the connection is open, we open the recordset using the connection and the SQL:

  myRecordset.Open mySQLCmdText, myConn

And finally we return the results of opening the recordset:

  Set ExecuteSQL = myRecordset

If you have spent any time working with ADO, you will see that all of these steps are very straightforward.
The FormatRecordset function accepts an ADO recordset as a parameter and returns an HTML table (in the form of a string variable). It has two loops—one inside the other. First, it steps through the list of fields and then it loops through the number of rows. These iterations are used to convert the recordset to an HTML table.
The HTML table is created as a string and a simple table is defined as the following:

  strTable = "<table border=1 width=500>"

Then we move to the first record and create the table row with the <tr> tag:

  rstTable.MoveFirst
  

  strTable = strTable & "<tr>"

We then iterate through the number of fields and add the name to the <td> tag with nice colors:

  For Index = 0 To rstTable.Fields.Count - 1
  

          strTable = strTable & "<td bgcolor='blue'><" & _
  

          "font color='white'>"
  

          strTable = strTable & _
  

          rstTable.Fields.Item(Index).Name
  

          strTable = strTable & "</font></td>"
  

  Next

The <tr> tag is closed; this marks the heading for the table:

  strTable = strTable & "</tr>"

Now we continue on to the data part of the recordset to fill the rest of the HTML table. We loop through all records and fill the HTML table tags <tr> and <td>:

  While (Not rstTable.EOF)
  

          strTable = strTable & "<tr>"
  

          For Index = 0 To rstTable.Fields.Count - 1
  

              strTable = strTable & "<td>"
  

              strTable = strTable & _
  

              rstTable(rstTable.Fields.Item(Index).Name).Value
  

              strTable = strTable & "<br>"
  

              strTable = strTable & "</td>"
  

          Next
  

          strTable = strTable & "</tr>"
  

          rstTable.MoveNext
  

      Wend

Finally, the table is complete and the function value is returned as a string variable type:

  strTable = strTable & "</table>"
  

  FormatRecordset = strTable

Sending Reports

      The Send_Mail function is used to send the reports to the authors. Notice that the constants SMTP_SERVER and SENDER_E-MAIL are being used in this function. Send_Mail uses the Microsoft® Collaboration Data Objects, sometimes referred to as CDO 2.0 or CDOSYS.DLL. CDO provides an object model for the development of messaging applications on Windows® 2000. CDOSYS is based on the SMTP and NNTP standards and is available as a system component on Windows 2000 Server installations. It is the standard API for building bulk-mailing and Web-based messaging applications on Windows 2000 Server.
      The Send_Mail function has three parameters: subject, rcpt, and msgHTML. Subject is the subject of the e-mail being sent, rcpt is the receiver's e-mail address, and msgHTML is the HTMLBody of the message. The report is sent to the authors in HTML format.
      The Send_Mail function uses two constants. The cdoSendUsingPickup constant indicates that the message should be sent using the local SMTP service pickup directory. The cdoSendUsingPort constant indicates that the message is using the network (SMTP over the network).
      If the SMTP service is installed on the local computer, then the constant's value defaults to cdoSendUsingPickup. Otherwise, if Outlook® Express is installed, the constant's value defaults to cdoSendUsingPort and the settings from the default account are used. For this article, we use cdoSendUsingPickup.
      Next, we use COM to create Message and Configuration objects as shown here:

  set iMsg = CreateObject("CDO.Message")
  

  set iConf = CreateObject("CDO.Configuration")

Configuration has several fields. Before setting the values of these fields, they are linked to the configuration by:

  Set Flds = iConf.Fields

The https://schemas.microsoft.com/cdo/configuration/ namespace defines the majority of fields used to set configurations for various CDO objects. We set and update the following three fields (SendUsing, SMTP_SERVER, and TimeOut) of the Configuration object:

  With Flds
  

  .Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = _
  

        cdoSendUsingPickup
  

  .Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = _
  

        SMTP_SERVER
  

  .Item("https://schemas.microsoft.com/cdo/configuration/ " & _
        "smtpconnectiontimeout") = 10
  .Update
  End With

Finally, the message is configured with the configuration we've just defined and with the rest of the e-mail message information before it is sent:

  With iMsg
           Set .Configuration = iConf
  

          .To = rcpt
  

          .From = SENDER_EMAIL
  

          .Subject = subject
  

          .HTMLBody = msgHTML
  

          .Send
  

  End With

Conclusion

In this article we have shown how push technology can be implemented using SQL Server 2000, VBScript, ADO, CDO, and DTS packages. Our example is simple but powerful. To extend it, you could use a complex data model and external reporting components to send richer reports to your users. You could also integrate other DTS tasks into the VBScript task. This technique can be used to push a wide variety of information to your users including timely financial statements, order status of goods purchased, industry updates, or any other data you want.

Alok Mehta is the Senior Vice President and Chief Technology Officer of American Financial Systems Inc. in Weston, MA. You can reach Alok at amehta@afs-link.com. Daniel Williams is Assistant Vice President of Technology at Deferral.com, an AFS company, and also a software engineer on the Deferral.com project. Daniel can be contacted at dwilliams@afs-link.com.

AI Skills Fest

Share via