Trying to execute sql 2008 r2 CTE script in VBscript - 2nd select statement is not being executed

[kdjonesmtb2]

Trying to excute a SQL 2008 r2 CTE script in VBscript - 2nd select statement is not being executed

The 1st select statment is being executed and is returning results

" select affiliationid as affiliationidq from Data " &_
" where Rownumberofaffiliates = 1 "



strConnection = "DRIVER=SQL Server;SERVER=ABN-NHP-SQL-T01,11001;UID=keithg;APP=QuickTest Professional;WSID=KEITHGJ790;DATABASE=Plandata_nhptest;Trusted_Connection=Yes"


Set conn = CreateObject("ADODB.Connection")

conn.Open strConnection

query =" ; With Data As " &_
"( " &_
"select " &_
" Row_Number() Over(Partition by a.provid order by g.fullname) as Rownumberofaffiliates " &_
" , a.affiliationid " &_
" , a.effdate " &_
", a.termdate " &_
", g. fullname as site " &_
" from dbo.affiliation a " &_
" left outer join provider g (nolock) on a.affiliateid = g.provid " &_
"WHERE a.provid = '" & DataTable.Value ("Provider_ID", dtGlobalSheet) &"'" &_
" and a.affiltype in ('GROUP', 'DIRECT') " &_
" ) " &_
" select affiliationid as affiliationidq from Data " &_
" where Rownumberofaffiliates = 1 "

Set rs2 = conn.Execute(query)

print query

AffiliationID=rs2("affiliationidq")



print "AffiliationID"
print AffiliationID

 

[SkipVought]

hi,

I would assign the second sql to a variable and then inspect the contents of the variable via debug.print. Use THAT string to access the data to check that it actually works.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}