Solved

How do I automate a nightly sql script to output results to a user?

Posted on 2006-10-24
12
848 Views
Last Modified: 2008-01-09
hi all, anyone have an idea on how to send output from a sql statement to a user on a regular basis?  i have a statement (select * from blah where blah) that gives results that are needed by a user on our floor.  This user does not have access to run the query, but she needs the output daily, and management wants the job run automatically, similar to a nightly batch job to run at 11:59 pm to give results for day.

we are amidst development of an actual report that shows this information, so this is only going to be a temporary solution.

any suggestions would be greatly appreciated.  i could probably write the sql as a stored procedure, then have a web page display the results, but that seems like overkill for this task.

thanks.
0
Comment
Question by:aaalife
  • 6
  • 6
12 Comments
 
LVL 9

Expert Comment

by:gpompe
ID: 17796922
You can use this procedure.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from sysobjects where id = object_id('dbo.SP_CDOMail') and sysstat & 0xf = 4)
      drop procedure dbo.SP_CDOMail
GO



CREATE PROCEDURE dbo.SP_CDOMail
      @From varchar(250) ,
      @recipients varchar(250),
      @copy_recipients varchar(250) = null,
      @blind_copy_recipients varchar(250) = null,
      @Subject varchar(100),
      @message varchar(4000)= 'Email Send out successfully',
      @query varchar(1000)= null,
      @dbuse varchar(50) = null,
      @server varchar(50) = null,
      @attach_results varchar(10)='true',
      @Width int=1200
AS

/*****************************************************************************************/
/*    Proc SP_CDOMail                                                                    */
/*                                                                                       */
/*    Send emails via CDO commands to avoid using mail clients on the SQL machines       */
/*                                                                                       */
/*    Parameters:                                                                        */
/*        @from - Email address that the email is being marked as sent from              */
/*        @recipients   - Email address that the email is being sent to                          */
/*        @copy_recipients   - CC's the email is being sent to                                        */
/*        @blind_copy_recipients   - BCC's the email is being sent to                                        */
/*        @subject - Subject of the email                                                */
/*        @message - test of the email, ALL in TEXT format, will send correct type */
/*        @query - optional query that will be run with results attached to email as file */
/*        @dbuse - optional database name to run query in, defaults to current db        */
/*        @server - optional server name where query is to be run, defaults to current server */
/*        @attach_results is additional, due to the requestion of Front-end developer      */
/*        @Width is is additional, due to the requestion of Front-end developer      */
/******************************************************************************************/

SET nocount on

Declare @iMsg int
Declare @hr int
declare @property varchar(255)
declare @errorsource varchar(255)
declare @errordesc varchar(255)
declare @query_internal varchar(2500)
declare @filename varchar(500)
declare @filename_1 varchar(500)
declare @html_ctrl char(1)


if (@message like  '%<html>%' or @message  like  '%<body>%') set @html_ctrl = '1'
      else set @html_ctrl = '0'

EXEC @hr = master.dbo.sp_OACreate 'CDO.Message', @iMsg OUT
if @hr <> 0 begin
      print 'Error in sp_OACreate, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2' -- use remote SMTP server
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 1, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'ikex3.nygard.com'
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 2, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 3, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").value', '10'
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 4, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
if @hr <> 0 begin
      print 'Error in sp_OAMethod, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'To', @recipients
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 5, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'From', @From
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 6, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'CC', @copy_recipients
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 6.5, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'BCC', @blind_copy_recipients
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 6.5.bcc, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'Subject', @Subject
if @hr <> 0 begin
      print 'Error in sp_OASetProperty 7, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

if (@query is not null) begin
      if (@server is null) set @server = @@servername
      if (@dbuse is null) set @dbuse = db_name()
      set @FileName=convert (char(8), getdate( ), 112)+replace(replace(convert (char(10), getdate( ), 114), ':', ''), '-','')
      set @query_internal='osql -E -S'+@server+' -d'+@dbuse+' -Q"'+@query+'" -n  >> D:\'+@Filename+'.txt'
      exec  master..xp_cmdshell @query_internal
      set @FileName_1='D:\'+@FileName+'.txt'
      EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',  NULL, @FileName_1
end


EXEC @hr = sp_OASetProperty @iMsg, 'textbody', @message

if @hr <> 0 begin
      print 'Error in sp_OASetProperty 8, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end

EXEC @hr = master.dbo.sp_OAMethod @iMsg, 'Send', NULL
if @hr <> 0 begin
      print 'Error in send, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
end

EXEC @hr = master.dbo.sp_OADestroy @iMsg
if @hr <> 0 begin
      print 'Error in sp_OADestroy, error is - '
      print @hr
      exec master.dbo.sp_OAGetErrorInfo @iMsg, @errorsource OUT, @errordesc OUT
      print @errorsource
      print @errordesc
      return
end



if (@query is not null) begin
      set @query_internal = 'del ' + @filename_1
      exec  master..xp_cmdshell @query_internal
end


SET nocount off

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
LVL 9

Expert Comment

by:gpompe
ID: 17796944
You can pass a query as parameter and it will send the result in a email

You will need a SMTP server and change the configurations in the proc.
0
 

Author Comment

by:aaalife
ID: 17797218
I'm testing locally as admin on a sql box and get the following errors:

output
Access is denied
null


output
could not find d:\200610241133449.txt
null

=========================================

using the following for params
exec usp_CDOMail
'mwalker@aaalifesales.com',
'mwalker@aaalifesales.com',
null,
null,
'subject',
null,
'select * from anigroup',
'CC70Prod',
'172.16.13.109',
'true',
1200
0
 
LVL 9

Expert Comment

by:gpompe
ID: 17797276
The problem is probably the Windows user has no access to drive d:\ (or is CD drive or something like that)

Look for this block of code (almost at the end) and replace "D:\" for path you can access (read and write access) using xp_cmdshell. Remember this is a local path is the server box . This is a temp folder to store the file with the result.

f (@query is not null) begin
     if (@server is null) set @server = @@servername
     if (@dbuse is null) set @dbuse = db_name()
     set @FileName=convert (char(8), getdate( ), 112)+replace(replace(convert (char(10), getdate( ), 114), ':', ''), '-','')
>     set @query_internal='osql -E -S'+@server+' -d'+@dbuse+' -Q"'+@query+'" -n  >> D:\'+@Filename+'.txt'
     exec  master..xp_cmdshell @query_internal
>     set @FileName_1='D:\'+@FileName+'.txt'
     EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',  NULL, @FileName_1
end


Let me know
0
 

Author Comment

by:aaalife
ID: 17797456
is there another way to accomplish this? something a little less complicated?  sorry, but this is just a select statement that a user would like to see the output from nightly.  i just found out the user can run queries, they must have the client tools of sql installed.

is there a script that could possibly open query analyzer, run the select statement, then save the results in a file for the user on their box?  maybe a scheduled task?
0
 
LVL 9

Expert Comment

by:gpompe
ID: 17797837
With the client installed you can use something like this:

osql -E -SserverName -dDBNAME -Q"Select * from Whatever" -n  >> C:\Results.txt

This is using windows authentication
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:aaalife
ID: 17798558
ok, that is producing results but they're all garbled up, plus there's a message about the table i'm pulling from, yet in query analyzer it's fine.  

can i output to text with decent formatting in the file?


this is statement i ran locally on sql server as admin:

osql -E -sqlg2x1 -agility_test -Q "select * from agility_test.dbo.alarm" -n >> c:\results.txt

i tried with just "select * from alarm" ..., same err message.


output::

Warning: Packetsize size invalid -- using server default.
Msg 208, Level 16, State 1, Server SQLG2X1, Line 1
Invalid object name 'alarm'.
Warning: Packetsize size invalid -- using server default.
AlarmID    qActivityID qRepeatUnit          qRepeatEveryqIsRepeatableq
      EmailNotificationFlagq
      AlarmDescription                                                      
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                      q
      AlarmTime              qEmailPriority       q
      EmailFrom                                         q
      EmailSubject                                                          
                                   q
      EmailBCC                                                              
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
            q
      EmailCC                                                                
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
            q
      EmailTo                                                                
                                                                            
                                                                            
.....                                                                             
                                                                            
                                                                            
                                                                            
0
 
LVL 9

Accepted Solution

by:
gpompe earned 500 total points
ID: 17798667
The reason for the error is there is a missing d in from of the db name.

try this:

osql -E -Sqlg2x1 -dagility_test -Q "select * from agility_test.dbo.alarm" -n -w1000>> c:\results.txt

The issue with the formatting, you have to fix it in the query. Concatenating and  truncating fields.

For example if you have field char(100) that means the columns will be 100 characters width.

What you can do is select Field1 +'   '+fiels2 ... from Table.
0
 
LVL 9

Expert Comment

by:gpompe
ID: 17798687
Another thing you can do is if you have Excel installed, you can create an spreadsheet to display the info. This is easy to do and the info will be updated every time the spreadsheet is open.

If this is an option I can help you to do it. Let me know.
0
 

Author Comment

by:aaalife
ID: 17798901
Thanks so much, that got it!!
0
 

Author Comment

by:aaalife
ID: 17798912
didn't see last post, how can i output to excel?
0
 

Author Comment

by:aaalife
ID: 17799065
i changed the output to result.xls and got the excel version, thanks again for the help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now