Automate Task

Question

Post reply

Automate Task

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

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

Steve Jones - SSC Editor SSC Guru Points: 739565 More actions · Answer 1

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

Shah_hs Ten Centuries Points: 1117 More actions · Answer 2

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,

pinky SSCommitted Points: 1997 More actions · Answer 3

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

Shah_hs Ten Centuries Points: 1117 More actions · Answer 4

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.

pinky SSCommitted Points: 1997 More actions · Answer 5

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.

Shah_hs Ten Centuries Points: 1117 More actions · Answer 6

Shah_hs

Ten Centuries

Points: 1117

February 28, 2007 at 1:15 pm

#692411

Thanks i will try.

Thanks a lot.