email query statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

email query statement

Author

Topic

badpig521
Starting Member

14 Posts

Posted - 2008-12-12 : 16:21:38

I have this statement to email query results

IF EXISTS ( SELECT ActiveRecords.FirstName + ' ' + CAST(ActiveRecords.LastName AS VARCHAR) AS Name,
Division.Division,
DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month
FROM ActiveRecords
INNER JOIN ( SELECT Number
FROM Numbers AS Numbers_1
WHERE ( Number BETWEEN 0 AND 120 )
) AS numbers ON DATEADD(yy, numbers.Number,
ActiveRecords.Birthdate) >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
AND DATEADD(yy, numbers.Number,
ActiveRecords.Birthdate) < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)
INNER JOIN Division ON ActiveRecords.DivisionID = Division.DivisionID )
BEGIN

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'dysv04',
@recipients = 'email@email.com',
@query = 'USE Employee_Data
SELECT ActiveRecords.FirstName+' '+CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month
FROM ActiveRecords INNER JOIN
(SELECT Number
FROM Numbers AS Numbers_1
WHERE (Number BETWEEN 0 AND 120)) AS numbers ON DATEADD(yy, numbers.Number, ActiveRecords.Birthdate) >= DATEADD(mm,
DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(yy, numbers.Number, ActiveRecords.Birthdate) < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)
INNER JOIN
Division ON ActiveRecords.DivisionID = Division.DivisionID
ORDER BY DAY(ActiveRecords.Birthdate)',
@Subject = 'Employee Birthdays for this MONTH',
@attach_query_result_as_file = 1 ;
END

But, I get this error. what Am I missing?

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month
F'.

badpig521
Starting Member

14 Posts

Posted - 2008-12-12 : 19:40:02

Here is the solution to my problem. I used this:

SELECT ActiveRecords.FirstName+ space(1) +CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

instead of this

SELECT ActiveRecords.FirstName+' '+CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:31:54

quote:
Originally posted by badpig521

Here is the solution to my problem. I used this:

SELECT ActiveRecords.FirstName+ space(1) +CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

instead of this

SELECT ActiveRecords.FirstName+' '+CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

always remember to specify a length while casting to varchar

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

badpig521
Starting Member

14 Posts

Posted - 2008-12-15 : 08:22:10

quote:
Originally posted by visakh16

quote:
Originally posted by badpig521

Here is the solution to my problem. I used this:

SELECT ActiveRecords.FirstName+ space(1) +CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

instead of this

SELECT ActiveRecords.FirstName+' '+CAST(ActiveRecords.LastName AS VARCHAR) AS Name, Division.Division, DATEPART(dd, ActiveRecords.Birthdate) AS Day_of_Month

always remember to specify a length while casting to varchar

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Good Point! Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 09:01:39

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources