SQL.au3 How to retrieve multiple tables from stored procedure?

[ferbfletcher]

Hi, I am using SQL.au3 (Chris Lambert v3.2) to connect and query a stored procedure, and it works except that my stored procedure returns 2 tables but this script is only seeing 1 of the tables.  How can I retrieve the 2nd table?  

When I run the query in mssql studio, it shows both tables, one right after the other.  

I'm doing a query like this:  $query = "exec spMyStoredProcedure".  The stored procedure returns 2 tables, but I only get 1 in AutoIt.

 

The stored procedure is like this:

SELECT * FROM [TableA];
SELECT * FROM [TableB];

So, I need to be able to do something like this:

DataTable tableA = ds.Tables[0];
DataTable tableB = ds.Tables[1];

 

 

Edited December 29, 2016 by ferbfletcher

[mLipok]

 

Edited December 29, 2016 by mLipok

[InunoTaishou]

SELECT * FROM TableA, TableB;

 

[ferbfletcher]

No, I cannot bypass the stored procedure.

[jchd]

Returning more than a single resultset is against relational algebra fundations (and common sense as well).

@InunoTaishou,

What you recommend is a cross join between tables, not even close to what the OP expect to have working, deviant as it is.

[ferbfletcher]

25 minutes ago, jchd said:

Returning more than a single resultset is against relational algebra fundations (and common sense as well).

@InunoTaishou,

What you recommend is a cross join between tables, not even close to what the OP expect to have working, deviant as it is.

You are mistaken, this is a standard thing and is used in many applications:

https://msdn.microsoft.com/en-us/library/ms131686.aspx

 

It is neither against common sense, nor it is deviant.

[mLipok]

Did you look in MSDN example ?

https://msdn.microsoft.com/en-us/library/ms130978.aspx

Dim con As New ADODB.Connection  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _  
         & "DataTypeCompatibility=80;" _  
         & "MARS Connection=True;"  
con.Open  
  
Dim recordset1 As New ADODB.Recordset  
Dim recordset2 As New ADODB.Recordset  
  
Dim recordsaffected As Integer  
Set recordset1 =  con.Execute("SELECT * FROM Table1", recordsaffected, adCmdText)  
Set recordset2 =  con.Execute("SELECT * FROM Table2", recordsaffected, adCmdText)  
  
con.Close  
Set con = Nothing  

 

[ferbfletcher]

11 minutes ago, mLipok said:

Did you look in MSDN example ?

https://msdn.microsoft.com/en-us/library/ms130978.aspx

Dim con As New ADODB.Connection  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _  
         & "DataTypeCompatibility=80;" _  
         & "MARS Connection=True;"  
con.Open  
  
Dim recordset1 As New ADODB.Recordset  
Dim recordset2 As New ADODB.Recordset  
  
Dim recordsaffected As Integer  
Set recordset1 =  con.Execute("SELECT * FROM Table1", recordsaffected, adCmdText)  
Set recordset2 =  con.Execute("SELECT * FROM Table2", recordsaffected, adCmdText)  
  
con.Close  
Set con = Nothing  

 

No, that is directly accessing the tables on the db, not using the stored procedure.

 

 

[mLipok]

Could you privide this stored procedure ?
I want to see how data are exported to the outer space

 

[jchd]

@ferbfletcher,

I'd hardly call MARS a "standard". Instead it's a pointless proprietary MS sugar inviting bad code.

[ferbfletcher]

On 12/29/2016 at 2:05 PM, mLipok said:

Could you privide this stored procedure ?
I want to see how data are exported to the outer space

 

It is a large complex procedure, however the contents of the procedure does not matter.  Whether it is complex, or a simple 2 line select as shown above, either way, there are 2 (or more) tables returned.

 

On 12/29/2016 at 7:39 PM, jchd said:

@ferbfletcher,

I'd hardly call MARS a "standard". Instead it's a pointless proprietary MS sugar inviting bad code.

Regardless, many developers use it, and in my case I have no control over changing the stored procedure.  I just receive whatever the procedure sends me.

 

UPDATE:

So, I did finally find the solution, and it is extremely simple.  Hopefully this will help someone else who runs across this in the future:

https://msdn.microsoft.com/en-us/library/ms677539(v=vs.85).aspx

The query handle returned by .Execute() contains the 1st table, like always, and do whatever you want with the data.

THEN, do .NextRecordset(), and the query handle will contain the next table, then again, do whatever you want with the data.

Basically, just add that one line, and it's done.

 

 

 

 

 

[mLipok]

1 hour ago, ferbfletcher said:

It is a large complex procedure, however the contents of the procedure does not matter.  Whether it is complex, or a simple 2 line select as shown above, either way, there are 2 (or more) tables returned.

I do not need to see entire procedure.
How they are returned to the outside of procedure ?