Image of Navigational Map linked to Home / Contents / Search OLE Automation in SQL Server 6.5

by Lisa Hooper - GUI Computing
Image of Line Break

I have always found SQL Server's task scheduler which comes standard as part of the SQL Executive service to be one of the most useful features of SQL Server. Often, within a single database there will be a large number of database maintenance tasks scheduled as well as application specific tasks such as transferring of data in and out of other business systems.

Most of the scheduled tasks within a database would usually be the perfect job for stored procedures but there are some instances where this is not the case. Stored procedures are not particularly nice for transferring data directly to or from other database systems, nor are they particularly good for writing out files in precise and differing formats. Let's face it sometimes VB is just the best tool for the job. Rather then spending hours attempting to work around your problem in some sort of bizarre Transact SQL which does not really fit the task at hand, why not leverage existing knowledge and often existing code by write VB functions?

OLE Automation Stored Procedures

With SQL Server 6.5's new OLE Automation stored procedures it is now easy to write some of your routine tasks in VB and have them run by the task scheduler in the same way as all your other stored procedures. These OLE Automation stored procedures allow you to use standard OLE Automation objects within a Transact-SQL statement batch. The new stored procedures are.

Example

As an example I have created an Active X dll in VB 5 called HostData which contains a single class called Customer. The customer class contains a method called GetNewCustmersForState which connects to both the SQL Server database and the company's main sales database (not SQL Server) and transfers all the customer records for a given state to the SQL Server database. The class also contains a property called DateOfLastExtract which checks a control table on the main database and returns the date an extract was last run.

Public Function GetNewCustmersForState(ByVal sState As String) As String
    Dim ws As Workspace
    Dim cnHost As Connection
    Dim cnSQL As Connection
    Dim rsHostData As Recordset
    Dim rsSQLData As Recordset
    Dim sSQL As String
    
  On Error GoTo Error_Handler
    
    ' Create ODBC workspace
    Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    
    'Open connection to SQL Server database
    Set cnSQL = ws.OpenConnection("SQLData", dbDriverNoPrompt, False, _
    "ODBC;DATABASE=Sales;UID=sa;PWD=;DSN=SQLODBC")
    
    'Open connection to Host database (Oracle)
    Set cnHost = ws.OpenConnection("HostData", dbDriverNoPrompt, False, _
    "ODBC;DATABASE=Sales;UID=sa;PWD=;DSN=HOSTODBC")

    'Open recordset to read
    Set rsHostData = cnHost.OpenRecordset("Select * from tblCustomerHost where state = '" & sState & "'", dbOpenSnapshot)
    'Open recordset to write
    Set rsSQLData = cnSQL.OpenRecordset("Select * from tblCustomer where state = '" & sState & "'", dbOpenDynamic, dbExecDirect, dbOptimisticValue)

    Do While Not rsHostData.EOF
        'Add host data to SQL Server data table
        rsSQLData.AddNew
            rsSQLData!CustomerId = rsHostData!CustomerId
            rsSQLData!Name = rsHostData!Name
            rsSQLData!State = rsHostData!State
            rsSQLData!DateAdded = rsHostData!DateAdded
        rsSQLData.Update
        rsHostData.MoveNext
    Loop

    rsHostData.Close
    rsSQLData.Close

    cnHost.Close
    cnSQL.Close
    ws.Close
    
    GetNewCustmersForState = "SUCCESS"
    
    Exit Function

Error_Handler:
    GetNewCustmersForState = Errors(0)

End Function


Public Property Get DateOfLastExtract() As Date
    Dim ws As Workspace
    Dim cnHost As Connection
    Dim rsHostData As Recordset
    Dim sSQL As String
    
    ' Create ODBC workspace
    Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    
    'Open connection to Host database (Oracle)
    Set cnHost = ws.OpenConnection("HostData", dbDriverNoPrompt, False, _
    "ODBC;DATABASE=Sales;UID=sa;PWD=;DSN=HOSTODBC")

    'Open recordset
    Set rsHostData = cnHost.OpenRecordset("Select LastUploadDate from tblControl", dbOpenSnapshot)

    If rsHostData.RecordCount > 0 Then
        DateOfLastExtract = rsHostData!LastUploadDate
    Else
        DateOfLastExtract = "1-Jan-1900"
    End If
    rsHostData.Close

    cnHost.Close
    ws.Close
    
End Property

To make use of my new dll I must use the OLE Automation stored procedures to create the object.

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
  
-- Create the object
EXEC @hr = sp_OACreate 'HostData.Customer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

-- Get the value for the DateOfLastExtract property and return it into the
-- @property variable
EXEC @hr = sp_OAGetProperty @object, 'DateOfLastExtract', @property OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @property

-- Call the GetNewCustmersForState method of the object passing in the parameter
-- of 'Vic'.
-- The return value of the function is returned in the @Return variable
EXEC @hr = sp_OAMethod @object, 'GetNewCustmersForState', @return OUT, 'Vic'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @return
  
-- Destroy the object when finished 
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

Any of the above Transact SQL code can be used in a stored procedure and scheduler within SQL Executive.

OLE Automation Errors

If you are wondering what this sp_displayoaerrorinfo thing is, it is a sample stored procedure which can be found in books online used to display OLE Automation error information when one of the OLE Automation procedures returns a non-zero HRESULT return code. In the case of task scheduling it is no use having your errors displayed on the screen so I usually change sp_displayoaerrorinfo to either E-mail someone when there is an error or to write out a log file.



Written by: Lisa Hooper
October '97

Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]