Multi result set procs in ADO, is it possible?

[Blade Hunter]

Hi Guys, so I have a proc that when run in SQL Server returns a 4 teir window for the results (4 result sets) and each one contains juicy info that I want except the first one, Running the proc in VBA using ADO unfortunately only seems to return the first result set and dismisses the rest, is there something simple I can do (maybe the result set is in a 2D array??) to get all the results back in Excel?

Cheers

Dan

 

[Blade Hunter]

*Bump*

 

[Norie]

Dan

What to you mean by a 'proc'?

A stored procedure?

Couldn't all the data be returned to one window/recordset?

Is it possible to separate the result sets in anyway?

 

[Blade Hunter]

OK, So I did some more digging and came across some advice to use the NextRecordSet method of the Recordset object. I did this and I got the following error:

run time error 3251 current providor does not support returning multiple recordsets from a single execution

Now this confuses the hell out of me because the server DOES support multi result procs, I can run the SP directly in SQL and it works (returns 4 seperate result sets).

Any advice would be appreciated guys, I am stuck on this.

Cheers

Dan

 

[Blade Hunter]

Dan

What to you mean by a 'proc'?

A stored procedure?

Couldn't all the data be returned to one window/recordset?

Is it possible to separate the result sets in anyway?

Hi Norie, yes a Stored Procedure, it has been written and is maintained by one of our developers (not me ) so I can't really go about reinventing the wheel.

To give you some background info, I have built a schedule compiler that compiles all the schedules from the various record labels, You can then select a bunch of UPC's (Product codes) and click a button, it will pole through them and return the status of that product on our D2C site. It runs various stages of checks to find the content and the last one is the "last ditch effort" to find a trace of it, this is the multi result proc.

Currently the first result set is pretty much the least useful to me out of all 4, I just need to be able to return the 4 result sets one after the other, I can then scan through them for the common status's and alert the user accordingly or promote the ingestion of that product through the workflow queue via another small piece of SQL code.

Thanks

Dan

 

[Norie]

Why are there multiple recordsets?

That just doesn't make sense to me - surely you want one recordset with all the data.

I was going to suggest a UNION query but I don't know if that would be suitable and/or how it would (could?) work with stored procedures.

I also don't know what access, if any, you have to the actual server.

What exactly does the stored procedure do?

 

[Blade Hunter]

It's like an all in one final check

Recordset 1 is 17 columns, 2 is 10, 3 is 6 and 4 is 10. Each of the 4 record sets will give me information on the product, where it is in regards to being ingested in to the system and what action I need to perform to get it live.

Each result is from a completely different set of tables with only a few things like UPC or Internal ID being common. We are talking millions and millions of rows in these tables so a qeury across all these tables with joins would not be performance efficient so we wrote a multi result set proc. it returns in around 10 seconds.

I have god access to just about everything in this place but my MS SQL is a bit rusty so I generally leave the procs to the developers and I stick to fairly basic free hand SQL.

This is a basic idea of how it is working (Removed just about all info for security reasons):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ProcName] 
 @UPC VARCHAR(30)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 -- Is the product currently in System?
 SELECT Columns1
 FROM Table1 inner join Table2 on Something = SomethingElse
 WHERE Something = SomethingElse
   AND VCH_EXTERNAL_ID LIKE @UPC
 -- Is the product currently in the workflow?
 SELECT *
 FROM Table3
 WHERE VCH_EXTERNAL_ID LIKE @UPC
 -- Is the product inside ProcessedContent content?
 SELECT Columns2
 FROM Table4 inner join Table5 on Something = SomethingElse
 WHERE VCH_EXTERNAL_ID LIKE @UPC
 -- Is the product currently in a delivery->ProcessedContent workflow
 SELECT *
 FROM Table6
 WHERE VCH_OBJECT_ID LIKE @UPC
END

Cheers

Dan

 

[Blade Hunter]

Here is an example of the output in SQL Server Manager.

 

[xenou]

Just out of curiousity how are you attempting to retrieve four recordsets in one vba routine? I guess I wouldn't assume that will happen "automatically". Running four procedures (i.e. four actually distinct queries in four stored procedures) would be simpler and work the "normal" way. Another option would be to append the results of each query to a temporary table and then return the result - which is about the same as Norie's idea of unioning everything.

 

[Norie]

Dan

That just seems like 4 select queries as far as I can see.

Are the columns/fields being returned the same?

I know you say the no of columns is different but I mean generally.

You can use queries in a UNION query that don't have all the fields, you just add the missing fields as expressions.

SELECT Field1, Field2, Field3
FROM Table1
WHERE something equals something

UNION

SELECT Field1, Field2:"", Field3
FROM Table1
WHERE something is something else

Perhaps not a good example, and may not help you but it might be an idea.

I'm going to find out if there is another way to find the results and see what the story is when the multiple recordsets are returned.

Or if they even are returned - only the first recordset being returned is kind of familiar from other things.