Incorrect syntax near 'and' -> no 'and' in query/procedure...

[psycho5]

Hey PHPFreaks,

 

maybe someone could help me with a strange (at least for me) problem which i am stuck with the last few days.

 

I have a stored procedure on MSSQL-Server which runs fine from SQLMGMT-Studio and from an Access-Frontend. But as soon as i execute it from within PHP it responds with:

 

Incorrect Syntax near the keyword 'and'.

 

Now to the strange part, except that it only produces errors executed from PHP, there is absolutely no AND in the procedure. We replaced all ANDs with NOR constructs.

 

Setup:

-PHP 5.2.6-1+lenny9 on Debian Lenny (Apache 2.2.9).

-MSSQL 2005 (latest patches and SP) on Win2k8 R2 64bit.

 

In php.ini:

mssql.batchsize=0

 

In freetds.conf:

text size = 62914560

 

PHP-Code:

$sqlquery = "EXECUTE AS LOGIN = \"domain\\".$_SESSION['username']."\"; exec dbo.proc_ContractFillTempTaskList; REVERT;";
$result = mssql_query($sqlquery);

 

SQL dbo.proc_ContractFillTempTaskList:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[proc_ContractFillTempTaskList]
AS
BEGIN
DECLARE @ContractTaskId int,
	@firstdate datetime, 
	@lastdate datetime,
	@cycleDatepart nvarchar(30), 
	@cycleNumber int,
	@editDatePart nvarchar(30),
	@editNumber int,
	@reminderDatepart nvarchar(30),
	@reminderNumber int

DELETE FROM dbo.tblContract_tmp_TaskList WHERE UserName = suser_sname()

DECLARE csr cursor for 
	SELECT     ContractTaskId, firstdate, lastdate, cycleDatePart, cycleNumber, editDatePart, editNumber, reminderDatePart, reminderNumber
	FROM         dbo.view_ContractTaskListBase 

open csr
fetch next from csr into @ContractTaskId, @firstdate, @lastdate, @cycleDatePart, @cycleNumber, @editDatePart, @editNumber, @reminderDatePart, @reminderNumber

while @@fetch_status=0
	BEGIN
		INSERT INTO dbo.tblContract_tmp_TaskList
			(ContractTaskId,
			 SeqNumber,
			 DueDate,
			 EditDate,
			 ReminderDate) 
		SELECT 
			ContractTaskId,
			 SequentialNumber,
			 DueDate,
			 EditDate,
			 ReminderDate
		FROM
			dbo.funcTbl_ContractAppointmentsAndReminders(
				@ContractTaskId,
				@firstdate, 
				@lastdate,
				@cycleDatepart, 
				@cycleNumber,
				@editDatePart,
				@editNumber,
				@reminderDatepart,
				@reminderNumber)

		fetch next from csr into @ContractTaskId, @firstdate, @lastdate, @cycleDatePart, @cycleNumber, @editDatePart, @editNumber, @reminderDatePart, @reminderNumber
	END			

close csr
deallocate csr

INSERT INTO dbo.tblContractTimeTable (ContractTaskId, DueDate, EditDate, ReminderDate)
SELECT ContractTaskId, DueDate, EditDate, ReminderDate FROM dbo.view_ContractTimeTableInsertBase
WHERE NOT (FixId IS NOT NULL OR TempId IS NULL)
    
exec dbo.proc_ContractDeleteTempTaskListOverheads


END

 

SQL dbo.proc_ContractDeleteTempTaskListOverheads:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[proc_ContractDeleteTempTaskListOverheads]
AS
BEGIN

DELETE FROM tblContractTimeTable 
WHERE ID IN 
(SELECT ID FROM view_ContractTimeTableDeleteBase)


END

 

SQL view_ContractTimeTableDeleteBase:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[view_ContractTimeTableDeleteBase]
AS
SELECT     dbo.tblContractTimeTable.Id
FROM         dbo.tblContractTimeTable INNER JOIN
                      dbo.view_ContractTimeTableInsertBase ON dbo.tblContractTimeTable.Id = dbo.view_ContractTimeTableInsertBase.FixId
WHERE     ((CASE WHEN TempId IS NOT NULL OR
                      tblContractTimeTable.DateDone IS NOT NULL OR
                      Remark IS NOT NULL THEN 0 ELSE 1 END) = 1)

 

 

The error occures in dbo.proc_ContractDeleteTempTaskListOverheads at the DELETE-Statement. If i change it to something like

DELETE FROM tblContractTimeTable WHERE ID=999999 it also fails.

But if i remove the DELETE and change it to a "DECLARE @a int" (just to do something in the procedure) it works. Not like intended but without an error.

view_ContractTimeTableDeleteBase also has no errors if executed solely.

 

Please excuse my english, im not a native speaker.

And many thanks in advance for every hint or hit in the face if i am again don't see the obviously

 

Kind regards,

psy5

[psycho5]

Hey Folks,

 

solved the problem. There is a trigger on tblContractTimeTable which builds kind of an audit trail (logs DELETES & UPDATES and from which user and time). The trigger queries the INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE and builds a join on the primary keys with the MSSQL coalesce()-function and puts an 'AND' in between if there are multiple key values. It seems that the output from coalesce() in PHP is slightly different and this brought up the error.

 

Thanks for letting me write down all this. Seemed to help

 

Have a nice day!

 

Regards,

psy5