Post reply

Email

  • October 25, 2004 at 2:41 am

    #66930

    I have produced a query which, when run gives me a set of results. Is it possible for me to Email these results through DTS to the relevant people. I don't know which way to go about it.

    Should I run 1 DTS package to put the results into a table and then email the results?

    Access Query (Access Query Returns Results)

     

    DTS Package Executed (DTS Pump Extracts results from Access)

     

    Results Returned in Table (Results are Put into a Table)

     

    Table Emailed to Relevant Users (The table is then Emailed to Certain Users)

    Or can I simply transfer the table to an email using the 'Transfer SQL Server Objects'?

    Please help

    Thanks

  • October 25, 2004 at 11:12 pm

    #527906

      1. Setup c:\template\1st_report.xls

         Format it to be landscape, gray headers, etc

         Increase column widths to avoid getting an error in step #8

           ** You can also setup two tabs on Excel template if

              you want to be able to keep notes in the file

              that are not in your database, one

           ** Example = SQL reports sales results on

              email campaigns, but fails to report "Send to Friend"

              figures since this is an "outsourced" item that

              we get monthly info on

           ** Solution = setup a blank TAB called

              "SQL raw" and the other with all

              the formatted data called "Final"

              and add =VLOOKUP(A1,'SQL raw'!A:B,2,FALSE)

              to column B1 of "Final" so it can find the

              130INPROMO2 A1 associated data in 130INPROMO2

              A1 of "SQL raw" and report the result in "Final"

      2. Copy to c:\final\1st_report.xls

      3. Create a new "LocalPackage"

      4. Select "FTP Task"

         Source = Directory

         Source Directory Path = c:\template\1st_report.xls

         Destination Directory Path = c:\final

         Click "Files"

         Select "1st_report.xls"

         Check "Overwrite"

      5. Add a "SQL Server Connection"

      6. Add a "Microsoft Excel Connection"

         File Name = c:\final\1st_report.xls

      7. Add a "Transform Data Task"

         Select "SQL Server Connection" as the SOURCE

         Select "Microsoft Excel Connection" as the DESTINATION

      8. Double click on the line you just made

         Select "SQL Query"

         Copy in your query statements

            SELECT     MIN(CompanyName) AS Test

            FROM         Customers

         Click "Transformations" to make sure it setup

      9. Add a "Send Mail Task"

     10. Select all the icons you have created

     11. Click "WorkFlow" --> "On Complete"

     12. Delete any lines that are not logical

         Click on your "FTP Task"

         Hold the "CTRL" down

         Click "SQL Server Connection"

         Release the "CTRL"

         Click "WorkFlow" --> "On Complete"

           Your final workflow line will be

            (1) FTP, (2) SQL, (3) Excel, (4) Email

     13. Left click anywhere in the white space

     14. Right click "Package Properties"

     15. Reduce the "Limit max # of Tasks" to 1

     16. Click "Package" --> "Save As"

     17. Right click on the package

     18. Left click "Schedule"

           Do the following to see existing schedules

              Click "Management" --> "SQL Agent" --> "Jobs"

    You will want to change the "Send Email Task"

    to an "ActiveX Script Task" if sending shared files

    to outside users, to avoid the "I cannot view the

    file since it is referencing an unknown drive" issue.

    Sample "ActiveX Script Task" Code Below

         Function Main()

         Dim iMsg

         set iMsg = CreateObject("CDO.Message")

         Dim objMail

         Set objMail = CreateObject("CDO.Message")

         objMail.From = "test@test.com"

         objMail.To ="test@test.com"

         objMail.Subject="your subject here"

         objMail.Attachment = "z:\test.xls"

         objMail.TextBody = "Thanks"

         objMail.Send

         Set objMail = nothing

         Main = DTSTaskExecResult_Success

         End Function

  • October 26, 2004 at 7:19 am

    #527968

    Here is another article on this subject:

    http://www.joesack.com/SQLServer_Email_Reports.htm

    Note to all:  The Send Mail task only works in a Outlook/Exchange environment.  The SQL Server must already be set up to send email.

    Diane

  • October 26, 2004 at 8:57 am

    #527987

    You can set up SQL Mail and use the xp_sendmail procedure to issue a query and email the results, either as an attachment, or at the end of the email body. If xp_sendmail is not an option, or if it proves insufficiently reliable, a good alternative in my experience is the xp_SMTP_sendmail extended stored procedure: http://www.sqldev.net/xp/xpsmtp.htm

     

  • October 26, 2004 at 12:08 pm

    #528041

    You can automate this process by setting up a job with two steps:

    Step1: Run a stored procedure to execute the query and save the result in a table, said tbl_Results

    Step2: Run another stored procedure to scan thru a table, said tbl_result_distribution, looking for users who should receive the result and email it to them.

    When you generalized the process, it will make your job much easier, and this setup would work for any query,result, or report distribution. Of course, you have to code for the two stored procedures, but it is not difficult to write them, by the way.

    If you have any concern, please email me at lamd@vmcmail.com

    VMC-WD-MIS-LAMD

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

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