Hi, im new here, and fairly new to SQL, and hoping someone can help me out with differences in sql results.What im trying to do is pull some data out of a database and email a report, but im seeing a difference in cell contents between sending a plain text email and html email.ie, if i use this query:declare @query nvarchar(max)set @query=N' SELECT user_account, tracker_id ,r_datetime ,l_datetime ,latitude ,longitude' +N' ,gps_valid ,satellite_number ,gsm_signal '+N' FROM gps.dbo.gps_info_last '+N' INNER JOIN gps.dbo.user_tracker_relation on gps.dbo.gps_info_last.sn_imei_id = gps.dbo.user_tracker_relation.sn_imei_id'+N' INNER JOIN gps.dbo.user_info on gps.dbo.user_info.user_id=gps.dbo.user_tracker_relation.user_id'+N' ORDER BY r_datetime ASC' exec msdb.dbo.sp_send_dbmail @profile_name ='sqlemail1', @recipients = '<my email addr>', @subject = 'Last Seen', @query = @query
The results i get in my datetime, latitude and longitude cells are2013-04-23 14:58:46.000 2013-04-23 14:58:50.000 -33.878734999999999 111.80752200000001 which is the format i want it to be inbut trying to use HTML email to make it look nicer,declare @query nvarchar(max)declare @xml nvarchar(max)declare @body nvarchar(max)SET @body ='<html><body><H3>Tracker Last Seen Report</H3>'+'<style type="text/css" >' +'table {border-collapse:collapse; border:1px solid#3399FF; font:10pt verdana; color:black; }'+'table td, table th, table caption { border:1px solid; font-weight=bold; background-color:white; }' +'table th { background-color:#3399FF; font-weight=bold; }' +'</style>'+'<table style="border:1px solid black;">'+'<tr>'+'<th> User </th> '+'<th> Tracker ID </th>'+'<th> Last Received</th>'+'<th> Last Logged </th> '+'<th> Lattitude </th> '+'<th> Longitude </th>'+'<th> GPS Valid </th>'+'<th> No Of Sats </th>'+'<th> GSM Signal </th>'+'</tr>'set @query=N' SELECT user_account, tracker_id ,r_datetime ,l_datetime ,latitude ,longitude' +N' ,gps_valid ,satellite_number ,gsm_signal '+N' FROM gps.dbo.gps_info_last '+N' INNER JOIN gps.dbo.user_tracker_relation on gps.dbo.gps_info_last.sn_imei_id = gps.dbo.user_tracker_relation.sn_imei_id'+N' INNER JOIN gps.dbo.user_info on gps.dbo.user_info.user_id=gps.dbo.user_tracker_relation.user_id'+N' ORDER BY r_datetime ASC'SET @xml = CAST(( SELECT [user_account] AS 'td' ,' ',[tracker_id] AS 'td' ,' ',[r_datetime] AS 'td' ,' ',[l_datetime] AS 'td' ,' ',[latitude] AS 'td' ,' ',[longitude] AS 'td' ,' ',[gps_valid] AS 'td' ,' ',[satellite_number] AS 'td' ,' ',[gsm_signal] AS 'td'FROM gps.dbo.gps_info_last INNER JOIN gps.dbo.user_tracker_relation on gps.dbo.gps_info_last.sn_imei_id = gps.dbo.user_tracker_relation.sn_imei_id INNER JOIN gps.dbo.user_info on gps.dbo.user_info.user_id=gps.dbo.user_tracker_relation.user_id ORDER BY r_datetime ASCFOR XML PATH('tr'),ELEMENTS ) AS NVARCHAR(MAX)) SET @body = @body + @xml +'</table></body></html>' exec msdb.dbo.sp_send_dbmail @profile_name ='sqlemail1', @recipients = '<my email addr>', @subject = 'Last Seen', @body_format ='HTML', @body = @body
my results end up as2013-04-09T18:28:26 2013-04-09T18:28:30 -3.388073200000000e+001 1.118018520000000e+00so im wondering if someone can advise how i can get the above text results visible in my html email table