Post reply

Automate Task

  • February 28, 2007 at 7:21 am

    #69525

    I need to run a stored procedure which creates a view. This is a weekly performance report which i need to send this to my manager every week. As it is a routine process i would like to automate this process. Is it possible that by using notification services of sql 2005 or 2000, every week this view or a report is send to my manager thru email. The stored procedure is not at all required to modify every week because it is based on current date.

    All suggestions welcome.

    Thanks,

  • February 28, 2007 at 7:42 am

    #692296

    Why do you need to create a view? I'm not understanding something about what you are looking for.

  • February 28, 2007 at 9:11 am

    #692334

    Sorry may be i need to me more clear.

    The view is a report which needs to be submitted every week. Ya i understand that we can avoid creating stored procedure and just send a out put of view in terms of excel or simple text. Thats fine. But can you show me a way how can automate this task.

    Thanks,

  • February 28, 2007 at 9:43 am

    #692340

    You can create a DTS package (Server->excel) and schedule the package. hope this is what you were looking for.

  • February 28, 2007 at 12:20 pm

    #692394

    Can this package be emailed weekly thru automation. If yes how i will configure the sql agent to send this package for eg. every monday.

    sorry..as i am a new dba, i don't know much about this automation process.Pleease guide.

    thanks a lot.

  • February 28, 2007 at 12:59 pm

    #692406

    Yes you can send the excel report through an email.

    DTS package should look like this:

    Server--->Excel---->send email task or use xp_sendmail----> use an activexscript to delete the current excel and place a template in its place after the email has been sent (template should have all the fields that you are pulling)

    here is the activexscript

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    Dim sDBFileName

    Dim sTemplateFile

    Dim oFSO

    sDBFileName = "\\servername\sharename\test\test.xls"

    sTemplateFile = "\\servername\sharename\test template\test.xls"

    SET oFSO = CreateObject("Scripting.FileSystemObject")

    'If the file exists, delete it.

    IF(oFSO.FileExists( sDBFileName )) THEN

     oFSO.DeleteFile( sDBFileName )

    END IF

     

    'Optionally, copy from another file.

    oFSO.CopyFile sTemplateFile, sDBFileName

     

    SET oFSO = Nothing

     Main = DTSTaskExecResult_Success

    End Function

    An email will be sent whenever this package runs.

    Hope this explains your questions.

     

  • February 28, 2007 at 1:15 pm

    #692411

    Thanks i will try.

     

    Thanks a lot.

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

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