Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: Database mail sending fails with SP_Send_DBmail.

    Hi,

    I am stuck in below problem.

    I have two servers; ServerA and ServerB. I have linked serverA to serverB by
    sp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB',
    @srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.
    The Query is :

    EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',
    @subject = 'MySubject',
    @body = 'This Is Test.',
    @body_format = 'Text', @profile_name = 'Profile1',
    @query = 'set nocount on
    SELECT * FROM [ServerB].[Database].dbo.myTable AS MT
    WHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',
    @execute_query_database = 'master',
    @query_result_separator = ' ',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'myFile.csv'

    I have created an proc on serverA's master database and calling this proc into a job.
    This job throws an error:

    " Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "

    Can Anybody help in solving this??
    It would be a great help.

    Thanks and Regards,
    Jignesh

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jigneshal View Post
    Hi,

    I am stuck in below problem.

    I have two servers; ServerA and ServerB. I have linked serverA to serverB by
    sp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB',
    @srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.
    The Query is :

    EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',
    @subject = 'MySubject',
    @body = 'This Is Test.',
    @body_format = 'Text', @profile_name = 'Profile1',
    @query = 'set nocount on
    SELECT * FROM [ServerB].[Database].dbo.myTable AS MT
    WHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',
    @execute_query_database = 'master',
    @query_result_separator = ' ',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'myFile.csv'

    I have created an proc on serverA's master database and calling this proc into a job.
    This job throws an error:

    " Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "

    Can Anybody help in solving this??
    It would be a great help.

    Thanks and Regards,
    Jignesh
    Looks like you need to change the authentication on the linked server. Use the sql server login and provide the userid/password pair. I have this same error once in a while with linked servers not using the windows authentication.

  3. #3
    Join Date
    Jun 2012
    Posts
    7
    Quote Originally Posted by corncrowe View Post
    Looks like you need to change the authentication on the linked server. Use the sql server login and provide the userid/password pair. I have this same error once in a while with linked servers not using the windows authentication.
    Authentication is already set to SQL Authentication. The problem occurs when i run my proc in Job. While i Run the query directly from SSMS all goes well.

  4. #4
    Join Date
    Jun 2012
    Posts
    7

    Cool Solution

    Hi,
    The issue is solved.There was a authentication problem for ServerA/SQLAgent.
    I have mapped both the user; i.e user from ServerA and user from ServerB using
    following system SP. In my addlinkedserver @useself was true bydefault.
    We need to make it false and map both the server's user.

    EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedServerName',
    @useself = 'false',
    @locallogin = 'localLoginName',
    @rmtuser = 'RemoteLoginName',
    @rmtpassword = 'RemoteLoginPassword';

    It worked for me.
    Now I am able to send DB mail from serverA with attachment of Data from ServerB.

    Cheers..!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •