Solved

Email SQL Query Results but referencing the code file instead of inserting the code

Posted on 2009-05-05
4
226 Views
Last Modified: 2012-05-06
I've created a SQL query (c:\myscript.sql) which is fairly complex.  I'd like to email the results of that query each night.

I've created a job that uses the code below.  The code below has the SQL inserted into it.  I'd like to reference the file I created so that I can update that file as needed and not have to worry about going into this job and updating in there as well.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domainl.com',@body='Message Body', 
@subject ='Message Subject',@profile_name ='DatabaseMailProfile',@query ='SELECT Name FROM Members', 
@attach_query_result_as_file = 0

Open in new window

0
Comment
Question by:bsimms01
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:mgmanoj
ID: 24310863
In Query parameter call osql with help of xp_cmdshell and with that osql you can use your script file to  create the output file and instead of attaching query result - attach a output file with file_attachment parameter as query output will have osql output it will be fixed file output from osql to attach to your email and it should work.
0
 

Author Comment

by:bsimms01
ID: 24328469
You'll need to break that down for me.  I'm new to this.

Call OSQL with help of xp_cmdshell?

What if I want to include the results in the email instead of attaching the results through an attachment?
0
 
LVL 3

Accepted Solution

by:
mgmanoj earned 250 total points
ID: 24343900
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domainl.com',@body='Message Body',
@subject ='Message Subject',@profile_name ='DatabaseMailProfile',
@query ='Exec master..xp_cmdshell "osql /S servername /E /ic:\script.sql /o c:\script.out" ',
@file_attachment = 'c:\script.out'

Check the correct osql parameters and file attachment variablename in books online and try it.  you can not place result in email as output will be for cmdshell here as you want to execute the script residing in file - this is very exceptional case when someone want to place query in file  -if you want result in email  than you have to create stored procedure in database and execute that like your select  command and you can place result as you like.
0
 

Author Comment

by:bsimms01
ID: 24484255
mqmanoj - i like the idea of a stored procedure.  i'm going to look more into that.  do you have any links that you'd recommend i check out?  i'll google it and see what i come up with.
0

Featured Post

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

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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