M
Matt Williamson
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_destination,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_address2,e.alt_bill_address3,e.alt_bill_address4,
e.notes,e.custody,e.contact,e.exempt_min,e.no_payout,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
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_destination,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_address2,e.alt_bill_address3,e.alt_bill_address4,
e.notes,e.custody,e.contact,e.exempt_min,e.no_payout,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