+ Post New Thread
Results 1 to 2 of 2
Scripts Thread, VBScript - SQL String Help in Coding and Web Development; I have a SQL string that I run in SQL Server Management Studio. It'll query the DB and look for ...
  1. #1

    Join Date
    Apr 2012
    Location
    Pittsburgh
    Posts
    5
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    VBScript - SQL String Help

    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:
    Code:
    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:
    Code:
    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
    Last edited by ChrisH; 1st May 2012 at 11:49 AM.

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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.
    Last edited by jinnantonnixx; 1st May 2012 at 11:06 AM.

SHARE:
+ Post New Thread

Similar Threads

  1. VBScript / SQL Server
    By Gatt in forum Scripts
    Replies: 7
    Last Post: 23rd September 2011, 03:21 PM
  2. Help With SQL Query Error
    By penfold_99 in forum Coding
    Replies: 6
    Last Post: 15th May 2009, 01:30 PM
  3. Help Required for VBscript for Active Directory (Server 2003)
    By markwilliamson2001 in forum Windows Server 2000/2003
    Replies: 3
    Last Post: 16th January 2009, 12:42 PM
  4. VBScript help.
    By Sirbendy in forum Scripts
    Replies: 0
    Last Post: 6th October 2008, 01:24 PM
  5. SQL Database Migration-HELP!!
    By Mr_M_Cox in forum MIS Systems
    Replies: 3
    Last Post: 16th August 2007, 03:24 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •