Error while updating Database with mssql_query

Question

I'm using mssql_query to connect to an existing SQL Server 2008 Database.

SELECT querys are ok, but when I run UPDATE querys like the following:

mssql_query("UPDATE TABLENAME SET fieldname = 1 WHERE Pk = '".$pk."'");

I get this error:

UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (severity 16)

Here is my connection code to Database:

$server = 'SRVSQL';

// Connect to MSSQL
$link = mssql_connect($server, 'xx', 'xxxxxx');

if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}

$conn = mssql_select_db('xxxxxxx',$link);

Answer 1

You might have to explicitly change the settings by turning the settings on. You can do so by issuing the following query prior to the UPDATE statement:

SET 
  ANSI_NULLS, 
  QUOTED_IDENTIFIER, 
  CONCAT_NULL_YIELDS_NULL, 
  ANSI_WARNINGS, 
  ANSI_PADDING 
ON;

Should there be additional settings yielding errors, those might have to be changed as well.

See also: ANSWER: UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'