I have a linked Server on my regular SQL server, and when I try to create a
stored procedure that reads from the linked server, I get the following erro
r:
ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS O
PTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES SEMANTI
CS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
When I click Check Syntax, it says that there are no Syntax Errors, but it w
ont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and "SET
ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same erro
r.
The query that is to be performed in the stored procedure is a simple SELECT
, no inner joins or anything. And the stored procedure receives only 1 (inte
ger) parameter.
Thanks in advance for your help.
Noe, from Mexico.Noe,
From the error I would understand that the linked server is not a Microsoft
SQL Server. Otherwise, how likely is it that the SQL Engine would be
concerned with "heterogeneous queries".
The error says to set these options for your _connection_ not for the query.
Since you are already connected before you try to compile the stored
procedure, it is too late for the SET commands to avoid the problem.
If you are using Query Analyzer to test this, then go to the menu item for
the connection and click these two settings on. (I believe that these are
normally set on by OLE DB / ODBC, but Query Analyzer may override these
settings.)
Russell Fields
"Noe" <noech77@.hotmail.com> wrote in message
news:D4F795B7-C67A-4F28-B95D-85B5FF7C7293@.microsoft.com...
quote:
> Hi everyone,
> I have a linked Server on my regular SQL server, and when I try to create
a stored procedure that reads from the linked server, I get the following
error:
quote:
> ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS
OPTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES
SEMANTICS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
quote:
> When I click Check Syntax, it says that there are no Syntax Errors, but it
wont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and
"SET ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same
error.
quote:
> The query that is to be performed in the stored procedure is a simple
SELECT, no inner joins or anything. And the stored procedure receives only 1
(integer) parameter.
quote:|||Hi Russell, thanks a lot for replying...
> Thanks in advance for your help.
> Noe, from Mexico.
you are correct, the linked server is a JDEdwards.
I was trying to create the stored procedure from the New Stored
Procedure Window, instead of the Query Analyzer. I dont know why I didnt
try the Query Analyzer before.
So there's a tip for all of you people, use the query analyzer and
forget about problems. Thanks again Russell!
Noe.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I doesn't mater which Database you use. Even in SQL server when we
link two different server, we get this problem. Once way of solving
this is create the stored procedure in SQL server in the format below
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
CREATE PROCEDURE dbo.MBUTotal
AS
BODY of THE STORED PROCEDURE
RETURN
GO
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
This will solve this problem. Atleast for me it helped to solve the
problem.
Regards,
John Solomon. J
Noe Carranza wrote:
> *Hi Russell, thanks a lot for replying...
> you are correct, the linked server is a JDEdwards.
> I was trying to create the stored procedure from the New Stored
> Procedure Window, instead of the Query Analyzer. I dont know why I
> didnt
> try the Query Analyzer before.
> So there's a tip for all of you people, use the query analyzer and
> forget about problems. Thanks again Russell!
> Noe.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it! *
john1811
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message291544.html
