Need Variable Pass to XP_SENDMAIL

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need Variable Pass to XP_SENDMAIL

Author

Topic

barry
Starting Member

14 Posts

Posted - 2006-07-03 : 14:55:24

Using SQL < 1yr

Anyway, I want to use the xp_sendmail procedure to send an email with a file attachment daily where the file name is going to include a "date" tag in the file name. What I was thinking I can not figure away to get this to work.

Here's the code and I'm trying to find a way to get the @attachments to include a variable @date(The + doesn't work and @date = REPLACE (CONVERT(char,getdate (), 1),'/','')
--The end result for what I want is to be able to declare the @date
make the attached file name be \\SERVER\n1\n2\n3\n4\xyz_070306.xls' which would change every day.:

/*
Some how delcare an additional variable (@date) to be used with this procedure
*/

xp_sendmail

@recipients = 'Anyone@World.com',
@subject = 'Sub',
@message = 'Msg',
@attachments = '\\SERVER\n1\n2\n3\n4\xyz_' + '@date' + '.xls'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-03 : 15:06:55

Remove the single quotes around @date:
@attachments = '\\SERVER\n1\n2\n3\n4\xyz_' + @date + '.xls'

If that doesn't work, then you'll need form the @attachments variable first, then pass it to xp_sendmail. Let us know if the above doesn't work and we'll help you out with the next solution.

Tara Kizer
aka tduggan

barry
Starting Member

14 Posts

Posted - 2006-07-03 : 15:32:11

Thanks;Yea I don't want file@date.xls; getting the variable to the stored procedure is still my issue:

Here's basically what I would like to use but get an error:

DECLARE @date char(6),
@atta varchar (255)

SET @date = REPLACE (CONVERT(char,getdate (), 1),'/','')
SET @atta = '\\Snetfilesvr2\csfnp01_files\Client Service\S C I\CatalystMasterLists\SOA_' + @date +'.xls'

xp_sendmail

@recipients = 'Anyone@World.com',
@subject = 'Sub',
@message = 'Msg',
@attachments = @atta

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'xp_sendmail'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-03 : 15:35:39

You need EXEC xp_sendmail.

Tara Kizer
aka tduggan

barry
Starting Member

14 Posts

Posted - 2006-07-03 : 15:43:48

Thanks; Simply put i was being lazy

Normally I run procedures w/o ever putting the EXEC before it would be a good habit for me to start. My variable works thanks for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources