asked on

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.

gpompe

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

gpompe

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.

aaalife

ASKER

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

gpompe

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

aaalife

ASKER

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?

gpompe

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

aaalife

ASKER

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


.....

ASKER CERTIFIED SOLUTION

gpompe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gpompe

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.

aaalife

ASKER

Thanks so much, that got it!!

aaalife

ASKER

didn't see last post, how can i output to excel?

aaalife

ASKER

i changed the output to result.xls and got the excel version, thanks again for the help!