Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: DTS package prob

    I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?

    Another question: should I explicitly write this to a table? If yes, how?

    ************************************************** ******************** ' Visual Basic ActiveX Script '************************************************* **********************

    Function Main() Main = DTSTaskExecResult_Success

    strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")

    oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"

    set oCommand.ActiveConnectection = oConn

    strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"

    oCommand.CommandText = strQuery oCommand.Properties("Page Size") = 99

    set oRS.sort ="cn"

    i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End Function

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Can you tell us what error you're getting?
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes, it would help to assess and refer to http://www.sqldts.com for reference on the active-x scripts.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2004
    Posts
    18

    Error

    ActiveX can't create object ADODB

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I think you need to register the ActiveX components (OCX) on every workstation. You can download Setup/Install-Maker-Programs which are doing this job for you.

    And ensure other PCs have the same version of the MDAC_TYP and http://msdn.microsoft.com/library/de...eateObject.asp for reference.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    He's attempting to run the ActiveX component from an ActiveX Script in a DTS package. If this is running as a job from SQL Server, then you don't need ADO on anything other then the SQL Server, and that should be installed by default. If you're executing the ActiveX Script in a DTS Package directly (as opposed to a job), then you have to have ADO on your desktop, which should also be installed by default.

    Godofredo, does it tell you exactly which line it fails on? And I'm assuming your real code does not have the run-on lines as in the example originally posted, am I correct?
    That which does not kill me postpones the inevitable.

  7. #7
    Join Date
    Mar 2004
    Posts
    18

    indeed

    No the run lines are due to formatting when copying the code... sorry for that.

    It fails on the | set oRS = CreateObject("ADODB.RecordSet") |- part the part between the two pipes. Is it that what you mean?

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Hmmm...

    Try replacing this line:
    oConn.Provider("ADsDSOObject")
    With this:
    oConn.Provider = "ADSDSOObject"

    Second, I don't know about the "User Name" property. In my connections to LDAP, I simply use "User ID"

    Third, I don't think you need to specify "Ads Provider" when you open the connection. Just leave it as:

    oConn.Open

    For reference, here's some code I've written for connecting to LDAP. You may find it useful:
    Code:
    'We need to make ADO objects to execute a search against Active Directory
    set oConn = CreateObject("ADODB.Connection")
    set oCommand = CreateObject("ADODB.Command")
    set oRS = CreateObject("ADODB.Recordset")
    
    'We have to use the AD Provider to search against AD using ADO
    oConn.Provider = "ADSDSOObject"
    oConn.Properties("User ID") = strADUserID 'Username of the account with access to *search* AD
    oConn.Properties("Password") = strADPassword 'Password of the above username
    oConn.Open 'Open connection to the provider
    set oCommand.ActiveConnection = oConn  'Set the active connection
    
    'Execute a SQL query through the AD Provider to the AD server to find our the username that was typed in
    'ADNamespace = LDAP: (for AD), WINNT: can also be used to authenticate against a NT Domain
    'ADServer = Server or IP to the AD server
    'ADBaseDN = What AD container do we start searching from
    'objectClass = "user" This tells the search to ignore everything except for "user" objects
    'sAMAccountName = This is the user ID we want to find
    strQuery= "SELECT distinguishedName FROM '" & strADNamespace & "//" & strADServer & "/" & strADBaseDN & "' WHERE objectClass='user' AND sAMAccountName='" & strUser & "'"
    oCommand.CommandText = strQuery
    oCommand.Properties("Size Limit") = 1 'We only want one result - and there should be only one.
    oCommand.Properties("Timeout") = 30 'Time out if it takes to long
    set oRS = oCommand.Execute  'Execute the query
    That which does not kill me postpones the inevitable.

Posting Permissions

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