SQL Server Agent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Agent

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-20 : 06:56:22

I would like to schedule a job where the contents of a view that I created would be exported to excel and mailed to someone. Is this possible?

I've never tried to use this feature before.

Thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 07:03:59

http://www.techrepublic.com/article/sending-e-mail-with-database-mail-in-sql-server-2005/6164310

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-20 : 08:15:07

Ok I tried that code but am getting errors I probably dont have the rest of this correct. Here is what I have:

USE [pcmon]
GO
/****** Object: StoredProcedure [dbo].[J_SendMail] Script Date: 04/20/2011 07:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[J_SendMail]

--EXEC msdb.dbo.sp_send_dbmail
--@recipients=N'j@m.com',
--@body='Message Body',
--@subject ='Message Subject',
--@profile_name ='Database-mailProfile',
--@file_attachments ='C:\FileAttachment.txt';

EXEC dbo.J_SendMail
@recipients=N'j@m.com',
@body='Message Body',
@sensitivity ='Personal',
@importance ='High',
@copy_recipients =J@M.com',
@subject ='Message Subject',
@profile_name ='Database-mailProfile';

End

I guess I need to create the procedure once and then execute it after that?

Error Im getting:
Msg 102, Level 15, State 1, Procedure J_SendMail, Line 1
Incorrect syntax near 'J_SendMail'.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-04-20 : 08:18:06

you missed a beginning quote for the value of @Copy_Recipients, to start with.. fix that and see if you can get past the error.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-20 : 08:43:54

Ok got past that one thanks. Now Im getting this one:

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server.
(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-20 : 08:49:04

do you have SSRS and SSIS ?

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-20 : 08:54:59

I'm sorry I dont even know what those are.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2011-04-20 : 09:06:18

Which version of SQL Server are you using?

-----------

If debugging is the process of removing software bugs, then programming must be the process of putting them in.

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-20 : 09:21:53

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.5512
Microsoft .NET Framework 2.0.50727.3603
Operating System 5.1.2600

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-21 : 08:11:53

Ok I now have SSRS and SSIS, and SQL Server Agent. Maybe I should forget about the mailing part for now.

All I want to do at this point is export a view to Excel on a recurring schedule i.e. everyday at 6AM.

I see a lot of different methods here but am unsure which to choose.

Thanks

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2011-04-21 : 09:16:54

You probably need to use SSIS. I am guessing there is some sort of export wizard. The wizard may have an option to schedule the task (which will make use of the SQL Server Agent)

-----------

If debugging is the process of removing software bugs, then programming must be the process of putting them in.

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-21 : 10:07:14

Ok but I was not able to find that option in any of the menus, I'll look again. Thanks

AmandaKennyEdgar
Starting Member

1 Post

Posted - 2011-04-22 : 01:49:23

AmandaKennyEdgar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources