Automatically email query results

I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron?). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?

Thank you for any guidance.

You have a query and you wish execute this query and send the results automatically. If this is what you want you can perform this by SQL jobs..........In the enterprise manager navigate to management and jobs refer,

http://doc.ddart.net/mssql/sql70/automaem_5.htm

http://doc.ddart.net/mssql/sql70/automaem_15.htm

in the 1st step of the job give your T-SQL code and also give the path of the o/p file in advanced options .......in the second step give the mail step and details of the recepients..........|||Maybe it would be also an option for you sending the information using Reporting Services which is also available for SQL Server 2000. It can handle recordset, format them properly and send them using various formats like Excel / Xml / Pdf etc.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Automatically email query results

I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron?). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?

Thank you for any guidance.

You have a query and you wish execute this query and send the results automatically. If this is what you want you can perform this by SQL jobs..........In the enterprise manager navigate to management and jobs refer,

http://doc.ddart.net/mssql/sql70/automaem_5.htm

http://doc.ddart.net/mssql/sql70/automaem_15.htm

in the 1st step of the job give your T-SQL code and also give the path of the o/p file in advanced options .......in the second step give the mail step and details of the recepients..........|||Maybe it would be also an option for you sending the information using Reporting Services which is also available for SQL Server 2000. It can handle recordset, format them properly and send them using various formats like Excel / Xml / Pdf etc.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Automatic Monitoring of Errorlog

Looking for a way to monitor for certain types of errors.
And then when one is found an email/paging of issue would
be sent.Have you looked into whether ALERT can detect the errors you are looking
for?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"KevinP" <anonymous@.discussions.microsoft.com> wrote in message
news:774801c43110$9328d120$a501280a@.phx.gbl...
> Looking for a way to monitor for certain types of errors.
> And then when one is found an email/paging of issue would
> be sent.
>|||>Looking for a way to monitor for certain types of errors.
>And then when one is found an email/paging of issue would
>be sent.
The notification piece is easy, use third party software from NotePage. As far
as I know PageGate will integrate with just about any monitoring application.
PageGate will send out the alerts to pagers or cell phones, you can download an
eval version to play with at http://www.notepage.net/pagegate.htm
Cheyenne

Automatic Monitoring of Errorlog

Looking for a way to monitor for certain types of errors.
And then when one is found an email/paging of issue would
be sent.
Have you looked into whether ALERT can detect the errors you are looking
for?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"KevinP" <anonymous@.discussions.microsoft.com> wrote in message
news:774801c43110$9328d120$a501280a@.phx.gbl...
> Looking for a way to monitor for certain types of errors.
> And then when one is found an email/paging of issue would
> be sent.
>
|||
>Looking for a way to monitor for certain types of errors.
>And then when one is found an email/paging of issue would
>be sent.
The notification piece is easy, use third party software from NotePage. As far
as I know PageGate will integrate with just about any monitoring application.
PageGate will send out the alerts to pagers or cell phones, you can download an
eval version to play with at http://www.notepage.net/pagegate.htm
Cheyenne

Automatic Monitoring of Errorlog

Looking for a way to monitor for certain types of errors.
And then when one is found an email/paging of issue would
be sent.Have you looked into whether ALERT can detect the errors you are looking
for?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"KevinP" <anonymous@.discussions.microsoft.com> wrote in message
news:774801c43110$9328d120$a501280a@.phx.gbl...
> Looking for a way to monitor for certain types of errors.
> And then when one is found an email/paging of issue would
> be sent.
>|||
>Looking for a way to monitor for certain types of errors.
>And then when one is found an email/paging of issue would
>be sent.
The notification piece is easy, use third party software from NotePage. As f
ar
as I know PageGate will integrate with just about any monitoring application
.
PageGate will send out the alerts to pagers or cell phones, you can download
an
eval version to play with at http://www.notepage.net/pagegate.htm
Cheyenne

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email

Dear Freind,
Its all boutt Dts.I have already created a DTSpackage and Activex script in VBscript to retrieve some particular names ...and i want to send those names through E mail.But the email should be automatic(using sql Sheduler ).Real Problem is...How can i write the code to access the DTS object from ASP.Net with VB? and How can shedule...please help me...i am hopefully waiting..........thanks in advanceIf you just want to send the mails queried from a table you can use the regular mail sending procedures from SQL Server like xp_sendmail (depends on your SQL Server version which to use)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Moving to the "SQL Server Integration Services" forum.

Automated email

Hi,

I want to send automated emails. I want to send emails to some persons in the table at the end of the day like 10.00 pm every day. How do I do that?
I am using sql server 2000 and asp.net.

Thanks in advance for the reply.Use the following syntax to send email
SendEMail 'softdev@.knoahindia.com','prasad@.knoahindia.com', 'Sending email through Stored Procedure', 'Body : Giving TO, FROM, SUBJECT, BODY, SMTP - Address - 192.168.0.5, An Email can be sent through stored procedure... and this is SAMPLE', '192.168.0.5'

Here is the storedprocedure
Create Procedure dbo.SendEmail
@.To VarChar(500), @.From VarChar(100), @.Subject VarChar(200), @.Body VarChar(1000), @.SMTPServer VarChar(50)
AS
Declare @.Object int, @.Hr int, @.Src VarChar(255), @.Desc VarChar(255)
--Create Message object
Exec @.Hr = sp_OACreate 'CDO.Message', @.Object OUT
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object, @.Src OUT, @.Desc OUT
SELECT hr=convert(varbinary(4),@.Hr), Source=@.Src, Description=@.Desc
Return
End
--set sEndusing property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sEndusing")', '2'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set port property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/SEndUsingMethod")', '25'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set smtp server property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @.SMTPServer
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--Update the configuration
Exec @.Hr = sp_OAMethod @.Object, 'Configuration.Fields.Update', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set TO property
Exec @.Hr = sp_OASetProperty @.Object, 'To', @.To
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set FROM property
Exec @.Hr = sp_OASetProperty @.Object, 'From', @.From
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set Subject property
Exec @.Hr = sp_OASetProperty @.Object, 'Subject', @.Subject
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set HTMLBody property
Exec @.Hr = sp_OASetProperty @.Object, 'HTMLBody', @.Body
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--SEnd the mail
Exec @.Hr = sp_OAMethod @.Object, 'SEnd', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--No memory leaks!
Exec @.Hr = sp_OADestroy @.Object
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End|||

Quote:

Originally Posted by prasadgelli

Use the following syntax to send email
SendEMail 'softdev@.knoahindia.com','prasad@.knoahindia.com', 'Sending email through Stored Procedure', 'Body : Giving TO, FROM, SUBJECT, BODY, SMTP - Address - 192.168.0.5, An Email can be sent through stored procedure... and this is SAMPLE', '192.168.0.5'

Here is the storedprocedure
Create Procedure dbo.SendEmail
@.To VarChar(500), @.From VarChar(100), @.Subject VarChar(200), @.Body VarChar(1000), @.SMTPServer VarChar(50)
AS
Declare @.Object int, @.Hr int, @.Src VarChar(255), @.Desc VarChar(255)
--Create Message object
Exec @.Hr = sp_OACreate 'CDO.Message', @.Object OUT
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object, @.Src OUT, @.Desc OUT
SELECT hr=convert(varbinary(4),@.Hr), Source=@.Src, Description=@.Desc
Return
End
--set sEndusing property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sEndusing")', '2'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set port property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/SEndUsingMethod")', '25'
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set smtp server property
Exec @.Hr = sp_OASetProperty @.Object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @.SMTPServer
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--Update the configuration
Exec @.Hr = sp_OAMethod @.Object, 'Configuration.Fields.Update', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set TO property
Exec @.Hr = sp_OASetProperty @.Object, 'To', @.To
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set FROM property
Exec @.Hr = sp_OASetProperty @.Object, 'From', @.From
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set Subject property
Exec @.Hr = sp_OASetProperty @.Object, 'Subject', @.Subject
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--set HTMLBody property
Exec @.Hr = sp_OASetProperty @.Object, 'HTMLBody', @.Body
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--SEnd the mail
Exec @.Hr = sp_OAMethod @.Object, 'SEnd', Null
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End
--No memory leaks!
Exec @.Hr = sp_OADestroy @.Object
If @.Hr <> 0
Begin
Exec sp_OAGetErrorInfo @.Object
Return
End

hi,
i am shahnawaz
i use this stored procedure but it give error
"The "SendUsing" configuration value is invalid. "
how can i resolve this problem .

Automated birthday email

I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...
How can I automate this?

You can use xp_sendmail to send the birthday emails. Please follow these steps to configure:

1. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Surface Area Configuration'

2. Click 'Surface Configuration for Features'

3. Select the SQL2005 instance (MSSQLSERVER in my testing); explore 'Database Engine'->go to 'OLE Automation'-> select the 'Enable OLE Automation' checkbox; enable SQL Mail in the same way; Click 'OK' to save changes (seeServerConfig.jpg)

4. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Configuration Manager'

5. Go to 'SQL Server 2005 Services, double click on 'SQL Server (InstanceName)', in the 'Log On' panel, choose 'This account' to use a domain account to logon (fareast\t-leijie in my testing); Click 'OK' to save change (see ConfigLogonAccount.jpg)

6. Restart SQL2005 instance service.

7. Install a default mail client if you do not have yet (OutLook is good), make a mail profile ('iori' in my testing) using the domain account to send emails, make sure emails can be sent using successful the account.

8. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'SQL Server Management Studio'

9. Choose your instance, explore 'Management'->'Legacy'->'SQL Mail', double click to set Properties, choose the profile used for SQLMail; Click 'OK' to save change.

And you also need a continous running script to detect the "birthdate" column and send mail, such as:

DECLARE @.email varchar(100)
WHILE(1=1)
BEGIN
If exists(select email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
BEGIN
select @.email=email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = N'Happy Birthday my dear firend!';
END
END

|||Thanks for the explanation!

You made such a detailed list and still I don't completely understand it...;)

A few more questions:

1. I want the script to run once every day at 05:00 AM (to keep db load to a minimum)
2. I want to use a format in HTML for my message, this must contain something like: dear <name>, we like to congratlate you with your birthday. Also this entire email must be made up nicely with tables and pictures (which can be just static). Should I just put my entire email in 1 string and replace the <name> part with the username or can I import a .html file and THEN replace the <name> tag with the username? I like the second solution better...but do you know how to achieve this?

Wow, long questions...:p...hope you can help me out!

Thanks!|||It's my pleasure to discuss with you:)

1. You can schedule a job at 05:00 AM every day to accompish this, just open Enterprise Manager, explorerMySQLInstance->'Management'->'SQL Server Agent'->right click 'Jobs'->'New Job', then go to 'Steps' pannel to new a step. Choose 'T-SQL' as step type, and copy your T-SQL script used for sending email to the 'Command' field (as you'll schedule the script to run once a day, let's remove the 'WHILE(1=1)' loop). Then go to 'Schedules' pannel to schedule your job step. You can press F1 for detailed help. Sorry I wrote a wrong script in my previous post, for the case that there may be more than 2 persons have same birhday:

declare @.email varchar(100), @.name varchar(100)

select email,name into #tbl
from tbl_Birthday
where month(birthdate)=month(getdate())
and day(birthdate)=day(getdate())
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
WHILE (@.email is not null)
BEGIN
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = @.name,
@.attachments='c:\happyBirth.html'--you have use attachment if you want HTML feature
DELETE FROM #tbl whereemail=@.email
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
END
DROP TABLE #tbl

2. See the script above. You can customize the email message by adding the name, but need attchments to add HTML feature.

Automate Row Update and send email?

I'd like to update rows matching a certain criteria each night automatically and then send an email.

I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don't see how to schedule one to run (other than at startup) and I'm not sure if the GETDATE function can be called.

Thanks for any help.

Joewindows service or a scheduled task.

sql -> a scheduled job.

It's not some procedure you can create or a function. You have to set it up on the main service to execute.|||create a SQL Server Agent Job to run the stored proc on a schedule...|||I have the same situation.
I am using Access, how can I do this?
Create a .vbs and schedule it with scheduled tasks?
I tried but i get an error in the first Dim statement.|||Thanks for the help. I ended up just creating another asp.net page to take care of it. Then I just run it as a scheduled task and kill it. I haven't learned enough about stored procedure to do it the otherway yet.

Joe

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 ever
y
night.
Data Transformation Services (DTS) in
Microsoft SQL Server 2000
http://msdn.microsoft.com/sql/sqlwa...ts_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/d...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 save
d
> 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...
>|||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.
>

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,
Mitch
You 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/sqlwar...s_overview.asp
Data Transformation Services
http://msdn.microsoft.com/library/de...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...
>
|||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.googlegr oups.com...
> Something like this should work...
> select @.yourdate = (select convert(CHAR(8),getdate(),112))
> This puts things in yyyymmdd format.
>

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.
>

Auto generating an email

Hi All,

I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)

Is there anyway to achive this via SQL 05 Express?

Thanks,

Mal.

Hi,

why would you need to open a webpage every day? SQL Server's capable of sending emails itself. Another option is to create a console application that can send the mails and have it scheduled with windows task schedular to run at 6am every day.

http://support.microsoft.com/kb/312839/en-us

Grz, Kris.

|||

Hi,

The reason I'd like to open up a web page is because, the template of the data is already there. With little bit of improvisation I would get it to be sending an email.

Actually, I did know (didn't know the code) it is possible to send emails from SQL 2K but not 05 Express.

My intension now is to open up the web page with a process and later kill the process. (with the help of schedular). It is not the best methods around as far as I can see, but hopefully I would be able to implement that quickly.

Thanks for the tip and the link.

Mal.

|||

Hi,

suhash:

My intension now is to open up the web page with a process and later kill the process. (with the help of schedular). It is not the best methods around as far as I can see, but hopefully I would be able to implement that quickly.

create a console application and use theHttpServerUtility.Execute method to execute the webform and capture the rendered html. No need to open a browser etc.

Grz, Kris.

|||

Oh, thanks a lot.

I was thinking to open up a page...

Problem solves thanks to you again,

Cheers.