How can i automate the emailing of the result set as a file.

I want to create a job that runs a stored procedure and
present the result set in txt. The administrator has to be
informed of sucess and also the result must be emailed to
the administrator. This will be happening on weekly basis.
I have created the stored procedure and the job that
informs the Administrator. The problem is how can i
automate the emailing of the result set as a file with the
alerting after running the job. Please help.What about something like
master..xp_sendmail
@.recipients = 'Allan Mitchell',
@.query = 'Exec byRoyalty 100',
@.dbuse = 'pubs',
@.attach_results = 'true'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Naz" <milnaz@.hotmail.com> wrote in message
news:3e9c01c37c2f$ca20f310$a501280a@.phx.gbl...
> I want to create a job that runs a stored procedure and
> present the result set in txt. The administrator has to be
> informed of sucess and also the result must be emailed to
> the administrator. This will be happening on weekly basis.
> I have created the stored procedure and the job that
> informs the Administrator. The problem is how can i
> automate the emailing of the result set as a file with the
> alerting after running the job. Please help.

How a user can execute SQL code

I am currently using DTS to run a query that spits out a txt file of the
results - works great, BUT... this code will now need to be run on a fairly
regular basis (possibly each Fri or Saturday). Although I am familiar with
coding, I am not familiar with having users interface with this code. My
question is, how can I have a user have the ability from his/her desktop to
click a button in some program and run this code? The Query is to a Project
Server database extracting data to be imported elsewhere. I though of
Access, but I do not believe Access can access mdf. Any ideas of what I can
use (I do not have Oracle).create a DTS package and try to Execute the package
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Diane" wrote:

> I am currently using DTS to run a query that spits out a txt file of the
> results - works great, BUT... this code will now need to be run on a fairl
y
> regular basis (possibly each Fri or Saturday). Although I am familiar wit
h
> coding, I am not familiar with having users interface with this code. My
> question is, how can I have a user have the ability from his/her desktop t
o
> click a button in some program and run this code? The Query is to a Proje
ct
> Server database extracting data to be imported elsewhere. I though of
> Access, but I do not believe Access can access mdf. Any ideas of what I c
an
> use (I do not have Oracle).|||Since your existing DTS package is working fine, you might consider running
it on the client machine. There are a number of methods to do this. One is
to execute the package using DTSRUN or DTSRUNUI and include the command in a
BAT file for easy user execution. Similarly, you can execute the package
directly from a VBScript or Access application.

> I though of Access, but I do not believe Access can access mdf. Any ideas
> of what I can
> use (I do not have Oracle).
You don't access the SQL Server database files directly in Access. You can
either link needed tables from an ODBC data source or query directly using
ADO in VBA code.
If you don't want to use DTS, you can use virtually any scripting or
programming language that allows you to run a SQL query and create a text
file. The best choice depends on your user interface requirements and
expertise. With Access, you could develop a simple form for any needed
parameters that invokes VBA code to perform the export task.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane" <Diane@.discussions.microsoft.com> wrote in message
news:E799328F-CD41-49F4-AF04-100A1242F70B@.microsoft.com...
>I am currently using DTS to run a query that spits out a txt file of the
> results - works great, BUT... this code will now need to be run on a
> fairly
> regular basis (possibly each Fri or Saturday). Although I am familiar
> with
> coding, I am not familiar with having users interface with this code. My
> question is, how can I have a user have the ability from his/her desktop
> to
> click a button in some program and run this code? The Query is to a
> Project
> Server database extracting data to be imported elsewhere. I though of
> Access, but I do not believe Access can access mdf. Any ideas of what I
> can
> use (I do not have Oracle).|||Yes- I'm a moron ;-( It didn't dawn on me to just load client tools -
thanks!!!!
"Dan Guzman" wrote:

> Since your existing DTS package is working fine, you might consider runnin
g
> it on the client machine. There are a number of methods to do this. One
is
> to execute the package using DTSRUN or DTSRUNUI and include the command in
a
> BAT file for easy user execution. Similarly, you can execute the package
> directly from a VBScript or Access application.
>
> You don't access the SQL Server database files directly in Access. You ca
n
> either link needed tables from an ODBC data source or query directly using
> ADO in VBA code.
> If you don't want to use DTS, you can use virtually any scripting or
> programming language that allows you to run a SQL query and create a text
> file. The best choice depends on your user interface requirements and
> expertise. With Access, you could develop a simple form for any needed
> parameters that invokes VBA code to perform the export task.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane" <Diane@.discussions.microsoft.com> wrote in message
> news:E799328F-CD41-49F4-AF04-100A1242F70B@.microsoft.com...
>
>