?
Solved

Syntax error with @Query parameter in e-mail with select statement

Posted on 2007-07-20
5
Medium Priority
?
276 Views
Last Modified: 2008-01-09
Cast question in a @query statment for e-mail.
exec msdb..sp_send_dbmail --@recipients = @copymembers,
 @profile_name = 'Mail Test',
 @recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar),
@Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'
Error:
Incorrect syntax near '+'.
I tried numerous set ups with moving quotes around and put () around entire statement and I can't get the error to go away.   Sorry, I am a beginner and getting frustrated.
0
Comment
Question by:dm3295
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19534891
declare @sql varchar(200)
set @sql = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar)
exec msdb..sp_send_dbmail --@recipients = @copymembers,
 @profile_name = 'Mail Test',
 @recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query = @sql
@Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 19534896
and to be "perfect":


declare @sql varchar(200)
set @sql = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate = convert(datetime, ''' + convert(varchar(10), @DateTime ,120) + ''', 120) '
exec msdb..sp_send_dbmail --@recipients = @copymembers,
 @profile_name = 'Mail Test',
 @recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query = @sql
@Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 19534909
try declaring the @query parameter before the call.

i.e.:

declare @query varchar(250),
             @DateTime datetime
SET @DateTime = getdate();
SET @query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar)
--Now do the call
exec msdb..sp_send_dbmail --@recipients = @copymembers,
 @profile_name = 'Mail Test',
 @recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query,
@Subject = 'Action:  dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19535707
there is no type varchar
there are types varchar(10), varchar(12), etc...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19538170
>there is no type varchar
actually, you can declare are variable like that:
declare @var varchar

which will be the same as
declare @var varchar(1)
...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question