Batching Stored Procedures
by Stephan Grieger - Independent Developer
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.
