We help IT Professionals succeed at work.

Verify that SET options are correct....

7,489 Views
Last Modified: 2012-05-09
The update procedure below works perfectly everywhere except through php.  When it is fired through the web layer, it fails with this:

Warning: mssql_query() [function.mssql-query]: message: UPDATE 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 C:\Inetpub\dev\include\tableeditor_save.php on line 20

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\dev\include\tableeditor_save.php on line 20


There are no indexed views.  The Active attribute is bit, and there is a default constraint of 1.

Suggestions are appreciated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_updateSymbolTable] (
 @SymbolID int,
 @newsymbolID int=NULL,
 @SymbolName VARCHAR(25)=NULL,
 @SymbolClass CHAR(1)=NULL,
 @Active bit=NULL
)
AS
SET NOCOUNT ON
/* Allows for the update of SymbolTable, as needed. */
BEGIN
	IF EXISTS(SELECT 1 FROM dbo.SymbolTable WHERE SymbolID = @SymbolID)
	BEGIN
	  UPDATE 
			dbo.SymbolTable
	  SET 
			SymbolName=COALESCE(@SymbolName,SymbolName),
			SymbolID=COALESCE(@newsymbolid,SymbolID),
			SymbolClass=COALESCE(@SymbolClass,SymbolClass),
			Active=COALESCE(@Active,Active)
	  WHERE 
			SymbolID = @SymbolID

		IF @@ERROR <> 0	
		BEGIN
			RAISERROR('Failed to update dbo.SymbolTable.',16,-1)
			RETURN;
		END
	END
	ELSE	
	BEGIN
		RAISERROR('The given @SymbolID does not exist.',16,-1)
		RETURN;
	END

END

SET NOCOUNT OFF


GO

Open in new window

Comment
Watch Question

you need to paste your php code

are you passing the sql parameters properly?

parameterized queries?

Author

Commented:
Unfortunately, I am not the web admin.  I manage the data layer.  I will see what I can find.

Author

Commented:
Puzzling to me, though, that it works everywhere except the web layer.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Potentially, yes.  Looks the same way to me, but that doesn't tell me what, where or why.  Hence, my inquiry.
the sql parameters must not be passed properly ... use parameterized queries for the same

unfortunately, without php code, i can help you only so much
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Try recreating it as:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_updateSymbolTable] (
 @SymbolID int,
 @newsymbolID int=NULL,
 @SymbolName VARCHAR(25)=NULL,
 @SymbolClass CHAR(1)=NULL,
 @Active bit=NULL
)
AS
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON
/* Allows for the update of SymbolTable, as needed. */
BEGIN
	IF EXISTS(SELECT 1 FROM dbo.SymbolTable WHERE SymbolID = @SymbolID)
	BEGIN
	  UPDATE 
			dbo.SymbolTable
	  SET 
			SymbolName=COALESCE(@SymbolName,SymbolName),
			SymbolID=COALESCE(@newsymbolid,SymbolID),
			SymbolClass=COALESCE(@SymbolClass,SymbolClass),
			Active=COALESCE(@Active,Active)
	  WHERE 
			SymbolID = @SymbolID

		IF @@ERROR <> 0	
		BEGIN
			RAISERROR('Failed to update dbo.SymbolTable.',16,-1)
			RETURN;
		END
	END
	ELSE	
	BEGIN
		RAISERROR('The given @SymbolID does not exist.',16,-1)
		RETURN;
	END

END

SET NOCOUNT OFF


GO

Open in new window

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The php driver is either not setting them, or giving them the wrong value.  Sql Server expects the first and third to be ON for all future versions from 2008 R2.

Author

Commented:
Thank you, cyberkiwi.  I've dropped and recreated it, as you've suggested.  Still no difference.
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
When you recreate it, please use the code in the previous comment.
This time around, try changing

SET ANSI_WARNINGS ON;

to

SET ANSI_WARNINGS OFF;

Author

Commented:
Nope.  After having done that, it now fails in SSMS w/this:

Msg 1934, Level 16, State 1, Procedure usp_updateSymbolTable, Line 22
UPDATE failed because the following SET options have incorrect settings;  'ANSI_WARNINGS'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filetered indexes and/or query notifications and/or XML data and/or spatial index operations.

Typing that error message just reminded me -- I have a filtered index on SymbolName, SymbolClass WHERE Active = 1.  That along with the default constraint on the bit value for Active are the only 'special' cases.  But again, it works just fine in SSMS.  Or it did until I changed ANSI_WARNINGS to OFF.  :-)
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Play with the other 2 set options for a while until you get it right.  There are only 3 more combinations to go!
Must match the same SET options used when creating the filtered index.

http://bugosql.net/index.php?option=com_content&task=view&id=695&Itemid=68

Author

Commented:
I have tried everything.  within the procedrue creation, and the index creation.  no changes, it still works within SSMS, but fails at the web layer.

I dropped the filtered index, and the update through the web layer worked fine.

any suggestions?

Author

Commented:
I really don't want to have to drop this filtered index.  I am still digging on this, but any suggestions would be greatly appreciated.
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The error message is quite explicit.  It mentions 3 SET options.
So try these at the top of the procedure create:

SET CONCAT_NULL_YIELDS_NULL OFF;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;

SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS OFF;
SET ANSI_PADDING ON;

SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING OFF;

etc.  Out of 3 options, there are only 8 combinations of ON/OFF.

Author

Commented:
The error message may very well be explicit, but it is caused by the php translation of the procedure, not the SQL construct itself.  See here:  
   http://www.php.net/manual/en/function.mssql-connect.php#53808

The chunk about 3/4 down, by Robert at realjax dot com, on 14-Jun-2005 01:00.  
' The problem is that the MS SQL database settings are not set to the ANSI defaults as when connecting through Microsoft products.  The setting CONCAT_NULL_YIELDS_NULL defaults to ON when connecting with ODBC or SQL Query Analyzer, which complies with the ANSI standard.  However, this defaults to OFF when connecting through PHP.  There are many other settings which may also need to be explicitly set. '

The three SETs which were already defined properly in the procedure, and on the index creation, needed to be explicitly defined within the php.  They were added to the php like this:


mssql_query( 'SET CONCAT_NULL_YIELDS_NULL ON', $conn );
mssql_query( 'SET ANSI_WARNINGS ON', $conn );
mssql_query( 'SET ANSI_PADDING ON', $conn );


Everything works fine now.


Thank you both for looking.  RakeshBhandari gets the points -- it was a problem within the php, which I was able to find w/the help of one of my SQL friends.


Author

Commented:
It was a problem w/the php.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.