Solved

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

Posted on 2006-10-24
12
847 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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