Verify that SET options are correct....

you need to paste your php code

are you passing the sql parameters properly?

parameterized queries?

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

Puzzling to me, though, that it works everywhere except the web layer.

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

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

Select allOpen in new window

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.

Thank you, cyberkiwi.  I've dropped and recreated it, as you've suggested.  Still no difference.

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;

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.  :-)

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

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?

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.

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.

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.

It was a problem w/the php.