bsimms01
asked on
Email SQL Query Results but referencing the code file instead of inserting the code
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.
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
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.