OLE Automation in SQL Server 6.5
by Lisa Hooper - GUI Computing
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.
