Post reply

Processing cube using stored procedure

  • August 25, 2005 at 8:39 am

    #111638

    I recently read a post where people were talking about how they used a stored procedure to reprocess their cubes and dimensions using dtsrun utility.  Does anyone have an example of what that stored procedure might look like?  I would like to automate the reprocessing of cubes using sql scheduler but I don't know how to create the stored procedure.

     

    Thanks

    Trevor

  • August 25, 2005 at 9:10 am

    #585030

    Hi,

    If the reason to use the SP is to schedule the processing the cube, I would suggest creating a DTS to do just that.

    You should use the "Analysis Services Processing Task" - the symbol is a Cube.

    After creating the DTS (aka package) right-click the specific package and choose "Schedule Package...".

    The DTS will be executed by the SQL Server Agent. See the details Server\Management\SQL Server Agent\Jobs.

    TR.

  • August 25, 2005 at 12:38 pm

    #585127

    Fantastic!  Thank-you. 

    You wouldn't happen to have an example on how to call a dts package from a stored procedure?

  • August 25, 2005 at 12:40 pm

    #585129

    EXEC ('DTSRun /~Z0x6979CADB8B8DE884B8BFC34648CBBA20A75783FEBA84F9FD6DB768368C543165C7F301465563DF2967E5070A3659F42283F7888036ED7C15AB190C36E0E82E363163564EACD843AE89ABDB85DD76475BF1E88403FF1164DA3DDFC4984E1D4269DFD058029428C0581C3447 ')

    you can create a job from within the dts menus and copy that long id from there.

  • August 25, 2005 at 1:02 pm

    #585140

    Thanks for the post that's exactly what I was looking for!

  • August 25, 2005 at 1:06 pm

    #585142

    HTH.

  • August 25, 2005 at 5:15 pm

    #585229

    Just out of interest, if you're looking for a quick and easy way to write the DTSRun command string, g to a cmd (command/dos) window and type dtsrunui (this assume the mssql\80\tools\binn is in your path variable).

    Once the UI has loaded, enter the name of the server that holds the package, select the package (using the eliipses button '...') and then click the 'Advanced' button.  Near the bottom you will see a section that has a 'generate' button, clickin this will create a clear text dtsrun command statement.  Checking the 'Encrypt' checkbox will produce the encryped version as per Remi's post.

    HTH, 

    Steve.

  • August 26, 2005 at 12:49 pm

    #585571

    Hi.

    Just to share other way:

    EXEC MASTER..XP_CMDSHELL 'DTSRUN.EXE /S <server> /N <dtsnamewithOlapProcessingTask> /E'

    I use this call in stored procedures.

    Hope it may help.


    Fernando Ponte
    factdata.com.br

Viewing 8 posts - 1 through 7 (of 7 total)

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