10

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

Maude
  • 512
  • 3
  • 8
  • 23
  • 1
    The key you seem to be missing: *for the connection*. You are setting options for the proc, not the *connection*. – Andrew Barber Apr 24 '12 at 14:47
  • 4
    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); – Maude Apr 24 '12 at 17:42

3 Answers3

10

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;");
Niloofar
  • 111
  • 1
  • 7
3

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
brasofilo
  • 25,496
  • 15
  • 91
  • 179
Tester
  • 31
  • 2
1

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
    ...
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stan
  • 985
  • 1
  • 7
  • 12
  • 2
    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! – GunnerFan420 Aug 26 '19 at 17:08