Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-05-05
4
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

609 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