VBScript - SQL String Help

[jmedlin6]

I have a SQL string that I run in SQL Server Management Studio. It'll query the DB and look for student users and display them so I can copy and paste the command in a command prompt to create the user. I'm trying to set it up so it runs in a VBScript. This is what I currently have for objRecordSet.Open:

objRecordSet.Open _
"select cscript 'CreateStudentAccounts.vbs'" & _
"+ rtrim(ps1.EMPLID) + ' '" & _
"+ substring(convert(char(4), ps1.EFFDT, 121), 3, 2) + ' '" & _ 
"+ rtrim(ps1.LAST_NAME) + ' '" & _
"+ rtrim(ps1.FIRST_NAME) + ' '" & _
"+ case" & _
    "when ps1.MI IS NULL then ' '" & _
    "else rtrim(ps1.MI)" & _
  "end" & _
 "from	PS_AD_UPDATES ps1" & _
 "where not exists (" & _
"select	*" & _
  "from	PS_AD_UPDATES ps2" & _
  "where ps1.EMPLID = ps2.EMPLID" & _
    "and ps2.ACTION in ('N', 'T', 'D')" & _
    "andps1.DATETIME_STAMP < ps2.DATETIME_STAMP" & _
")" & _
   "and ps1.ACTION in ('C', 'H')" & _
   "and ps1.EFFDT >= '2009-01-01'" & _
   "and lower(ps1.JOBTITLE) like '%student%'" & _
   "and	(	ps1.NETCONNECT1 is null" & _
    "or	rtrim(ps1.NETCONNECT1) <= ' '" & _
")" & _
 "order by ps1.LAST_NAME, ps1.FIRST_NAME, ps1.EFFDT", _ 
       objConnection, adOpenStatic, adLockOptimistic 

 

Is there an easier way to execute the sql query? My objConnection.Open is fine. I use the same script to do a simple query, but nothing this elaborate.

 

 

 

This is what I currently execute in SQL Server Management Studio:

select	'cscript CreateStudentAccounts.vbs '
+ rtrim(ps1.EMPLID) + ' '
+ substring(convert(char(4), ps1.EFFDT, 121), 3, 2) + ' ' 
+ rtrim(ps1.LAST_NAME) + ' ' 
+ rtrim(ps1.FIRST_NAME) + ' '
+ case 
    when ps1.MI IS NULL then ' '
    else rtrim(ps1.MI)
  end
 from	PS_AD_UPDATES ps1
 where	not exists (
select	*
  from	PS_AD_UPDATES ps2
  where	ps1.EMPLID = ps2.EMPLID
    and	ps2.ACTION in ('N', 'T', 'D')
    and	ps1.DATETIME_STAMP < ps2.DATETIME_STAMP
)
   and	ps1.ACTION in ('C', 'H')
   and	ps1.EFFDT >= '2009-01-01'
   and	lower(ps1.JOBTITLE) like '%student%'
   and	(	ps1.NETCONNECT1 is null
    or	rtrim(ps1.NETCONNECT1) <= ' '
)
 order by ps1.LAST_NAME, ps1.FIRST_NAME, ps1.EFFDT

Edited May 1, 2012 by ChrisH

[jinnantonnixx]

Building strings using concatentation is slow and inefficient, especially in VBS. Unfortunately, VBS doesn't have the nice string builder classes which you'll find in the .net framework. If you wrote it in VB.net, it would be much easier and neater.

Improving .NET performance using the StringBuilder Class

 

Stepping back from the problem, it looks like you want to get a user list from your tables. Why not make a paramaterised view or query on the server and call this from one line of VBS, writing the result to a file. This would keep the complexity of the SQL on the server side.

Edited May 1, 2012 by jinnantonnixx