Heterogeneous queries require the ANSI_NULLS and A

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 ;_;

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;"); 

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

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 2017

For example:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON 

CREATE PROCEDURE  dbo.access_update @O_SQL_Error_State int = NULL  output
as 
...