Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Luton, Bedfordshire, UK
    Posts
    6

    Unanswered: xp_sendmail and using open query

    I beleive (rightly or wrongly) that you are unable to run an open query within an xp_sendmail.

    I am trying to run a stored procedure with an open query within it. wrapping it within an xp_sendmail.

    Which fails.

    Any sugestions on how to get this running.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can try to surround your query in [] instead of ' ':

    Code:
    exec master.dbo.xp_sendmail @query=[SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')]

  3. #3
    Join Date
    Sep 2003
    Posts
    212
    ODBC error 8198 (42000) Could not obtain information about Windows NT group/user ''.

    go this error when i tired that

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Then this means that that the linked server is setup to use Windows Authentication and the user that invokes the query is not a valid login on that server.

    Do you guys work together?

  5. #5
    Join Date
    Dec 2003
    Location
    Luton, Bedfordshire, UK
    Posts
    6
    Originally posted by rdjabarov
    Then this means that that the linked server is setup to use Windows Authentication and the user that invokes the query is not a valid login on that server.

    Do you guys work together?
    We don't work togther as far as i am aware.

    However i have tried this and also set the linked server to be mapped to the oracle user no matter who logs in, and still the error occurs.

    the option set is in the properties, security tab, For login not defined in above list, connections will:

    - be made using this security context.

    Is there anywhere else that the user needs to be set?

    Cheers

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you able to logon to oracle using the user and password specified in the Security properties tab? Also, is oracle running on Windows and if yes does it honor Windows authentication?

  7. #7
    Join Date
    Dec 2003
    Location
    Luton, Bedfordshire, UK
    Posts
    6
    Oracle runs off unix, and I am able to login and run the openqueury and return a results set.

  8. #8
    Join Date
    Sep 2003
    Posts
    212
    so the query function wont work without setting up a linked server??!

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, openquery can only work with linked servers.

    How about putting the OPENQUERY part into a stored procedure and specify the call to it in @query parameter?

  10. #10
    Join Date
    Sep 2003
    Posts
    212
    well say if i'm sending an email after performing balancing on a table and i want to send a query in the email. Why do i need to create a linked server if the sql running is suppose to get the query from the server it's running from...

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmm, my understanding was that krysis_tonath was trying to send the results of the query that was running on ora by executing it from SQL server. If you're to use OPENQUERY then you need to have a linked server set up. You can also use OPENROWSET or OPENDATASOURCE to accomplish the same thing, and using those two you do not need a linked server.

Posting Permissions

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