Page 1 of 1

Strange Problem with mssql RAISERROR behavior

Posted: Tue Oct 30, 2007 9:33 am
by waskelton4
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello group,

I'm running MS SQL 2005 (SP1 i think) with PHP 4.3.7

I have a script that executes a stored procedure from PHP like so..

Code: Select all

$SQL = "EXEC dbo.saveNoteField 	@userID = ".$_SESSION['user_ID'].",
		 				@visitID = ".$_REQUEST['visit'].",
			 		        @noteID = ".$_REQUEST['nid'].",
						@noteTypeID = ".$_REQUEST['ntype'].",
						@fieldName = '".$_REQUEST['field']."', 
						@fieldData = '".$_REQUEST['data']."'";
											
		if(mssql_query($SQL)){
			 $message = explode('|', mssql_get_last_message());
		}
		else {
			$msg =0;
			$msgtext="Something is HOSED!";
		}
The 'else' is some debugging code in there.. It isn't ever run.

The sql code looks like this.

Code: Select all

SET @msgReturn = '1|this is a message!'
EXEC sp_executesql @sql
IF (@@ERROR > 0) 
	BEGIN
		SET @msgReturn = '0|Error Saving Note Field. '+ CONVERT(varchar,getDate(),0)
		GOTO finish
	END	
ELSE
	BEGIN
		IF @noteID > 0
			BEGIN
				UPDATE tblNotes SET lastEditWho = @userID, lastEditWhen = getDate(), [status] = 11 WHERE noteID = @noteID
				IF (@@ERROR > 0 OR @@ROWCOUNT = 0) 
					BEGIN
						SET @msgReturn = '0|Audit Info not saved: ' + CONVERT(varchar,getDate(),0)
						GOTO finish
					END
				ELSE
					BEGIN
						SET @msgReturn = '1|WTF MATE?'
						GOTO finish
					END
			END
		ELSE
			BEGIN 
				SET @msgReturn = '1|Field Saved: ' + CONVERT(varchar,getDate(),0)	
				GOTO finish
			END	
	END
END

finish:
BEGIN
	RAISERROR (@msgReturn, 10, 1) with log
END
GO
The crazy thing about this is that depending on the status of @noteID int the "IF @noteID > 0" portion of the code.. it works.. or it doesnt

if @noteID is not set and the IF/ELSE goes to else then the message is returned as expected.
if @noteID > 1 the message returned to PHP in the mssql_get_last_message is "Changed database context to 'myTestingDB'"
the nutty part is that the "with log" portion of the RAISERROR statement write the correct message to the log.

any thoughts on what is going on here?

Thanks
Will


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Strange Problem with mssql RAISERROR behavior - [SOLVED]

Posted: Tue Oct 30, 2007 10:13 am
by waskelton4
Found the problem..

I had some select statements above that had values being returned that I wasn't handling and for some reason it was causing this funny behavior.

sorry about the lack of syntax declaration feyed..

ws