Solved

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

Posted on 2009-05-05
4
219 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

20 Experts available now in Live!

Get 1:1 Help Now