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