Schedule SQL Procs and Emails 1

[cghoga]

I am an anlsyst, not a developer by trade, working in my operations department and I am venturing on a project and I do not know where to get started.

I can code SQL well enough to create a script that will give me the desired result.

The issue is around my access to the tools necessary to do what I need to do.

I would like to create nightly scheduled jobs that email out results to predefined distributions based on queries that I run manually today.

Can someone help get me started on what I need to have or ask for to make this happen?

Do I need special permissions on the server or databases?

I am using SQL Server 2000 and/or 2005.

Any and all recommendations are appreciated.

I am not asking for someone to do this for me.

Maybe someone has lessons learned that they can share.

Thanks for your time and help!

 

[SQLSister]

I would create a DTS package (in 2000) or an SSIS package (in 2005) that creates the file based on a query and then has another step to email the file to a distribution list as as attachment.

Once the package is created and tested. I would schedule the job. THe SQl Server agent that runs the job will need appropriate permissions to create the outgoing file in the directory you specify and to send emails.

"NOTHING is more important in a database than integrity." ESquared