OPENQUERY

  • I am trying to query a linked server via a Sproc and on trying to save it get the following error

    "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

    Any suggestions. Also any good online resources on this.

    Thnx

  • I have not seen this before on my linked servers, however what type of server is the linked server and what si the query you are using to in the SPROC.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Both servers are named instances of SQL Server and are on the same box.

    This is the SPROC.

    CREATE PROCEDURE usp_Test

    AS

    SELECT *

    FROM OPENQUERY (LINKED_SERVER_NAME, 'SELECT TOP 10 * FROM tblTemp')

  • Why are you using Openquery instead of just referencing the server directly?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • I agree use

    SELECT TOP 10 * FROM LINKED_SERVER_NAME.dbname.dbo.tblTemp

    will produce the same results.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • If you are on the same server use a server reference as mentioned. If you in the future need to do the same thing against another server you need to re-create the stored procedure you are calling with the SET ANSI_NULLS ON statment first.

    Example:

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE usp_Test

    AS

    SELECT *

    FROM OPENQUERY (LINKED_SERVER_NAME, 'SELECT TOP 10 * FROM tblTemp')

    GO

  • Doesnt have to be same server, you just have to have a linked server set up pointing to whereever it is.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Did you tried an OPENROWSET like this one

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE usp_Test

    AS

    SELECT a.*

    FROM OPENROWSET('SQLOLEDB','Server';'user';'Password',

    'SELECT * FROM Database.dbo.') AS a

    GO

    The openrowset have better performances than linked servers.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply