Automate a nightly query and email results...

Hi,
I was wondering if someone could help with my latest project.
I need to run a nightly query on our MS SQL 2000 DB, have the results saved
to a text file and then email the file to someone. I have no idea were to
begin with this one...help?
Thanks,
MitchYou can create a Data Transformation Services package to create the file
based on the query and then email the file. Schedule the package to run every
night.
Data Transformation Services (DTS) in
Microsoft SQL Server 2000
http://msdn.microsoft.com/sql/sqlwarehouse/dts/default.aspx?pull=/library/en-us/dnsql2k/html/dts_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_5zg3.asp?frame=true
AMB
"mitch" wrote:
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results saved
> to a text file and then email the file to someone. I have no idea were to
> begin with this one...help?
> Thanks,
> Mitch
>
>|||I would do this using a DTS package or using BCP.
For example, let's go with a DTS package:
In the package, you will add an SQL Server source, and flat file destination
connections. You add a data pump task that connects these two connections,
and pumps out your query results to the flat file.
Then the next step will send an email by specifying the file name as the
attachment.
For sending emails, I use xp_smtp_sendmail, which is a free download from
http://sqldev.net
You could also use SQL Mail. See SQL Server Books Online for more
information.
if you are not familiar with DTS, start with Books Online, and play around
with it from the DTS designer in Enterprise manager. For DTS info, checkout
sqldts.com.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I was wondering if someone could help with my latest project.
> I need to run a nightly query on our MS SQL 2000 DB, have the results
> saved to a text file and then email the file to someone. I have no idea
> were to begin with this one...help?
> Thanks,
> Mitch
>|||Hi,
If u wanted to transfer all data to a text file then use bcp utility
ex:
exec master..xp_cmdshell 'bcp epinav.dbo.dlvmode out
D:\scriptrecordtable\dlvmode.bcp -n -"EPIOLAPP428G" -U -P'
epiolapp428g is the servername
for more help read books online just type BCP
if u wanted selected data then create a view and tranfer data from
view.
with the same as above.
for sending mail i refer to vyas solution.
to copy data from text file to ur sql table
use this
exec master..xp_cmdshell 'bcp epinav.dbo.custtable in
E:\RepScript\custtable.bcp -n -"aicml370" -U -P'
hope this help
from
killer|||Thanks everyone!!!
I started playing around with DTS and was able to setup a query and save the
info to a text file, but now I have 2 more question.
The information is saved in the text file row by row, BUT it is inserting a
space between each row, is there some way to remove the space?
Also, when a record is submitted it has to follow a YYYYMMDD format so I
placed a hidden text field in the form like this one:
<input name="source_date" type="hidden" id="source_date" value="<%
response.write YEAR(Date()) & _
Pd(Month(date()),2) & _
Pd(DAY(date()),2)
%>">
I am having some trouble in the query that runs in DTS to automatically
capture today's date and use it in the WHERE clause:
where [request_info_form].[source_date]='20050113'
Can you guys help me think of a way?
Thank you very much for all the help!
Mitch
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%238YBLiSnFHA.2156@.TK2MSFTNGP14.phx.gbl...
>I would do this using a DTS package or using BCP.
> For example, let's go with a DTS package:
> In the package, you will add an SQL Server source, and flat file
> destination connections. You add a data pump task that connects these two
> connections, and pumps out your query results to the flat file.
> Then the next step will send an email by specifying the file name as the
> attachment.
> For sending emails, I use xp_smtp_sendmail, which is a free download from
> http://sqldev.net
> You could also use SQL Mail. See SQL Server Books Online for more
> information.
> if you are not familiar with DTS, start with Books Online, and play around
> with it from the DTS designer in Enterprise manager. For DTS info,
> checkout sqldts.com.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "mitch" <mitch_001@.REMOVEyahoo.com> wrote in message
> news:eUiUKWSnFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I was wondering if someone could help with my latest project.
>> I need to run a nightly query on our MS SQL 2000 DB, have the results
>> saved to a text file and then email the file to someone. I have no idea
>> were to begin with this one...help?
>> Thanks,
>> Mitch
>|||Something like this should work...
select @.yourdate = (select convert(CHAR(8),getdate(),112))
This puts things in yyyymmdd format.|||Works perfectly!!! Thanks!
<unc27932@.yahoo.com> wrote in message
news:1123689630.540990.188690@.z14g2000cwz.googlegroups.com...
> Something like this should work...
> select @.yourdate = (select convert(CHAR(8),getdate(),112))
> This puts things in yyyymmdd format.
>