Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server and executes
other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that states
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make these settings in a usp?
After the procs are created and I create the link at runtime can I expect some problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
'Ack!'
troy
Troy,
The settings need to be set when you create your stored
procedures - not in the stored procedures but for the
connection creating the stored procedures - so that the
stored procedures are created with these settings. Something
along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 9 Apr 2004 13:06:06 -0700, "Troy"
<anonymous@.discussions.microsoft.com> wrote:

>I'm trying to create a stored proc that creates a link to a remote server and executes
>other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
>I'm having trouble creating these sub-procs, I get the error message that states
> Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
>I have the remote server linked when I try to create the procs.
>I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these need to be set on the linked server.
>I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
> EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
>but got an 'invalid options' error.
>Does this need to be set as server defaults on each server or can I make these settings in a usp?
>After the procs are created and I create the link at runtime can I expect some problems?
>Any suggestions/links would be greatly appreciated.
>I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
>to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
>'Ack!'
>troy
|||Thanks Sue, that took care of it.
|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you
guys figured out what the problem was but the solution is not in this
thread. Any chance you can share your wisdom?
imalik
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message177518.html
|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?
|||Hi all!
I have the same problem with a view I'm trying to use with Crystal Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but it's still doesn't work...
How can I solve it please?

Quote:

Originally Posted by Sue Hoegemeier

If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?

|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:
|||

Quote:

Originally Posted by Sue Hoegemeier

I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:

I fix my problem by setting ANSI_NULL/WARNINGS in my ODBC source. Thx anyway

Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server an
d executes
other stored procedures as needed. Each of the sub-procs will be running He
terogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that st
ates
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these
need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make the
se settings in a usp?
After the procs are created and I create the link at runtime can I expect so
me problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as nee
ded. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books o
n-desk.
'Ack!'
troyThanks Sue, that took care of it.|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you guys
figured out what the problem was but the solution is not in this thread. Any
chance you can share your wisdom?|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?|||Hi all!
I have the same problem with a view I'm trying to use with Crystal
Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
it's still doesn't work...
How can I solve it please?
Sue Hoegemeier;3612624 Wrote:[vbcol=seagreen]
> If you have the same issues with a stored procedure, the
> answer is in the post in this thread. You need to create the
> procedure with the appropriate setting e.g.
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> CREATE PROCEDURE YourStoredProc...etc.
>
> -Sue
> On Mon, 10 Jan 2005 04:45:31 -0600, imalik
> <imalik.1inb94@.mail.webservertalk.com> wrote:
>
> you
zen69|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote:

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

Heterogeneous queries error

Hi,
I've a dynamic query that works on linked server but it keep giving me this
error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS option
s to be set for the connection. This ensures consistent query semantics. Ena
ble these options and then reissue your query."
I've set all the ANSI_DEFAULT on but still doesn't work. Can someone help me
out?
Thx.
----
--
CREATE PROCEDURE a_sp_check_record
@.serverDB_name VARCHAR(100),
@.storeID VARCHAR(2),
@.cutoff_date VARCHAR(30)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)
DECLARE @.recCount INT
-- exist in remote but missing in HQ
SET @.sqlString ='SELECT DISTINCT po_number, invoice_number, received_date, u
pc '
+'FROM ' + @.serverDB_name + '.dbo.receive_order_transactions REMO '
+'WHERE received_date>''' + @.cutoff_date + ''' AND '
+ 'store_id=' + @.storeID + ' '
+ 'AND NOT EXISTS (SELECT * FROM [tm341].dbo.receive_order_transactions HQ '
+ 'WHERE HQ.po_number=REMO.po_number AND HQ.invoice_number=REMO.invoice_n
umber AND '
+ 'HQ.received_date=REMO.received_date AND HQ.store_id=REMO.store_i
d AND '
+ 'HQ.store_id=' + @.storeID + ' AND REMO.store_id=' + @.storeID +')'
EXEC SP_EXECUTESQL @.sqlString
IF @.@.rowCOUNT = 0
print 'remote have all HQ rcv'
ELSE
print 'remote have missing HQ rcv'
GOYou the ANSI_NULLS & ANSI_WARNINGS settings within the stored procedure.
Instead apply these settings while creating the procedure itself.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE usp_test(...
GO
Anith