I want to send emails from sql server 2008 through linked server. I configured the Linked server using:--
EXEC sp_addlinkedserver N'linkedserver.com',N'SQL Server';
EXEC sp_addlinkedsrvlogin 'linkedserver.com', 'false','sa' , 'user', 'Pwd';
It linked successfully.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Profile name',
@recipients='[email protected]',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.',
@query = 'select top 10 * from [linkedserver.com].[comm].[dbo].[tbl_mobilereporting] order by visit_date desc'
It works great. But the problem is when I'm trying send it through sql server job agent, it shows:--
NT AUTHORITY\SYSTEM. Error formatting query.probably invalid parameters [SQLSTATE 42000] (Error 22050).The step failed.
and when i parsed the query, it shows command parsed successfully.
The linked server login is setup for sa. Check that the agent job is run as sa or add a linked server login for the correct local login.
The example looks like it has been simplified and has values supplemented. If so, try creating your query in an nvarchar(max) variable then passing it to @query. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.