MSSQL scalar-valued functions + PHP

Question

first post/question here. I'm attempting to access a scalar-valued function in PHP using the following query:

select *, dbo.fnFunctionName_asArray(AnID,';') as refRes FROM ResourceDB where UniqueID = 'IDgoesHere'

This query runs perfectly fine within MS SQL Server Management Studio but when trying to run the query via PHP I get a horrendous error.

Here is the PHP code itself:

$sql_r = "select *, dbo.fnFunctionName_asArray(AnID,';') as refRes FROM ResourceDB where UniqueID = 'IDgoesHere'";
$r_r = mssql_query($sql_r, $msdbc);

And the error is as follows:

Warning: mssql_query() [function.mssql-query]: message: SELECT failed because the following SET options have incorrect settings: '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) in

I've searched far and wide and have found information on stored procedures, but not on functions within MSSQL. I'm not sure how to interpret the error message as well.

Thanks in advance.

Answer 1

the error message is the key:

Warning: mssql_query() [function.mssql-query]: message: SELECT failed because the following SET options have incorrect settings: '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) in

when trying to use certain features, SQL Server is picky about your connection settings.

you are connecting to SQL Server with different settings for SSMS and your php program. Their defaults must be different. Run this SQL command from SSMS and then from within php:

DBCC USEROPTIONS 

it will show your actual settings, which are different. Try using these settings:

SET ANSI_NULLS ON 
SET CURSOR_CLOSE_ON_COMMIT ON 
SET ANSI_NULL_DFLT_ON ON 
SET ANSI_PADDING ON 
SET QUOTED_IDENTIFIER ON 
SET ANSI_WARNINGS ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF