?
Solved

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 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