Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-05-05
4
Medium Priority
?
239 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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

810 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