Using CTE sql script in vbscript - how to assign CTE result to variable in vbscript

[kdjonesmtb2]

Hello,

I running a sql cte script in vbscript and I want assign the result of the vbscript cte sql query to a vbscript variable

This is the code that runs but prints a blank result:

The same query in SQL run in SQL Server R2 2008 produces a result
The vbscript query follows below the SQL

SQL query:

drop table ##QNXT_Claims
select top 1000
a.memid
, a.fullname
, c.claimid, cd.claimline
, cd.prindiag
, cd.servcode
, cd.modcode
,c.status
,c.startdate
,c.enddate
,cd.status as claim_line_status
,cd.dosfrom
, cd.dosto
,c.logdate
,c.paiddate
into ##QNXT_Claims
from --[ABN-NHP-SQL-T03,11003].plandata_rpt.
dbo.claim c with (nolock)
inner join --[ABN-NHP-SQL-T03,11003].plandata_rpt.dbo.
member a with (nolock) on c.memid = a.memid
inner join --[ABN-NHP-SQL-T03,11003].plandata_rpt.
dbo.claimdetail cd with (nolock) on c.claimid = cd.claimid
where paiddate = '2012-10-17 00:00:00.000'
and c.status = 'PAID'
Select count(distinct claimid) as ClaimQty_QNXT from ##QNXT_Claims;

with cte As
(
select
ed.CID as CID_ed,
ed.externalId,
ed.externalData,
ed.SYSTEM_ID,
ucon.CID as CID_ucon,
ucon.diagnosis,
ucon.ICD,
ucon.ICD_category,
ucon.notes as notes_ucon,
ucon.source_id as source_id_ucon,
ucon.source_date as source_date_ucon,
ucon.medical_date,
ucon.entry_date as entry_date_ucon,
ucon.registrar as registrar_ucon,
ucon.inactive as inactive_ucon,
ucon.metadata_guid as metadata_guid_ucon,
ucon.VF_01 as VF_01_ucon,
ucon.VF_02 as VF_02_ucon,
ucon.VF_03 as VF_03_ucon,
ucon.VF_04 as VF_04_ucon,
ucon.VF_05 as VF_05_ucon,
ucon.VF_06 as VF_06_ucon,
ucon.VF_07 as VF_07_ucon,
ucon.VF_08 as VF_08_ucon,
ucon.VF_09 as VF_09_ucon,
ucon.VF_10 as VF_10_ucon,
ucon.db_rowversion as db_rowversion_ucon,
ucon.CONDITION_TYPE_ID,
ucon.TERM_DATE,
ucon.diagnosis_code_set_id,
ucon.ID,
uproc.CID as CID_uproc,
uproc.procedure_code,
uproc.service_place_id,
uproc.procedure_date,
uproc.entry_date as entry_date_uproc,
uproc.notes as notes_uproc,
uproc.source_id as source_id_uproc,
uproc.source_date as source_date_uproc ,
uproc.source_detail,
uproc.registrar as registrar_uproc,
uproc.inactive as inactive_uproc,
uproc.metadata_guid as metadata_guid_uproc,
uproc.VF_01,
uproc.VF_02,
uproc.VF_03,
uproc.VF_04,
uproc.VF_05,
uproc.VF_06,
uproc.VF_07,
uproc.VF_08,
uproc.VF_09,
uproc.VF_10,
uproc.procedure_code_set_id
from [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.EXTERNAL_MEMBER_DATA ed
inner join [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.U_MEMBER_CONDITION ucon
on ed.CID = ucon.CID
inner join [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.U_MEMBER_PROCEDURE uproc
on ed.CID = uproc.CID
where externalId in (select memid collate SQL_Latin1_General_CP1_CI_AI from ##QNXT_Claims)
and ICD in (select prindiag collate SQL_Latin1_General_CP1_CI_AI from ##QNXT_Claims )
)


Select count(distinct ICD) as DiagQty_CCA from cte




VBscript code for CTE

'strConnection = "DRIVER=SQL Server;SERVER=CSN-NHP-SQL-13, 11004;UID=keithg;APP=QuickTest Professional;WSID=KEITHGJ790;DATABASE=CANHPPRD1;Trusted_Connection=Yes"




'strConnection = "DRIVER=SQL Server;SERVER=ABN-NHP-SQL-T09, 11005;UID=keithg;APP=QuickTest Professional;WSID=KEITHGJ790;DATABASE=CANHPPPM1ARE;Trusted_Connection=Yes"

strConnection = "DRIVER=SQL Server;SERVER=csn-nhp-sql-08, 11001;UID=keithg;APP=QuickTest Professional;WSID=KEITHGJ790;DATABASE=plandata;Trusted_Connection=Yes"
'csn-nhp-sql-08, 11001

Set conn = CreateObject("ADODB.Connection")

conn.Open strConnection

'QNXT Prod Query

query = " drop table ##QNXT_Claims select top 1000 " &_
" a.memid " &_
" , a.fullname " &_
" , c.claimid, cd.claimline " &_
" , cd.prindiag " &_
" , cd.servcode " &_
" , cd.modcode " &_
",c.status " &_
",c.startdate " &_
",c.enddate " &_
",cd.status as claim_line_status " &_
",cd.dosfrom " &_
", cd.dosto " &_
",c.logdate " &_
",c.paiddate " &_
"into ##QNXT_Claims " &_
"from dbo.claim c with (nolock) " &_
"inner join dbo.member a with (nolock) on c.memid = a.memid " &_
"inner join dbo.claimdetail cd with (nolock) on c.claimid = cd.claimid " &_
"where paiddate = '2012-10-17 00:00:00.000' " &_
"and c.status = 'PAID' " &_
"Select count(distinct claimid) as ClaimQty_QNXT from ##QNXT_Claims "



On Error Resume Next
If Err.Number <> 0 Then
query="Drop Table ##QNXT_Claims"
Set rs12 = conn.Execute(query)
Else
Print ("No issues with Dropping ##QNXT_Claims_QTP Table")
end if


Set rs200= conn.Execute(query)

query= "Select count(distinct claimid) as ClaimQty_QNXT from ##QNXT_Claims "
Set rs12 = conn.Execute(query)


Total_Distinct_Claimids_QNXT=rs12("ClaimQty_QNXT")



Print ("Total Number of QNXT Medical Claims")

Print Total_Distinct_Claimids_QNXT




'************CCA Link Server Query

Dim Multiple_Mem_ICD



Dim Multiple_Mem_NDC


Dim Total_Distinct_Claimids_CCA




Set rs= conn.Execute(query)


query = " with cte As " &_
" ( " &_
"select " &_
" ed.CID as CID_ed, " &_
" ed.externalId, " &_
" ed.externalData, " &_
" ed.SYSTEM_ID, " &_
" ucon.CID as CID_ucon, " &_
" ucon.diagnosis, " &_
" ucon.ICD " &_
" from [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.EXTERNAL_MEMBER_DATA ed " &_
" inner join [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.U_MEMBER_CONDITION ucon " &_
" on ed.CID = ucon.CID " &_
" inner join [ABN-NHP-SQL-T07,11004].CANHPPPM1.dbo.U_MEMBER_PROCEDURE uproc " &_
" on ed.CID = uproc.CID " &_
" where externalId in (select memid collate SQL_Latin1_General_CP1_CI_AI from ##QNXT_Claims) " &_
" and ICD in (select prindiag collate SQL_Latin1_General_CP1_CI_AI from ##QNXT_Claims ) " &_
")" &_
"Select count(distinct ICD) as NDC_Qty_CCA from cte "

'Set cmd = createobject ("ADODB.Command")
'cmd.CommandTimeout = 300
Dim Distinct_NDC_Qty_CCA
Set rs300= conn.Execute(query)




Total_Distinct_NDC_CCA=rs300("NDC_Qty_CCA")' Variable to hold result of CTE query

Print ("Total_Distinct_NDC_CCA")
Print Total_Distinct_NDC_CCA ' Display prints only blank values