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

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

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

I have a SQL200 stored proc that gives me 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." when I try to execute it through the query analyzer.

I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
or else my vb6 recordset crashes.

When I created the sproc, I did what every one was telling me to do in the forums by putting in the

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF

Can someone help me?this is a confirmed bug of sql2k. do u read this link?
http://support.microsoft.com/kb/296769/en-us|||I am not using the Enterprise Manager like the artical says. I am using the Query Analyser. I don't have a problem creating the stored proc. I only get the error when I try to execute the stored proc.|||I assume this to be the code that you are using to create the stored procedure.

SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure usp_SprocName

AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF


OK. But why haven't you done what was mentioned in the Microsoft Support Article? That would seem like the only logical step to me.

The error occurs because, in order to execute this type of query, the stored procedure definition must contain the definitions mentioned in the article. Your stored procedure sets the relative option to 'ON' but then immediately afterwards sets it to OFF. Try reading your stored procedure definition again...

Regards,|||The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav 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.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel