dim objshell on error resume next Set objShell = CreateObject("Wscript.Shell") call Main() wscript.quit Function Main() ' 'wscript.Echo "Howdy" 'Call adUsersLoad()' 'exit function ' 'Function adUsersLoad() Dim adoConnection, strBase, strFilter, strAttributes Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strcn Dim SQLConnection, SQLConnString, sqlCommand, SQLRecordset, adostrSQL, sDesc, item '10 '****************************************************** ' Detail ADO objects for AD '****************************************************** Set adoConnection = CreateObject("ADODB.Connection") adoConnection.Provider = "ADsDSOObject" adoConnection.Open "Active Directory Provider" '****************************************************** ' Create your SQL Server Connection '20'****************************************************** 'SQLConnString = ";DRIVER={SQL Server};SERVER=calisqltest01;DATABASE=AD_DB;Trusted_Connection=Yes;" 'SQLConnString = "DRIVER={SQL Server};SERVER=calisqltest01;DATABASE=AD_DB;Trusted_Connection=Yes;" SQLConnString = ";DRIVER=SQL Server;SERVER=calisqltest01.CALITEST.local;DATABASE=AD_DB;Trusted_Connection=Yes" Set SQLConnection = CreateObject("ADODB.Connection") Set SQLRecordset = CreateObject("ADODB.Recordset") SQLConnection.Open SQLConnString '******************************************** ' Specify a command to Truncate the Table '******************************************** Set sqlCommand = CreateObject("ADODB.Command") sqlCommand.ActiveConnection = SQLConnection sqlCommand.CommandType = 1 ' adCmdText sqlCommand.CommandText = "Truncate Table [AD_DB].[dbo].[AD_USER_REPORT]" sqlCommand.Execute , , 128 ' no records returned Set sqlCommand = Nothing '******************************************** ' Open The AD RecordSets & MSQ SQL RecordSets '******************************************** '38 adostrSQL = "SELECT [userAccountControl],[DN],[sAMAccountName],[department],[company],[mail],[description]" adostrSQL = adostrSQL & ",[givenName],[sn],[streetAddress],[l],[st],[postalCode],[co]" adostrSQL = adostrSQL & ",[telephoneNumber],[otherTelephone],[displayName],[extensionAttribute2]" adostrSQL = adostrSQL & ",[extensionAttribute3],[extensionAttribute4],[extensionAttribute5]" adostrSQL = adostrSQL & ",[extensionAttribute7],[extensionAttribute6],[extensionAttribute8]" adostrSQL = adostrSQL & ",[extensionAttribute9],[extensionAttribute10],[createTimeStamp],[whenChanged],[Name],[Manager]" adostrSQL = adostrSQL & " FROM [AD_DB].[dbo].[AD_USER_REPORT]" '47 Set SQLRecordset = CreateObject("ADODB.Recordset") Set SQLRecordset.ActiveConnection = SQLConnection SQLRecordset.Open adostrSQL, SQLConnection, 1, 4 Set objRootDSE = GetObject("LDAP://RootDSE") strDNSDomain = objRootDSE.Get("defaultNamingContext") strBase = "" strFilter = "(&(objectCategory=person)(objectClass=user)(company=*))" '58 strAttributes = "sAMAccountName,DisplayName,sn,GivenName,mail,StreetAddress,l,TelephoneNumber,cn,distinguishedName," strAttributes = strAttributes & "OtherTelephone,Company,Department,extensionAttribute1,extensionAttribute2,extensionAttribute3," strAttributes = strAttributes & "extensionAttribute4,extensionAttribute5,extensionAttribute6,extensionAttribute7,extensionAttribute8," strAttributes = strAttributes & "extensionAttribute9,extensionAttribute10,co,userAccountControl,l,co,st,postalCode,createTimeStamp,WhenChanged,description, name, manager" ' AD LDAP QUERY strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" Set adoRecordset = CreateObject("ADODB.Recordset") Set adoRecordset.ActiveConnection = adoConnection adoRecordset.Properties("Page Size") = 1000 ' EXECUTE THE QUERY adoRecordset.Source = strQuery adoRecordset.Open ' Enumerate the results Do Until adoRecordset.EOF 'On Error GoTo Err_442 '74 SQLRecordset.AddNew SQLRecordset.Fields("userAccountControl") = adoRecordset.Fields("userAccountControl") SQLRecordset.Fields("DN") = adoRecordset.Fields("distinguishedName") SQLRecordset.Fields("sAMAccountName") = adoRecordset.Fields("sAMAccountName") SQLRecordset.Fields("department") = adoRecordset.Fields("department") sDesc = "" If IsNull(adoRecordset.Fields("description")) = False Then For Each item In adoRecordset.Fields("Description").Value sDesc = sDesc & Trim(item) Next SQLRecordset.Fields("description") = sDesc Else SQLRecordset.Fields("description") = Null End If 'SQLRecordset.Fields("extensionAttribute1") = adoRecordset.Fields("extensionAttribute1") SQLRecordset.Fields("company") = adoRecordset.Fields("company") SQLRecordset.Fields("mail") = adoRecordset.Fields("mail") SQLRecordset.Fields("givenName") = adoRecordset.Fields("givenName") SQLRecordset.Fields("sn") = adoRecordset.Fields("sn") SQLRecordset.Fields("streetAddress") = adoRecordset.Fields("streetAddress") SQLRecordset.Fields("l") = adoRecordset.Fields("l") SQLRecordset.Fields("st") = adoRecordset.Fields("st") SQLRecordset.Fields("postalCode") = adoRecordset.Fields("postalCode") SQLRecordset.Fields("co") = adoRecordset.Fields("co") SQLRecordset.Fields("telephoneNumber") = adoRecordset.Fields("telephoneNumber") SQLRecordset.Fields("displayName") = adoRecordset.Fields("displayName") SQLRecordset.Fields("extensionAttribute2") = adoRecordset.Fields("extensionAttribute2") SQLRecordset.Fields("extensionAttribute3") = adoRecordset.Fields("extensionAttribute3") SQLRecordset.Fields("extensionAttribute4") = adoRecordset.Fields("extensionAttribute4") SQLRecordset.Fields("extensionAttribute5") = adoRecordset.Fields("extensionAttribute5") SQLRecordset.Fields("extensionAttribute6") = adoRecordset.Fields("extensionAttribute6") SQLRecordset.Fields("extensionAttribute7") = adoRecordset.Fields("extensionAttribute7") SQLRecordset.Fields("extensionAttribute8") = adoRecordset.Fields("extensionAttribute8") SQLRecordset.Fields("extensionAttribute9") = adoRecordset.Fields("extensionAttribute9") SQLRecordset.Fields("extensionAttribute10") = adoRecordset.Fields("extensionAttribute10") SQLRecordset.Fields("createTimeStamp") = adoRecordset.Fields("createTimeStamp") SQLRecordset.Fields("whenChanged") = adoRecordset.Fields("whenChanged") SQLRecordset.Fields("name") = adoRecordset.Fields("name") ' 104 adoRecordset.MoveNext Loop SQLRecordset.UpdateBatch adoRecordset.Close adoConnection.Close Set adoRecordset = Nothing Set adoConnection = Nothing SQLRecordset.Close Set SQLRecordset = Nothing SQLConnection.Close Set SQLConnection = Nothing '119 Main = True 'Exit_Proc: '0 Exit Function 'Err_442: ' If Err.Number = 424 Or Err.Number = 13 Then' ' Resume Next ' End If ' main= False ' MsgBox Error ' Resume Exit_Proc End Function