SQL newbie needs help automating query to send via email

[gostars65]

Hello all,

I'm new to using SQL and I'm trying to create a "script" if that is the proper term for it; to automatically pull a query that I have created successfully in a manual process and then have it automatically sent via email to a user so they can act upon the data. any ideas are appreciated.

I'm using SQL Distr Mgmt Framework (SQL-DMF) ver SQL 8.00.2039.

Thanks!

[bwestover]

Hello all,

I'm new to using SQL and I'm trying to create a "script" if that is the proper term for it; to automatically pull a query that I have created successfully in a manual process and then have it automatically sent via email to a user so they can act upon the data. any ideas are appreciated.

I'm using SQL Distr Mgmt Framework (SQL-DMF) ver SQL 8.00.2039.

Thanks!

The way I have done this in the past is to use SQL Mail. Its a little cumbersome to set up as you must install a MAPI client on your SQL server. I was able to use Outlook for this, but you can use others as well. You also need an exchange server, or compatible pop3 mail server.

Here's an article that should get you started.
http://support.microsoft.com/kb/263556

Another alternative you might look into is called XPSMTP. It doesn't require the use of any special software on your SQL server, but it still requires a mail server. Here is the site for that.
XPSMTP

[teddarr]

If you have the Business Intelligence Development Studio (comes with SQL Server developer edition and Visual Studio 2005 academic versions to name a few) you should look into using an SSIS package. Before SQL Server 2005 they were called DTS packages. This will allow you to automate this process and then go to the sever agant and schedule the task to happen on the schedule you require.

[bsanders1979]

I wrote a ruby script to provide this kind of functionality and it's pretty damn easy to use. All configuration is done in the script itself and all you need to do is have ruby/dbi installed and schedule the script. Here's a sample to get you going. Be sure to replace the references to $global accordingly.

require "date"
require "dbi"
require "logger"
require "../../globals"
 
module ERM
    class Backlog        
        def initialize(accounts)
            @log = Logger.new("backlog.log")
            @log.level = Logger::INFO
            @log.info("Initializing")
 
            @accounts = accounts
            @queries = Hash.new
            @queries["Archive"] = Hash.new
            @queries["Archive"]["header"] = "Archiving Progress Report"
            @queries["Archive"]["mapping"] = {}
            @queries["Archive"]["query"] = <<EOQ
                select max(import_date) Imported,max(message_date) Date,max(import_date)-180 Target,datediff(day,max(message_date),max(import_date)-180) Diff
                from rmarchivedb.dbo.message
                where import_date >= '2007/08/04'
                group by convert(varchar,import_date,111)
                order by Imported desc
EOQ
 
            @queries["OldestPerBox"] = Hash.new
            @queries["OldestPerBox"]["header"] = "Oldest Message Per Box"
            @queries["OldestPerBox"]["mapping"] = {"mailboxids" => "{1}"}
            @queries["OldestPerBox"]["query"] = <<EOQ
                select convert(varchar,name) Mailbox,min(time) OldestMessage from
                (
                    select
                    rgm.name,rm.message_id,max(rml.time) time
                    from rm_message rm
                    join rm_group_mailbox rgm on rm.group_mailbox_id = rgm.group_mailbox_id 
                    join rm_log rml on rm.message_id = rml.message_id
                    where 
                    rm.message_status_id = 4 and 
                    rm.group_mailbox_id in ({1}) and 
                    rm.thread_id is not null and 
                    rm.assigned_user_id is null and 
                    rm.is_auto_assignable = 1
                    group by rgm.name,rm.message_id
                ) a
                group by name
EOQ
 
            @queries["Unassigned"] = Hash.new
            @queries["Unassigned"]["header"] = "Unassigned Messages"
            @queries["Unassigned"]["mapping"] = {"mailboxids" => "{1}"}
            @queries["Unassigned"]["query"] = <<EOQ
                select Mailbox,Count from
                (
                select case when rgm.[name] is null then 1 else 0 end as Sort,
                convert(varchar,isnull(rgm.[name],'Total')) [Mailbox],count(*) [Count]
                from rm_message rm,rm_group_mailbox rgm 
                where 
                    rm.message_status_id = 4 and 
                    rm.group_mailbox_id in ({1}) and 
                    rm.thread_id is not null and 
                    rm.assigned_user_id is null and 
                    rm.is_auto_assignable = 1 and
                    rm.group_mailbox_id = rgm.group_mailbox_id 
                group by rgm.[name] with rollup
                ) a
                order by sort, Mailbox                                                       
EOQ
            @queries["PerBoxPerDay"] = Hash.new
            @queries["PerBoxPerDay"]["header"] = "Backlog Summary Per Box Per Day"
            @queries["PerBoxPerDay"]["mapping"] = {"mailboxids" => "{1}"}
            @queries["PerBoxPerDay"]["query"] = <<EOQ
                select convert(varchar,isnull(Mailbox,'')) Mailbox,isnull(Date,'Total') Date,[Count] from
                (
                    select case when rgm.[friendly_name] is null then 1 else 0 end as Sort,
                    rgm.friendly_name as Mailbox,Convert(varchar,[time],111) as [Date],count(*) as [Count]
                    from rm_log rml,rm_group_mailbox rgm 
                    where log_id in 
                    (
                        select max(log_id) as log_id from rm_log where message_id in 
                        (
                            select message_id
                            from rm_message
                            where message_status_id = 4 and assigned_user_id is null and group_mailbox_id in ({1}) and is_auto_assignable = 1
                        )
                        and activity_type_id = 1 and message_type_id = 1
                        group by message_id
                    )
                    and rml.group_mailbox_id = rgm.group_mailbox_id
                    group by rgm.friendly_name ,Convert(varchar,[time],111) with rollup
                ) a
                where not (Mailbox is not null and [Date] is null)
                order by Sort,[Date]
EOQ
            @queries["OldMessages"] = Hash.new
            @queries["OldMessages"]["header"] = "Backlog Detail of Older Messages"
            @queries["OldMessages"]["mapping"] = {"mailboxids" => "{1}","cutoff" => "{2}"}
            @queries["OldMessages"]["query"] = <<EOQ
                select convert(varchar,rgm.friendly_name),convert(varchar,[Time],120) as [Time],thread_id
                from rm_log rml,rm_group_mailbox rgm 
                where log_id in 
                (
                    select max(log_id) as log_id from rm_log where message_id in 
                    (
                        select message_id
                        from rm_message
                        where message_status_id = 4 and assigned_user_id is null and group_mailbox_id in ({1})  and is_auto_assignable = 1
                    )
                    and activity_type_id = 1 and message_type_id = 1
                    group by message_id
                )
                and rml.group_mailbox_id = rgm.group_mailbox_id
                and [Time] < getdate() - {2}
                order by [Time]
EOQ
            @queries["TurnAround"] = Hash.new
            @queries["TurnAround"]["header"] = "Turn Around Time Detail"
            @queries["TurnAround"]["mapping"] = {"date" => "{1}"}
            @queries["TurnAround"]["query"] = <<EOQ
                select convert(varchar,ms_user.name) Username,replied.thread_id ThreadID,assigned.time Assigned,replied.time Replied
                from rm_log replied
                join ms_user on replied.user_id = ms_user.user_id
                join 
                (
                    select rm_log.message_id,rm_log.time 
                    from rm_log 
                    join
                    (
                        select 
                        max(log_id) log_id
                        from rm_log
                        where    
                        activity_type_id = 3
                        and message_type_id = 1
                        and new_user_id is not null
                        and message_id = message_id
                        and time >= getdate()-7
                        group by message_id
                    ) t on rm_log.log_id = t.log_id
                ) assigned on replied.original_message_id = assigned.message_id
                where 
                convert(varchar,replied.time,111) = convert(varchar,'{1}',111)
                and replied.activity_type_id = 4
                and replied.message_type_id = 2
                order by name,assigned.time
EOQ
 
        end
 
        def mkSql(report,params)
            @log.debug("mkSql(#{report},#{params.inspect})")
            sql = String.new(@queries[report]["query"]) #If you don't create a copy, it modifies the class variable
            @queries[report]["mapping"].each_pair do |src,dest|
                sql.gsub!(dest,params[src].to_s) #Apply the mapping
            end
            @log.debug("#{sql}")
            return sql
        end
 
        def run            
            begin
                runTime = ARGV[0].nil? ? Time.now : Time.parse(ARGV[0])
                @accounts.each_pair do |account,params|
                    if not params["enabled"].nil? and params["enabled"] then                    
                        params["times"].split(",").each do |time|
                            delta = runTime - Time.parse(time)
                            if delta >= 0 and delta <= 120 then #2 minute tolerance for job to run
                                begin
                                    DBI::connect(params["connstr"]) do |dbh| #Connect to database
                                        output = ""
                                        params["reports"].split(",").each do |report| #Cycle thru each report
                                            @log.info("Processing #{account} - #{report}")
                                            output << "#{@queries[report]['header']}\n"
                                            sth = dbh.execute(mkSql(report,params))
                                            rows = sth.fetch_all
                                            if rows.length > 0 then
                                                col_names = sth.column_names                                        
                                                DBI::Utils::TableFormatter.ascii(col_names,rows,:left,:right,0,0,nil,output)
                                                output << "\n"                                        
                                            else                                        
                                                output << "Nothing to report today\n\n"
                                            end
                                            sth.finish                                    
                                        end
                                        @log.info("Emailing report for #{account}")
                                        sendmail("Backlog@#{account}",$globals[:alwaysnotify].split(",") + params["notify"].split(","),"Daily Backlog Report",output)                                    
                                    end
                                rescue => e
                                    @log.error(e)
                                    sendmail("Error@Backlog",$globals[:alwaysnotify].split(","),"Error running report",e)
                                end
                            end
                        end                
                    end
                end
            rescue => e
                @log.error(e)
                sendmail("Error@Backlog",$globals[:alwaysnotify].split(","),"Error running report",e)
            ensure
                @log.info("Done")
            end
        end
 
        def to_s
            s = ""
            @queries.each_pair do |key,value|
                s << sprintf("%s:\n%s\n",key,value)
            end        
            return s            
        end
    end
end
 
if __FILE__ == $0 then
    accounts = Hash.new
    accounts["Sample"] = Hash.new
    accounts["Sample"]["enabled"] = true
    accounts["Sample"]["connstr"] = "DBI:ADO:Provider=SQLOLEDB;Data Source=DBServer1;Initial Catalog=dbSample;User ID=Sample;Password=Sample123"
    accounts["Sample"]["hours"] = 24
    accounts["Sample"]["cutoff"] = accounts["Sample"]["hours"].to_f/24
    accounts["Sample"]["notify"] = "[e-mail],[e-mail]"
    accounts["Sample"]["mailboxids"] = "12,14,34,36,38,40,42,44,46"
    accounts["Sample"]["reports"] = "PerBoxPerDay,OldMessages"
    accounts["Sample"]["times"] = "0:00"
 
    backlog = ERM::Backlog.new(accounts)
    backlog.run
end