emails

Question

Post reply

emails

Mike Levan

SSChampion

Points: 14201
More actions
October 30, 2007 at 6:27 am

#179670

I have a store proc which i want to make it as a sql job where in if it returns 0 rows then it has to kick another proc else if it returns say 10 rows then it has to be sent to my email id with those result set or as an attachment(excel file) to my email id.
I think we can do this through sql job, but how can i send attachement or content to my email?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

okhere SSC-Addicted Points: 443 More actions · Answer 1

Hi Mike,

You can include it all in an SSIS package.

Osama

Mike Levan SSChampion Points: 14201 More actions · Answer 2

Osama

do u mean I cant do that in DTS, is it possible only in sql 2005

okhere SSC-Addicted Points: 443 More actions · Answer 3

Yes you can do that in DTS.

Create a new DTS and use the SQL Task for the SQL, email task for teh email, etc.

Osama

Mike Levan SSChampion Points: 14201 More actions · Answer 4

thanks,I wud like to know how can i show my result set of store proc as an attachemnt in the email

okhere SSC-Addicted Points: 443 More actions · Answer 5

Hi Mike,

Try creating a DTS package with an SQL Task that executes a stored procedure followed by an email task that send email with any attachment (for now not the output of the stored procedure).

Save the package as Visual Basic format adn open it with a text editor.

Import it into a Visual Basic project and enhance to get what you want.

Thanks

Osama

Mike Levan SSChampion Points: 14201 More actions · Answer 6

I get the following error message when i try to click on email task in DTS designer 2000

Error Source : Micorsoft Data transaformation Services (DTS) Package

Error DEscription : Cannot Load MAPI interface layer for DTS. PLese make sure that semmap90.dll is installed

Janet Keith-489067 Ten Centuries Points: 1052 More actions · Answer 7

/*

I use smtp mail. I find this method far simpler that DTS.

Export the data to a text file using osql. Attach the text file to the email and send.

*/

declare @cmd varchar(1000)

select @cmd = 'osql -E -S"[SRVR]" -d servername -Q"select * from mytable" -o"\\servername\c$\audit\filename.txt" -w8000'

exec master..xp_cmdshell @cmd

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail

@FROM = N'SQL.mail@somewhere.com',

@FROM_NAME ="[SRVR]" ,

@TO = N'janet.keith@somewhere.com',

@priority = N'Normal',

@subject = N'My Aduit Report',

@message = N'My Audit Report',

@server = N'My Relay server.Net',

@attachment = '\\servername\c$\audit\filename.txt'

select RC = @rc

Mike Levan SSChampion Points: 14201 More actions · Answer 8

how can i export my store proc result set into a text file first?

Janet Keith-489067 Ten Centuries Points: 1052 More actions · Answer 9

Here's a stored procedure I found awhile back that easily write a text string to a file.

CREATE PROCEDURE spWriteStringToFile

(

@String Varchar(8000), --8000 in SQL Server 2000

@Path VARCHAR(255),

@Filename VARCHAR(100)

--

)

AS

DECLARE @objFileSystem int

,@objTextStream int,

@objErrorObject int,

@strErrorMessage Varchar(1000),

@Command varchar(1000),

@hr int,

@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem, 'CreateTextFile'

, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,

@strErrorMessage='writing to the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0

begin

Declare

@Source varchar(255),

@Description Varchar(255),

@Helpfile Varchar(255),

@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,

@source output,@Description output,@Helpfile output,@HelpID output

Select @strErrorMessage='Error whilst '

+coalesce(@strErrorMessage,'doing something')

+', '+coalesce(@Description,'')

raiserror (@strErrorMessage,16,1)

end

EXECUTE sp_OADestroy @objTextStream

GO

ed.nettestad SSC-Addicted Points: 480 More actions · Answer 10

Have you thought about using sp_send_dbmail. It has lots of parameters. You have the ability to run a sql script as a parameter and attach the output as an attachment. This sure is a lot easier than DTS.

Ed

Mike Levan SSChampion Points: 14201 More actions · Answer 11

I dont have that proc in msdb Db, coz its sql 2000. If you can copy the proc here i ca just create it in my 2000 and try working on it.

thanks

ed.nettestad SSC-Addicted Points: 480 More actions · Answer 12

In that case use xp_sendmail. Here is a link to it's format.

http://msdn2.microsoft.com/en-us/library/ms189505.aspx

Ed