Sql Mail

[vmlal]

I have this command:

Exec Master..xp_SendMail
@recipients = @recipientslist,
@subject = @subj,
@message = @msg,
@query = ''Select * FROM DatabaseName.user.TableName''

But it gives me an error on the @query command. If I try the same thing without the @query command it works fine. How do I get the query to show in the mail as well?

Thanks

[rmiao]

Do you have permission on the table?

[skhanal]

use single quote for the query, double quote has special meaning if you have set quoted identifier to true.


Exec Master..xp_SendMail
@recipients = @recipientslist,
@subject = @subj,
@message = @msg,
@query = 'Select * FROM DatabaseName.user.TableName'

[vmlal]

Yes I have permission to the table. I'm in the adminsitrator group and the using the single quote in @query still gives me this error:


ODBC error 8198 (42000) Could not obtain information about Windows NT group/user 'domain\username'.

[rmiao]

What permission does your windows account have on target db and table? Member of dbo or something else? Get any result if run 'Select * FROM DatabaseName.user.TableName' alone?

[vmlal]

I can 'Select * from tablename' on QA just fine.

[rmiao]

What's sql server service account? Does the account have permission on the table?

[skhanal]

Did you create a mail profile for the sql server service account in outlook?