Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the 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.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue