Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

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

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
aaalife
Asked:
aaalife
  • 6
  • 6
1 Solution
 
gpompeCommented:
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
 
gpompeCommented:
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
 
aaalifeAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gpompeCommented:
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
 
aaalifeAuthor Commented:
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
 
gpompeCommented:
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
 
aaalifeAuthor Commented:
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
 
gpompeCommented:
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
 
gpompeCommented:
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
 
aaalifeAuthor Commented:
Thanks so much, that got it!!
0
 
aaalifeAuthor Commented:
didn't see last post, how can i output to excel?
0
 
aaalifeAuthor Commented:
i changed the output to result.xls and got the excel version, thanks again for the help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now