dongtang1997
dongtang1997
2011-08-31 20:19
浏览 41
已采纳

MSSQL标量值函数+ PHP

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doukuizuo1795
    doukuizuo1795 2011-08-31 20:42
    已采纳

    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
    
    点赞 评论

相关推荐