• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1192
  • Last Modified:

sp_send_dbmail @Query with Select statement Questons

General info:  I have a table that has a column type of DateTime (fdUpdateDate) and I am trying to send and error e-mail and showing the rows in this table that match a parameter of @DateTime that was set to the current date earlies in the SQL code.  However, I am having problems with the @query statement recognizing the @DateTime as a parameter and not a sring or at least I think that is what is wrong. I have tried the below with the following errors:
exec msdb..sp_send_dbmail  
@profile_name = 'Mail Test',
@recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@copy_recipients = '7192428383@cingularme.com',
@query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + @DateTime ,
@Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'

Error:  Doesnt compile
Msg 102, Level 15, State 1, Procedure sp_NumOfFiles_Directories, Line 182
Incorrect syntax near '+'.

Then I tried this as well with the + @DateTime inside the quotes.
exec msdb..sp_send_dbmail
@profile_name = 'Mail Test',
@recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@copy_recipients = '7192428324@cingularme.com',
@query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate = + @DateTime',  @Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'

Errors:  
(6 row(s) affected)
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 137, Level 15, State 2, Server WICDTL01DM3295, Line 1
Must declare the scalar variable "@DateTime".
(1 row(s) affected)
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

NOTE:  I have changed the number in the pager for text to a dummy number because this will be on the internet.
How can I get this to only select the rows that match my date parameter?

0
dm3295
Asked:
dm3295
  • 2
1 Solution
 
twoboatsCommented:
'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar)
0
 
twoboatsCommented:
You have to make the datetime type a string type to concatenate it.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now