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.
| Author |
Topic |
|
gogetter
Starting Member
18 Posts |
 Posted - 2008-04-03 : 12:46:17
|
| I have an automated procedure that sends a message to a particular department based on some query results. This has been working fine but all of a sudden the @query results that are used by xp_sendmail are not being formatted properly in the output email. The query being used by @query is very simple and only contains four columns so I don't understand why this is happening. Here is what the query looks like when you print it: select distinct employeeID, convert(char(10), originalhiredate, 101) as Hiredate, ' ' + rtrim(locationcode) as Location, rtrim(emp_firstname) + rtrim(emp_lastname) as Namefrom dbo.outputdatawhere YearsofService < 0 or MonthsofService < 0 If you run it in QA in returns the result set with no problem. Does anybody have any idea why the formatting is off within the body of the email? |
|
|
gogetter
Starting Member
18 Posts |
Posted - 2008-04-03 : 13:19:58
|
quote:
Originally posted by gogetter
I have an automated procedure that sends a message to a particular department based on some query results. This has been working fine but all of a sudden the @query results that are used by xp_sendmail are not being formatted properly in the output email. The query being used by @query is very simple and only contains four columns so I don't understand why this is happening. Here is what the query looks like when you print it: select distinct employeeID, convert(char(10), originalhiredate, 101) as Hiredate, ' ' + rtrim(locationcode) as Location, rtrim(emp_firstname) + rtrim(emp_lastname) as Namefrom dbo.outputdatawhere YearsofService < 0 or MonthsofService < 0 If you run it in QA in returns the result set with no problem. Does anybody have any idea why the formatting is off within the body of the email?
I seem to have figured it out. Somebody changed the employeeID column on my table from char (9) to varchar (8000). Not sure how or why this was done but this change was causing xp_sendmail to put extra spaces in for all of those extra characters. I can't change the schema during production so to solve the problem I changed my query to select a substring of 9 characters from the employee ID column and that did the trick. You learn something new about SQL everyday. |
 |
|
|
|
|
|
XP_Sendmail @query formatting