Re: ANSI_WARNINGS error in Stored Procedure

From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 01/16/04 

Date: Fri, 16 Jan 2004 15:43:38 -0500

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...
> 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:
>
> 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.
>
> 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.
>
> 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.
>
> Thanks in advance for your help.
>
> Noe, from Mexico.

Relevant Pages

  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... table, if another inserting occurs at the nearly exactly the same moment, ... SQL Server will pass it a default value. ... assume that you do have a connection that can reach SQL Server Express. ... unless your stored procedure has some thing that only works ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... Of course this is assume that you do have a connection that can reach SQL Server Express. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ... return @RETURNVALUE or the SQL Server 2000 version won't work either. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... set cmd = Server.CreateObject ... 2000, 2005), as long as the connection is OK and the SP has two parameters ... If the problem is not with the SQL server Exp, then how come the SQL Sever ... unless your stored procedure has some thing that only ...
    (microsoft.public.dotnet.framework.aspnet)