DBMAIL / Query (SP) using Parameters

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

SQL Server Administration (2000)

DBMAIL / Query (SP) using Parameters

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-10-17 : 13:11:20

I need to have an email sent (SS 2005x64), attaching a query to the email. The query is a stored proc, very similar to this thread's question.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6603&SearchTerms=Mail,query,param

however my parameter needs to be the result of getdate().. something like this -


declare @f as varchar(1000)
declare @t as datetime
set @t = getdate()
set @f = 'exec vmfg.dbo.tsp_ProjectsShippedArchive ' + @t

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'SQL Administrator',
  @recipients = 'me@me.com',
   @subject = 'Projects to Archive',
   @body = 'hi',
   @query = @f,
   @attach_query_result_as_file = 1

The error I'm getting is : Conversion failed when converting datetime from character string. [SQLSTATE 22007] (Error 241) - I've tried a number of possible work arounds and calling getdate() in different places. Such as


set @f = 'exec vmfg.dbo.tsp_ProjectsShippedArchive ' + getdate()

I know the db mail works as I use it for many other emails, any ideas? Thanks,

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 14:52:37

Try putting quotes around the date in the procedure call line like this

set @f = 'exec vmfg.dbo.tsp_ProjectsShippedArchive ''' + @t + ''''

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-10-25 : 09:37:19

That, along with changing @t to a varchar seemed to do the trick. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources