Progress from Stored proc in Excel
 

I've got a stored proc on SQL2000 that is basically just 47 select queries
to compare between an old system and a new system. I'm using
copyfromrecordset in excel to dump the results into the excel spreadsheet as
a simple UI. I'm trying to figure out a way to create a progress bar. I've
created a custom userform and written the code to do the progressbar but I'm
not sure how to increment it based upon which part of the stored proc has
finished. Any suggestions? I can change either code to suit.


Here is the code in Excel:

Sub RunQuery()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lLastRow As Long

Set cn = New ADODB.Connection
cn.Open "Driver={SQL
Server};Server=HCBAPXDB\APX;Trusted_Connection=yes ;Database=HCBDW"

'Execute the stored procedure into a returned record set
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_APX_UDA" 'Name of stored procedure
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set rst = cmd.Execute()

Range("A1").CopyFromRecordset rst

Do
DoEvents
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Set rst = rst.NextRecordset
Loop Until rst.State < 1

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

ActiveSheet.Columns.AutoFit

End Sub

And this is the basic gist of the Stored Proc:

Create Procedure SP_Compare
AS
BEGIN
-- Drop tables if they already exist
if object_id('fullcompare1', 'u') is not null drop table fullcompare1
if object_id('fullcompare2', 'u') is not null drop table fullcompare2

--Create Comparison Tables
select code, active_inactive, proxy_voting, cl_lst_revwd, nso.obj_agmt,
taxable_nontaxable,
rr,
custodian,family_group,dist_freq,dist_day,dist_amt ,employee,pmt_type,billing_short_name,
how_est,why_closed,source,destination,comments_in, comments_out,projected_value,closing_value,
fee_schedule,discount_exp,alt_debit_acct,invoiced, alt_bill_name,alt_bill_address1,alt_bill_address2, alt_bill_address3,
alt_bill_address4,notes,custody,contact,exempt_min ,no_payout,COD_flag,Debit_HCB_COD,custodian_acct,
exempt_min_comm,adj_payout,Sales_office
into fullcompare1
from Table1 uda
join Table2 nso on nso.portcode = uda.code
order by code

select p.portfoliocode,
e.status, e.proxy_voting, b.closedate, e.obj_agmt, p.taxstatus, e.rr,
e.custodian,
e.family_group,e.dist_freq,e.dist_day,e.dist_amt,e .employee,e.pmt_type,e.billing_short_name,
e.how_est,e.why_closed,e.Acct_source,e.Acct_destin ation,e.comments_in,e.comments_out,
e.projected_value,e.closing_value,e.fee_schedule,e .discount_exp,e.alt_debit_acct,e.invoiced,
e.alt_bill_name,e.alt_bill_address1,e.alt_bill_add ress2,e.alt_bill_address3,e.alt_bill_address4,
e.notes,e.custody,e.contact,e.exempt_min,e.no_payo ut,e.COD,e.Debit_HCB_COD,
e.custodian_acct,e.exempt_min_comm,e.adj_payout,e. Salesoffice
into fullcompare2
from Portfolio p
join PortfolioBase b on p.portfolioid = b.portfoliobaseid
join PortfolioBaseExt e on e.portfoliobaseid = p.portfolioid
order by p.portfoliocode

-- Active/Inactive
select 'Active / Inactive Status';
select 0 as sort, 'code' as a, 'UDA_active_inactive' as b, 'APX_status' as c
into #fc1
union
SELECT 1, code, active_inactive as UDA_active_inactive, status as APX_status
FROM (SELECT C1.code, C1.active_inactive, C2.status
FROM fullCompare1 AS C1
full JOIN fullCompare2 AS C2
ON C1.code = C2.portfoliocode) AS T
WHERE active_inactive < status
order by 1;
select a,b,c from #fc1
drop table #fc1

-- Proxy Voting
select 'Proxy Voting';
select 0 as s, 'code' as a, 'UDA_proxy_voting' as b, 'APX_proxy_voting' as
c, 'Open/Closed' as d
into #fc2
union
SELECT 1, code, T.PV1 as UDA_proxy_voting, T.PV2 as APX_proxy_voting, T.OC
FROM (SELECT C1.code, c1.active_inactive as OC,
case c1.proxy_voting
when 1 then 'Yes' else 'No' end as PV1,
c2.proxy_voting as PV2
FROM fullCompare1 AS C1
full JOIN fullCompare2 AS C2
ON C1.code = C2.portfoliocode) AS T
WHERE T.PV1 < T.PV2
and T.OC like '%A%'
order by 1;
select a,b,c,d from #fc2;
drop table #fc2;

-- with 45 more after this..

if object_id('fullcompare1', 'u') is not null drop table fullcompare1
if object_id('fullcompare2', 'u') is not null drop table fullcompare2

set nocount off

End


TIA

Matt

Progress from Stored proc in Excel
 

Not possible surely. The SP is executed in the database, so you get no
feedback until it completes, successfully or otherwise.

--
__________________________________
HTH

Bob

"Matt Williamson" wrote in message
...
I've got a stored proc on SQL2000 that is basically just 47 select queries
to compare between an old system and a new system. I'm using
copyfromrecordset in excel to dump the results into the excel spreadsheet
as a simple UI. I'm trying to figure out a way to create a progress bar.
I've created a custom userform and written the code to do the progressbar
but I'm not sure how to increment it based upon which part of the stored
proc has finished. Any suggestions? I can change either code to suit.


Here is the code in Excel:

Sub RunQuery()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lLastRow As Long

Set cn = New ADODB.Connection
cn.Open "Driver={SQL
Server};Server=HCBAPXDB\APX;Trusted_Connection=yes ;Database=HCBDW"

'Execute the stored procedure into a returned record set
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_APX_UDA" 'Name of stored procedure
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set rst = cmd.Execute()

Range("A1").CopyFromRecordset rst

Do
DoEvents
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Set rst = rst.NextRecordset
Loop Until rst.State < 1

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

ActiveSheet.Columns.AutoFit

End Sub

And this is the basic gist of the Stored Proc:

Create Procedure SP_Compare
AS
BEGIN
-- Drop tables if they already exist
if object_id('fullcompare1', 'u') is not null drop table fullcompare1
if object_id('fullcompare2', 'u') is not null drop table fullcompare2

--Create Comparison Tables
select code, active_inactive, proxy_voting, cl_lst_revwd, nso.obj_agmt,
taxable_nontaxable,
rr,
custodian,family_group,dist_freq,dist_day,dist_amt ,employee,pmt_type,billing_short_name,
how_est,why_closed,source,destination,comments_in, comments_out,projected_value,closing_value,
fee_schedule,discount_exp,alt_debit_acct,invoiced, alt_bill_name,alt_bill_address1,alt_bill_address2, alt_bill_address3,
alt_bill_address4,notes,custody,contact,exempt_min ,no_payout,COD_flag,Debit_HCB_COD,custodian_acct,
exempt_min_comm,adj_payout,Sales_office
into fullcompare1
from Table1 uda
join Table2 nso on nso.portcode = uda.code
order by code

select p.portfoliocode,
e.status, e.proxy_voting, b.closedate, e.obj_agmt, p.taxstatus, e.rr,
e.custodian,
e.family_group,e.dist_freq,e.dist_day,e.dist_amt,e .employee,e.pmt_type,e.billing_short_name,
e.how_est,e.why_closed,e.Acct_source,e.Acct_destin ation,e.comments_in,e.comments_out,
e.projected_value,e.closing_value,e.fee_schedule,e .discount_exp,e.alt_debit_acct,e.invoiced,
e.alt_bill_name,e.alt_bill_address1,e.alt_bill_add ress2,e.alt_bill_address3,e.alt_bill_address4,
e.notes,e.custody,e.contact,e.exempt_min,e.no_payo ut,e.COD,e.Debit_HCB_COD,
e.custodian_acct,e.exempt_min_comm,e.adj_payout,e. Salesoffice
into fullcompare2
from Portfolio p
join PortfolioBase b on p.portfolioid = b.portfoliobaseid
join PortfolioBaseExt e on e.portfoliobaseid = p.portfolioid
order by p.portfoliocode

-- Active/Inactive
select 'Active / Inactive Status';
select 0 as sort, 'code' as a, 'UDA_active_inactive' as b, 'APX_status' as
c
into #fc1
union
SELECT 1, code, active_inactive as UDA_active_inactive, status as
APX_status
FROM (SELECT C1.code, C1.active_inactive, C2.status
FROM fullCompare1 AS C1
full JOIN fullCompare2 AS C2
ON C1.code = C2.portfoliocode) AS T
WHERE active_inactive < status
order by 1;
select a,b,c from #fc1
drop table #fc1

-- Proxy Voting
select 'Proxy Voting';
select 0 as s, 'code' as a, 'UDA_proxy_voting' as b, 'APX_proxy_voting' as
c, 'Open/Closed' as d
into #fc2
union
SELECT 1, code, T.PV1 as UDA_proxy_voting, T.PV2 as APX_proxy_voting, T.OC
FROM (SELECT C1.code, c1.active_inactive as OC,
case c1.proxy_voting
when 1 then 'Yes' else 'No' end as PV1,
c2.proxy_voting as PV2
FROM fullCompare1 AS C1
full JOIN fullCompare2 AS C2
ON C1.code = C2.portfoliocode) AS T
WHERE T.PV1 < T.PV2
and T.OC like '%A%'
order by 1;
select a,b,c,d from #fc2;
drop table #fc2;

-- with 45 more after this..

if object_id('fullcompare1', 'u') is not null drop table fullcompare1
if object_id('fullcompare2', 'u') is not null drop table fullcompare2

set nocount off

End


TIA

Matt

Progress from Stored proc in Excel
 

Bob Phillips ) writes:
Not possible surely. The SP is executed in the database, so you get no
feedback until it completes, successfully or otherwise.

This is not correct. Matt has 47 result sets, and unless the result
sets are very small, his client code will be activated when he gets one
or more result sets, so he could build the progress bar around his
calls to NextRecordset.

Obviously, if some queries takes longer time to run than others, the
progress bar can be unreliable if it's percentage of time. If he bases
it on the number of queries, the bar will make more sense.


--
Erland Sommarskog, SQL Server MVP,

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Progress from Stored proc in Excel
 

On Fri, 20 Jun 2008 16:06:00 -0400, "Matt Williamson" wrote:

¤ I've got a stored proc on SQL2000 that is basically just 47 select queries
¤ to compare between an old system and a new system. I'm using
¤ copyfromrecordset in excel to dump the results into the excel spreadsheet as
¤ a simple UI. I'm trying to figure out a way to create a progress bar. I've
¤ created a custom userform and written the code to do the progressbar but I'm
¤ not sure how to increment it based upon which part of the stored proc has
¤ finished. Any suggestions? I can change either code to suit.

You won't be able to display the progress of each individual query, but as Erland was indicating you
can display the percentage of progress based upon the number queries that have been processed by
CopyFromRecordset.

For example, if you have 50 SELECT queries and 10 have been processed by CopyFromRecordset, the
progress bar should indicate that the process is 20% completed (10 divided by 50).


Paul
~~~~
Microsoft MVP (Visual Basic)

Progress from Stored proc in Excel
 

¤ I've got a stored proc on SQL2000 that is basically just 47 select
queries
¤ to compare between an old system and a new system. I'm using
¤ copyfromrecordset in excel to dump the results into the excel
spreadsheet as
¤ a simple UI. I'm trying to figure out a way to create a progress bar.
I've
¤ created a custom userform and written the code to do the progressbar but
I'm
¤ not sure how to increment it based upon which part of the stored proc
has
¤ finished. Any suggestions? I can change either code to suit.

You won't be able to display the progress of each individual query, but as
Erland was indicating you
can display the percentage of progress based upon the number queries that
have been processed by
CopyFromRecordset.

For example, if you have 50 SELECT queries and 10 have been processed by
CopyFromRecordset, the
progress bar should indicate that the process is 20% completed (10 divided
by 50).


Paul
~~~~
Microsoft MVP (Visual Basic)

Is there any way to count the number of recordsets? I've stepped through the
loop many times and I'm not seeing any property that gives the current
recordset number.

TIA

Matt

Progress from Stored proc in Excel
 

Matt Williamson ) writes:
Is there any way to count the number of recordsets? I've stepped through
the loop many times and I'm not seeing any property that gives the
current recordset number.

You would have to keep the count yourself. And you need to know how many
the total number recordsets there are. But your initial post, indicated
that this is known to you.


--
Erland Sommarskog, SQL Server MVP,

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx