Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics

Question

I have checked over the whole web and couldn't find a solution that seems to work for me..

I have recreated my stored procedure, making sure to have these lines as first lines:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE test_insert
AS
....
BEGIN
...
END

I only get this error when i call my stored procedure from php. it works fine in sql server.. i really don't know what else i can do..please help me ;_;

Casualty

The key you seem to be missing: for the connection. You are setting options for the proc, not the connection. – Entrust

I cannot put a formal answer so : @Andre Barber: You were right, adding it to the stored procedure did not work. Here is what was needed to do: In the PHP: $sql="EXEC my_store_procedure"; $result = mssql_query("SET ANSI_NULLS ON") or die(mssql_get_last_message()); $result = mssql_query("SET ANSI_WARNINGS ON") or die(mssql_get_last_message()); $result= mssql_query($sql); – Casualty

Answer 1

Added this BEFORE your statement rather than at the start of the main query

$result = mssql_query("SET ANSI_NULLS ON;");
$result = mssql_query("SET ANSI_WARNINGS ON;"); 

Sechrist

Answer 2

This is an example that works... Try it like this

create procedure dbo.access_update @O_SQL_Error_State int = NULL     output

as

set ANSI_NULLS ON 

SET ANSI_WARNINGS ON    

....
....

GO

Guidry

Answer 3

Its not often a better answer is on another forum - but according to this post, the SET commands must be before the CREATE PROCEDURE. Tested and works with SQL Server 2017.

For example:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.access_update 
    @O_SQL_Error_State int = NULL OUTPUT
AS
    ...

Tims

This worked for me but you have to put GO after each line or you get 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Thank you! – Eboni