ASP: Reading multiple recordsets from a stored procedure with ASP

Reading a recordset from a stored procedure is easy, essentially we just treat the procedure statement as the SQL statement, for example, something like:

sqlStr="USP_MyProc 1,2,3"
set rs=Server.CreateObject("adodb.Recordset")
rs.cachesize=1 sqlStr, conn, adOpenForwardOnly,adLockReadOnly

But how about reading more than one recordset from a procedure? Perhaps you have a circumstance where you need a couple of resultsets within the same code block. You could have more than one procedure and open each recordset in turn (or perhaps use the same procedure and control which resultset is returned using the procedure’s parameters).

Instead why not return all the required recordists from the one procedure using the one call to the database? It can be done, a procedure can return multiple recordists and each can be read in turn from your ASP code.

The trick is NextRecordset. You may already have been using this without quite realising, for example, when inserting a new record to the database that results in a unique identity value, we usually have a need to grab that value, so we’d do something like this:

sqlStr="INSERT INTO tblMyTable (myField) VALUES (myValue);"
Set rs = conn.execute (sqlStr & "select SCOPE_IDENTITY()").nextrecordset

If you look carefully you’ll see we’re actually running two queries, first the insert, and then a second to grab the identity. You can do the same with a procedure and multiple recordists. For example, let’s say you have a procedure that returns two recordists, you just cycle through them with NextRecordset:

sqlStr="USP_MyProc 1,2,3"
set rs=Server.CreateObject("adodb.Recordset")
rs.cachesize=1 sqlStr, conn, adOpenForwardOnly,adLockReadOnly
set rs2 = rs.NextRecordSet

Now you have the first results in rs and the second batch of results in rs2.

ASP Timeout expired error: Microsoft OLE DB Provider for SQL Server error ‘80040e31’

I was working on an ASP page today that included an AJAX call off to trigger various SQL updates, amongst which was a call to a stored procedure. The page started to error with:

Microsoft OLE DB Provider for SQL Server error ‘80040e31’
Timeout expired

The error line was the call to the stored procedure. After a bit of fiddling around I traced the issue to the ADO ConnectionTimeOut value. By default this is 30 seconds.

There are, I guess, 3 timeout values of interest on an ASP page:

1. Session timeout

How long the user’s browser session will last. On IIS this is defaulted to 20 minutes

2. Script timeout

How long a page can take to complete. On IIS this is defaulted to 90 seconds.

3. Connection timeout

How long a single call to the database can take to complete. This defaults to 30 seconds.

The stored procedure was taking > 30 seconds, hence throwing an error. I solved the problem by increasing the ConnectionTimeOut value for the particular call to the stored procedure, eg:


Dim cmdC
Set cmdC = Server.CreateObject("ADODB.Command")
with cmdC
.ActiveConnection = conn
.CommandText = "[procedure name]"
.CommandType =adCmdStoredProc
.CommandTimeout = 3000
end with


The value is in seconds, so 3000 seconds = 5 minutes. You can read more about ConnectionTimeOut here.

Of course, there’s a whole other discussion why we have a stored procedure taking so long to run as part of an AJAX call. We’ll probably shift it off to a scheduled job or something eventually but we needed a quick fix for the interim.

Looping through records in SQL Server Stored Procedure

Someone who’s pretty good programming on the front end, but not so crash hot on the backend, eg SQL Server, asked me the other day about looping through recordsets in a SQL Server Stored Procedure.

It is of course possible to use a cursor and FETCH but most SQL programmers frown on cursors in stored procedures. Cursors involve grabbing a bunch of records and locking them, there can be a substantial performance hit. They should really only be used as a last resort, for example, where you don’t have a good enough unique key value on your rows (although there are ways around that one usually as well).

The easiest way I know to look through records is to employ a temporary table, here’s a simple example:


-- Create a temporary table to hold the records to be updated
DECLARE @uniqueId int
DECLARE @TEMP TABLE (uniqueId int)
-- Insert into the temporary table a list of the records to be updated
SELECT uniqueId FROM myTable
-- Start looping through the records
-- Grab the first record out
SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100))
-- Perform some update on the record
UPDATE myTable SET myField = 'something or other' WHERE uniqueId = @uniqueId
-- Drop the record so we can move onto the next one
DELETE FROM @TEMP WHERE uniqueId = @uniqueId






How to Set Consultancy Fees

Came across this fantastic nugget the other day:

consultants set their rates by the project. They estimate the number of
hours they expect to spend on a project, then multiply by their hourly

some consultants set their project fees using the value the client
derives from the consultant’s advice. There’s an old joke about
physicist Niels Bohr that illustrate this principle.

company’s machine breaks down. The company’s owner, an old school chum
of Niels Bohr, calls in the physicist for help in fixing it.

Bohr examines the machine. He draws an X on the side and says, "Hit it right here with a hammer."

company’s mechanic hits the machine with a hammer. It springs into
action. The company’s owner thanks Niels Bohr profusely and sends him
on his way.

A few days later, the owner receives an invoice from Bohr for $10,000. Shocked, the owner phones Bohr!

"Niels! What’s this $10,000 invoice? You were only here for 10 minutes! Send me a detailed invoice."

Bohr agrees to send the invoice. A few days later, the company’s owner opens a new invoice.

Drawing X on the side of your machine                             $       1
Knowing where to put the X                                             $ 9,999
Total                                                                             $10,000

HP All in One Printer and Snow Leopard

Went to scan a document yesterday on the HP Photosmart 3310, but my Mac couldn't see the scanner – the pop up scan window kept saying scanner not found.

Played with it for ages, using the HP Device Manager thingy, but apparently the scanner was gone. But could print fine.

Finally found this document on the HP site. What do you know. The Snow Leopard upgrade breaks the HP scanner system.

But not to worry – because now it's all built into Mac, you can scan direct from Preview (very handy, straight to PDF which was what I wanted in the first place); Image Capture; or just from the Print and Fax settings.

You'll need to delete the printer from System Preferences > Print and Fax, and add it again, but that's the work of a moment.

Despite the initial frustration, actually very happy. But damn it's annoying when something simply disappears after an update and you have to go hunting for a solution. I did check for updates on the HP software and it said it was the latest version. What would be nice was if HP pushed an update that alerted you to these changes.

Other projects will pay for hare-brained scheme – NOT!!!!!

Just been reading Kenneth Davidson “Other projects will pay for Rudd’s hare-brained scheme” in The Age today. I think he is just plain wrong in his assertion that “the high-speed broadband network should be built incrementally”. Using his arguments the gas and electricity companies wouldn’t install pipes and wires to my house until I discovered I needed to have a shower or turn on a light.

He misses the point entirely. Innovation over the centuries has been driven by bands of individuals coming across a service or infrastructure and creating new and exciting purposes and products.

Staying in the technology sector for a moment, both the Apple iPhone Apps Store and Facebook Applications systems were infrastructure ideas created by the companies without a complete vision of what would result. But they were visionary enough to comprehend that by constructing a framework that enabled developers across a broad range of competencies and resource availability to create new software, they could stimulate a world of innovation. And that’s exactly what has happened. Both companies now have available tens of thousands of services and applications produced by third parties. Some of the programs are terrible. Others are striking in their innovation. All that was required was a faith in the inventiveness of their customers.

We started our internet company in our lounge room in 2000, back before broadband was even available to us. We sticky taped solutions together at almost no cost using what was available. Six years later we sold the company for several million dollars.

Our innovation today still emanates from our loungeroom – we have web servers in the family room cupboard. We constantly play with the available technologies to investigate how they can be alternatively utilised or reorganised to produce new products and services. Under Davidson’s regime we would not have access to the new infrastructure – not being a big company, or hospital or university.

I notice Davidson has left schools off his list of those privileged organizations who should receive preferential treatment. My eleven year old daughter, who runs online forums, writes on her blog and is constantly searching for new ways to use her internet connection and computer will be very disappointed. He’s ignoring one of the most innovative groups in our country – the kids who, by the time the NBN is finished in eight or nine years, will be moving into the workforce, starting businesses, and creating community and economic value.

History also shows us that major projects, particularly technology based,  rolled out incrementally are rarely completed. Progress slows, attention and resource is diverted, specifications and impetus and politics divert from the path.

Kevin Rudd’s vision on this is bold to be sure. Yes there are massive challenges. But the technology is proven. And put that technology in the hands of individuals and not restrict it to the privileged few, and the aggregate can only benefit Australia and the rest of the world.

Niche Content Millionaire

090406 Book Cover 200px

Just putting the final touches to our new eBook, Niche Content Millionaire and sending drafts off to various friends for review.

Niche Content Millionaire will be released this month. But you can make sure you secure your copy by registering now.

a cool experience when you’re sitting in a lawyer’s office high up in a
down town office block and someone casually hands you a check for a
couple of million dollars. It’s even more cool strolling into the
nearest bank branch and depositing the check – then rushing out to the
ATM to check your account balance.

We’ve been involved
with niche online content as a business since 2000. In that time we’ve
launched commercially successful web sites in Australia, the USA and
the UK. Our sites have attracted tens of thousands of subscribers, and
many times that number of casual visitors. In late 2006 we sold the
largest of our businesses for more than $US1 million.

book is not designed to be a text book, nor a biography, rather it’s a
distillation of our story, married with advice and ideas that we
believe assisted us along our journey to success. We learnt our lessons
the hard way. We hope that you will read the book, learn and then be in
a position to apply those lessons to your own online, or offline,
business venture.