July 17, 2002 at 7:49 am
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
July 17, 2002 at 8:19 am
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)
July 17, 2002 at 8:36 am
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')
July 17, 2002 at 10:12 am
Why are you using Openquery instead of just referencing the server directly?
Andy
July 17, 2002 at 11:02 am
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)
July 17, 2002 at 2:36 pm
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
July 17, 2002 at 2:49 pm
Doesnt have to be same server, you just have to have a linked server set up pointing to whereever it is.
Andy
July 18, 2002 at 12:06 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply