We help IT Professionals succeed at work.

Select statement works in SQL Query, not in VBscript.  Why?

The f ollowing select statement in SQL Query works correctly.  

Select ePO4_CWEPOCON.dbo.OrionUsers.id as 'UserID',ePO4_CWEPOCON.dbo.OrionUsers.Fullname as 'FullName',ePO4_CWEPOCON.dbo.OrionGroups.Name as
'GroupName',Max(ePO4_CWEPOCON.dbo.OrionAuditLog.StartTime) as 'LastLogin' from ePO4_CWEPOCON.dbo.OrionUsers
Left Outer join ePO4_CWEPOCON.dbo.OrionGroupMemberships on ePO4_CWEPOCON.dbo.OrionUsers.ID=ePO4_CWEPOCON.dbo.OrionGroupMemberships.UserID  
Left Outer join ePO4_CWEPOCON.dbo.OrionGroups on ePO4_CWEPOCON.dbo.OrionGroupMemberships.GroupID=ePO4_CWEPOCON.dbo.OrionGroups.ID
Left Outer join ePO4_CWEPOCON.dbo.OrionAuditLog on ePO4_CWEPOCON.dbo.OrionUsers.id=ePO4_CWEPOCON.dbo.OrionAuditLog.UserID group by ePO4_CWEPOCON.dbo.OrionGroups.Name,ePO4_CWEPOCON.dbo.OrionUsers.id,ePO4_CWEPOCON.dbo.OrionUsers.Fullname

When I run the same select statement in vbscript, it doesn't select any records/

rs.open "Select dbo.OrionUsers.id as 'UserID',dbo.OrionUsers.Fullname as 'FullName',dbo.OrionGroups.Name as 'GroupName', Max(dbo.OrionAuditLog.StartTime) as 'LastLogin' from dbo.OrionUsers Left Outer join dbo.OrionGroupMemberships on dbo.OrionUsers.ID=dbo.OrionGroupMemberships.UserID Left Outer join dbo.OrionGroups on dbo.OrionGroupMemberships.GroupID=dbo.OrionGroups.ID Left Outer join dbo.OrionAuditLog on dbo.OrionUsers.id=dbo.OrionAuditLog.UserID group by dbo.OrionGroups.Name,dbo.OrionUsers.id,dbo.OrionUsers.Fullname", db

What is wrong with this?
Comment
Watch Question

Commented:
Do you have the error?
jterharkIT Consultant

Author

Commented:
There is no error.  It runs with no errors, yet it does not select/output any records.  

' ***   Script to query Orion User table for list of Users.

Option Explicit
Dim db,rs, strSQL, cnstr, EPOUserReport, ofile, filesys, stroutput, LastLogin, Fullname, GroupName, Userid

Set db = CreateObject("ADODB.Connection")
cnstr =  "Provider=sqloledb;Data Source=cweposql;" & _
"Initial Catalog=epo4_cwepocon; User Id=; Password=;"  
db.open cnstr

Set rs = CreateObject("ADODB.recordset")

rs.open "Select dbo.OrionUsers.id as 'UserID',dbo.OrionUsers.Fullname as 'FullName',dbo.OrionGroups.Name as 'GroupName', Max(dbo.OrionAuditLog.StartTime) as 'LastLogin' from dbo.OrionUsers Left Outer join dbo.OrionGroupMemberships on dbo.OrionUsers.ID=dbo.OrionGroupMemberships.UserID Left Outer join dbo.OrionGroups on dbo.OrionGroupMemberships.GroupID=dbo.OrionGroups.ID Left Outer join dbo.OrionAuditLog on dbo.OrionUsers.id=dbo.OrionAuditLog.UserID group by dbo.OrionGroups.Name,dbo.OrionUsers.id,dbo.OrionUsers.Fullname", db

'strSQL = "Select dbo.OrionUsers.id as 'UserID',dbo.OrionUsers.Fullname as 'FullName',dbo.OrionGroups.Name as 'GroupName', Max(dbo.OrionAuditLog.StartTime) as 'LastLogin' from dbo.OrionUsers" _
'  & " Left Outer join dbo.OrionGroupMemberships on dbo.OrionUsers.ID=dbo.OrionGroupMemberships.UserID" _
'  & " Left Outer join dbo.OrionGroups on dbo.OrionGroupMemberships.GroupID=dbo.OrionGroups.ID" _
'  & " Left Outer join dbo.OrionAuditLog on dbo.OrionUsers.id=dbo.OrionAuditLog.UserID group by dbo.OrionGroups.Name,dbo.OrionUsers.id,dbo.OrionUsers.Fullname"



'rs.open strSQL, db

EPOUserReport = "H:\Hyatt\Mcafee\EPO\EPOUserReport.csv"    
Set filesys = CreateObject("Scripting.FileSystemObject")      

Set ofile = filesys.CreateTextFile(EPOUserReport, True)

ofile.WriteLine "Userid,FullName,GroupID,Last Login"

Dim st, useridout, fullnameout, groupout, startimeout, lineout

rs.MoveFirst


' *** Read & process records
wscript.echo "Start reading records"

Do Until RS.EOF

   st = DateAdd("h",-5,LastLogin)

wscript.echo lastlogin
wscript.echo st  
wscript.echo userid


     lineout = userid & "," & fullname & "," & Groupname & "," &  st
     ofile.writeline(lineout)
 
 RS.MoveNext
Loop


'*** Write last record
If RS.EOF = True Then
     lineout = userid & "," & fullname & "," & Groupname & "," &  st
     ofile.Writeline(lineout)
End If
wscript.echo "Last record"


ofile.close
set oFile = nothing
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Commented:
If you run just a simple select does it work?  If not then it is not the SQL but the ASP.
jterharkIT Consultant

Author

Commented:
I now have this to the point where it is performing correctly except for it is not selecting  user records from  table OrionUsers when there is no matching records in table OrionAuditLog.. Buy using the left join, it should give me all records in OrionUsers regardless if there are any matching records in OrionAuditlog.   Why is it doing this?

strSQL = "Select dbo.OrionUsers.id,dbo.OrionUsers.Fullname,dbo.OrionGroups.Name, Max(dbo.OrionAuditLog.StartTime) as 'lastLogin' from dbo.OrionUsers" _
  & " Left Outer join dbo.OrionGroupMemberships on dbo.OrionUsers.ID=dbo.OrionGroupMemberships.UserID" _
  & " Left Outer join dbo.OrionGroups on dbo.OrionGroupMemberships.GroupID=dbo.OrionGroups.ID" _
  & " Left Outer join dbo.OrionAuditLog on dbo.OrionUsers.id=dbo.OrionAuditLog.UserID Where dbo.OrionAuditLog.CmdName = N'Login attempt' group by dbo.OrionUsers.id,dbo.OrionGroups.Name,dbo.OrionUsers.Fullname"
rs.open strSQL, db

Commented:
Move the where to the join that is the problem, you are saying where nothing (from the Auditlog table matches something in the user table):

strSQL = "Select dbo.OrionUsers.id,dbo.OrionUsers.Fullname,dbo.OrionGroups.Name, Max(dbo.OrionAuditLog.StartTime) as 'lastLogin' from dbo.OrionUsers" _
  & " Left Outer join dbo.OrionGroupMemberships on dbo.OrionUsers.ID=dbo.OrionGroupMemberships.UserID" _
  & " Left Outer join dbo.OrionGroups on dbo.OrionGroupMemberships.GroupID=dbo.OrionGroups.ID" _
  & " Left Outer join dbo.OrionAuditLog on dbo.OrionUsers.id=dbo.OrionAuditLog.UserID
AND dbo.OrionAuditLog.CmdName = N'Login attempt'
group by dbo.OrionUsers.id,dbo.OrionGroups.Name,dbo.OrionUsers.Fullname"
rs.open strSQL, db
jterharkIT Consultant

Author

Commented:
That did it.   What a diff an 'and' can make versus a 'where'.    Thanks!