January 29, 2010 at 12:27 pm
Sql Server 2005 /Excel 2003
I tried returning top & bottom results & then importing all 4 sets through VBA. However, only the first set is returned in Excel. How can I return all 4 sets in a single worksheet? Could I call another stored procedure by using another strsql or is an 2nd separate call okay to import the full data on a separate sheet?
create table #tellerRank
( tnum int
,bank varchar (4)
,loc varchar (6)
,description varchar(20)
,inv money
,roi money
)
insert into #tellerRank values ('10054','E102','E10201', 'BA', '10034.25', '200.57')
insert into #tellerRank values ('10068','S115','S1105', 'WM', '10238.25', '168.89')
insert into #tellerRank values ('10174','N202','N20206', 'WF', '17534.25', '267.94')
insert into #tellerRank values ('16754','W902','W90203', 'NY', '13584.25', '245.72')
insert into #tellerRank values ('12646','S302','E30204', 'CH', '11682.25', '186.20')
insert into #tellerRank values ('13579','N113','N11301', 'BA', '16742.35', '210.09')
insert into #tellerRank values ('13550','N113','N11302', 'WM', '19456.88', '250.38')
insert into #tellerRank values ('10354','E478','E47803', 'WF', '12575.59', '207.65')
insert into #tellerRank values ('10088','S101','E10104', 'WF', '11791.58', '178.49')
insert into #tellerRank values ('10234','E102','E10204', 'NB', '13575.60', '225.99')
insert into #tellerRank values ('10567','E102','E10203', 'BA', '12847.78', '201.62')
--ranked worksheet
select top 10 percent tnum, bank, loc, description
,inv
,row_number() over (order by inv desc) as [inv_Rank]
,roi
,row_number() over (order by roi desc) as [roi_Rank]
from #tellerRank
select top 10 percent tnum, bank, loc, description
,inv
,row_number() over (order by roi desc) as [roi_Rank]
,roi
,row_number() over (order by inv desc) as [inv_Rank]
from #tellerRank
select top 10 percent tnum, bank, loc, description
,inv
,row_number() over (order by roi) as [roi_Rank]
,roi
,row_number() over (order by inv) as [inv_Rank]
from #tellerRank
select top 10 percent tnum, bank, loc, description
,inv
,row_number() over (order by inv) as [inv_Rank]
,roi
,row_number() over (order by roi) as [roi_Rank]
from #tellerRank
-------------
Sub TestStoredProcedure()
Dim strSql As String
Dim objConn As New ADODB.Connection
Dim Rank As New ADODB.Recordset
'Set objConn = New ADODB.Connection
objConn.CommandTimeout = 1200
objConn.Open "Provider=sqloledb.1;data source=pomv;Initial catalog=rankqry;Integrated Security = SsPI;"
'objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security = SsPI;"
'Set Rank = New ADODB.Recordset
'On Error GoTo Errhandler
'objConn.CommandTimeout = 1200
strSql = "exec rankprocedure '" & Worksheets("Main").Range("b3") & "','" & Worksheets("Main").Range("b4") & "'"
'strSql = "exec rankprocedure '6/1/09','6/1/09'"
'MsgBox (strSql)
'Set Rank = objConn.Execute(strSql)
Rank.Open strSql, objConn, adOpenStatic, adLockReadOnly
Worksheets("Data").Range("A2").CopyFromRecordset Rank
Rank.Close
Set Rank = Nothing
objConn.Close
Set objConn = Nothing
End Sub
Sub ExeAll()
ClearDataSheet
TestStoredProcedure
TestStoredProcedure2
RefrshPivots
End Sub
February 1, 2010 at 7:38 am
Hi ackrite55,
I haven't done this for a while, but there is a nextrecordset option in ADO.
So you shoul dbe able to do somthing like:
Dim Nextrs as ADODB.Recordset
SET Nextrs = rs.NextRecordset
And then carry on with the next set of results.
As I said haven't touched ADO in Excel for ages, but it should work along those lines.
Cheers,
Rodders...
February 1, 2010 at 10:37 am
Thanks, I have explored a few examples of NextRecordset & each have produced an error. After failed loops, I tried this one but it didnt execute the strSql which uses the desired parameter, @Startdate. However, Set Rank=cmd.Execute yields the error.
strSql = "exec rankprocedure '" & Worksheets("Main").Range("b3") & "','" & Worksheets("Main").Range("b4") & "'"
'strSql = "exec rankprocedure '6/1/09','6/1/09'"
Rank.Open strSql, objConn, adOpenStatic, adLockReadOnly
With cmd
Set .ActiveConnection = objConn
.CommandText = "rankprocedure "
.CommandType = adCmdStoredProc
.Prepared = True
End With
Set Rank = cmd.Execute
Rank.NextRecordset
Set Rank = Rank.NextRecordset
'Worksheets("Rank").Range("A2").CopyFromRecordset Rank
Set Rank = Nothing
objConn.Close
Set objConn = Nothing
February 2, 2010 at 2:19 am
Hi ackrite55,
I think I have sorted it out.
You can't do this with the same recordset variable
Set Rank = Rank.NextRecordset
What you have to do is this:
Dim Rank2 As ADODB.Recordset
Set Rank2 = Rank.NextRecordset
Worksheets("Data").Range("A10").CopyFromRecordset Rank2
Set Rank = Rank2.NextRecordset
Worksheets("Data").Range("A20").CopyFromRecordset Rank
Set Rank2 = Rank.NextRecordset
Worksheets("Data").Range("A30").CopyFromRecordset Rank2
You basically have to keep flipping between the recordsets to get the next one. Or create four recordsets and base each one of the previous recordset's nextrecordset!
This works fine for a few resultsets, and so long as you know how many resultsets are coming back.
Otherwise you code is going to become very cumbersome very quickly.
Cheers,
Rodders...
October 6, 2010 at 2:51 am
Hi,
What about:
set rank2 = rank.clone
CopyFromRecordset Rank2
set rank = rank.nextrecordset
set rabk2 = rank.clone
CopyFromRecordset Rank2
...
So you eventually loop till no more recordsets and allways use same logic?
Succes!
May 25, 2012 at 7:27 am
Hi,
With respect to your line causing the error:
Set Rank = cmd.Execute
I think you are just missing the brackets off the end and it should read like this:
Set Rank = cmd.Execute()
Hope that helps. This thread has certainly been helpful to me 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply