Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Glasgow
    Posts
    4

    Unanswered: Linked Servers and sp_executeSQL

    Hi,

    I am trying to do something like the following within a Stored Procedure but have also tried it on its own within a batch:

    --
    go
    Declare @MySQL nvarchar(500)
    @MySQL = 'Select * from openquery(borders, ''Select * from

    dba.organise'')'

    exec sp_executeSQL @MySQL
    go
    --

    then I get Error 7405 'Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection....' I tried setting these in a SQL batch before the one above. Various sources say that you should set these before you create procedure but as you can see above I am not doing this within a create procedure - is it something to do with the sp_executeSQL ???? Help!!!

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    EXEC and sp_executeSQL run on seperate threads so include the set statments in your string

    Code:
    @MySQL = 'set ANSI_NULLS on
              set ANSI_WARNINGS on
              Select * from openquery(borders, ''Select * from dba.organise'')'
    exec(@MySQL)
    Also, I have found that including the database name in the table reference will eliminate headaches.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Location
    Glasgow
    Posts
    4
    Thanks Paul - What I eventually did was this:

    set ansi_nulls on
    set ansi_warnings on
    go
    create procedure .....

    ... putting it in the SQL statement worked in Query Analyzer but not when it was within the stored procedure.

    Thanks for the quick reply!
    Regards

    Anna

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I would suggest doing the SETs inside the sp, that way if someone extracts the code from your server and recompiles you will still have the correct environment for you linked server query
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2003
    Location
    Glasgow
    Posts
    4
    Hi Paul,

    I did try that but I couldn't seem to get that to work.

    Cheers

    Anna

Posting Permissions

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