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.
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.