Post reply

Using ActiveX through DTS packages....

  • March 23, 2006 at 10:54 am

    #68779

    Good Morning everyone...

    For some reasons, my DBA does not allow us to use xp_cmdshell, so I can't use it to automate some of my stuff such as calling a .bat file to refresh a pivot table in excel.  I heard that we can write VBA in activeX in DTS packages, and the only problem is that I am not familiar with VBA much. 

    Would anybody please tell/show me if there is a way to REFRESH a pivot table by using ACTIVEX through DTS packages? Or Is there away to REFRESH a pivot table in excel through DTS packages without using xp_cmdshell?

    Please help!

    Thanks!

     

     

  • March 24, 2006 at 5:15 am

    #628569

    In theory you can do this in DTS using VB script by instantiating an Excel object. However one limitation os that you will need to have Excel installed on the server that is running the DTS package. I have not found how to get around this.

    I guess also as you can instantiate objects in vbscript, you may be able to use some sort of command shell object that allows you too excute a dos command. although not too sure about this.


    Thanks Jeet

  • March 24, 2006 at 6:12 am

    #628579

    To get around the xp_cmdshell restriction you can create an unscheduled sql agent job to run a batch file to do your automation.  Then whenever you need to execute the batch file call it with sp_start_job.

  • March 24, 2006 at 8:48 am

    #628650

    Jeet... Thanks for the info... I can't use cmdshell though.

    Ally MacKenzie,

    How do you call a batch file without using cmdshell?  Please educate me on this.  This is the exact problem that I am dealing with right now.  I want to call a batch file but can't use cmdshell, so don't know how to do.

    Thanks!

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply