Image of Navigational Map linked to Home / Contents / Search Batching Stored Procedures

by Stephan Grieger - Independent Developer
Image of Line Break

I recently had a need for an application that would execute a number of stored procedures in an SQL Server database. Unfortunately there didn't seem to be anything around that would do the task. Luckily I found an app resting in the dim recesses at the back of my brain. I thought I might share it with the rest of the World as it may actually be a handy application for some.

Basically the way it works is this. The program has an associated Ini file from which it retrieves the stored procedures. Along with each procedure, there are flags, which tell it what to do with the procedure and when to execute it.

The guts of the application is fairly simple. It uses ADO to create a connection to the database, create a command and execute the procedure.

It is important that you use ADO rather that RDO as this will enable you to set timeouts on the commands, thus eliminating the timeout problem for large procedures.

"What timeout problem?", I hear you ask..

This error doesn't ordinarily happen with databases that are of a reasonanble size. However, when you are trying to run a query over a database that has in excess of 12 million records...

The way around the problem is to drop RDO in favour of ADO. ADO gives you not only increased performance, but also the ability to set the timeouts of queries.

The below code will select approximately 2.5 million records. The problem with running this query under RDO is that the first set of results will be returned. Then SQL Server will pause for a little bit while it scans the rest of the database for more matches.

It's during this pause that RDO seems to think that SQL Server has finished the query and will close the connection. The other problem is that if SQL Server takes too long in finding the next result set or it takes to long to finish the query, you will get a timeout error.

ADO allows you to open a connection, then a command and set a timeout value to the command. 0 means that the query will never time out. Dangerous if you are unsure what will happen.

Private Sub Command1_Click()

   Dim t       As Long

   Set objADO = CreateObject("ADODB.Connection")
   objADO.open "driver={SQLServer};server=rater;uid=sa;pwd=;database=system1"

   Set cmdADO = CreateObject("ADODB.Command")
   Set cmdADO.ActiveConnection = objADO

   cmdADO.CommandText = "Select BillNum, Country, OrigTime, RetCost,_ 
   CallDur, TermCity, TollType From CDR Where AcctNum = '1004535' And_ 
   BillType = '1' And BillNum = '01718552945' And (RetStatus = 'X' Or_ 
   RetStatus = 'Y') And (OrigTime >= '05/01/98' And OrigTime <=_ 
   '05/11/98')"
   cmdADO.CommandTimeOut = 60

   Set rstADO = CreateObject("ADODB.Recordset")
   Set rstADO = cmdADO.Execute()

   If Not rstADO.EOF Then
       t = 1
       rstADO.MoveFirst
       While Not rstADO.EOF
           If Int(t / 1000) = t / 1000 Then
               Me.Caption = t
               DoEvents
           End If
           t = t + 1
           rstADO.MoveNext
       Wend
   End If

End Sub

The application is not really designed to return a result set. Rather, it has been created with the view to execute procedures which will in some way alter tables and data.

The code for this is fully documented and pretty much self explanatory, so I won't go into any more detail. It is available for download.

The Ini file basically controls which procedures are executed and how. The Ini file is documented internally and explains exactly how each entry must be written.



Written by: Stephan Grieger
June '98

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