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

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