|
stephenbaer
Yak Posting Veteran
71 Posts |
 Posted - 2009-10-07 : 21:28:41
|
| Hi everyone, I'm trying to email query results using database mail, but the @query parameter is making me crazyThis query runs fine:-----------------------------------SELECT Address1+ ISNULL(address2, '') + ', ' + city + ', ' + State + ' '+ Zipcode AS 'GH Address', '12' AS RCL, FacilityID, bedcount AS 'Lic. Cap.', DecBedCount as 'Dec. Lic. Cap.',dbo.fnCountLADCFS(FacilityID)AS #DCFS,dbo.fnCountLAPROB(FacilityID)AS #PROB, dbo.fnCountLADMH(FacilityID)AS DMH,dbo.fnCountNONLA(FacilityID)AS #NONLA,(dbo.fnCountLADCFS(FacilityID) + dbo.fnCountLAPROB(FacilityID) +dbo.fnCountLADMH(FacilityID) + dbo.fnCountNONLA(FacilityID))AS'Month End Total', '12-17' as 'Age Range', 'F' as GenderFrom dbo.facilityHowever, after double quoting everything that needs it, the procedure following errors out with:Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476Query execution failed: Msg 208, Level 16, State 1, Server SERVER1\SBSSQL, Line 1Invalid object name 'dbo.Facility'.Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.--------------------------EXEC msdb.dbo.sp_send_dbmail @recipients= 'xxxxxxx@xxxx.xxx', @Query = 'SELECT Address1 + ISNULL(address2, '''') + '', '' + city + '', '' + State + '' ''+ Zipcode AS ''GH Address'', ''12'' AS RCL, FacilityID, bedcount AS ''LicCap'', DecBedCount as DecrLicCap, dbo.fnCountLADCFS(FacilityID)AS #DCFS,dbo.fnCountLAPROB(FacilityID)AS #PROB, dbo.fnCountLADMH(FacilityID)AS DMH,dbo.fnCountNONLA(FacilityID)AS #NONLA,(dbo.fnCountLADCFS(FacilityID) + dbo.fnCountLAPROB(FacilityID) + dbo.fnCountLADMH(FacilityID) + dbo.fnCountNONLA(FacilityID))AS ''Month End Total'',''12-17'' AS ''Age Range'', ''F'' AS Gender FROM dbo.Facility', @subject = 'GH Util',@attach_query_result_as_file = 0 ; -----------------Stephen |
|
sp_send_dbmail - in 'quotes' hell!