Issues encountered with MSSQL stored procedures that use linked servers are called
This post was last edited by Ycliaojy on 2013-03-24 20:40:29
PHP calls the MSSQL stored procedure using the linked server error, who has encountered a similar problem?
Omit other statements
$queryP = "exec oa_pfm_attend_group_toexempt @begin = ' $DATE 1 ', @end = ' $DATE 2 '";
$cursor = Msexequery ($msconnection, $queryP);
Omit other statements
The combination of $QUERYP is: exec oa_pfm_attend_group_toexempt @begin = ' 2013-03-01 ', @end = ' 2013-03-24 '
The stored procedure does not have a problem in Query Analyzer execution, and all the statements that use the linked server are removed after the PHP call is not a problem, is not the stored procedure that the PHP call cannot use the linked server? I'm trying to write an ASP. [Tested, stored procedures executed under ASP without any questions]
PHP error messages have no reference value:
Error #0:
SQL statement: exec oa_pfm_attend_group_toexempt @begin = ' 2013-03-01 ', @end = ' 2013-03-24 '
SQL Server event tracking and log information does not find useful information.
Stored Procedure Code:
drop proc Oa_pfm_attend_group_toexempt
Go
Create proc Oa_pfm_attend_group_toexempt
@begin datetime,
@end datetime
As
Begin
SET NOCOUNT ON
Set ANSI_WARNINGS on
Set ANSI_NULLS on
--declare @begin datetime
--declare @end datetime
--set @begin = @beginSTR
--set @end = @endSTR
CREATE TABLE #query_Table_Temp (
Checkday datetime NOT NULL
, checkIn datetime NOT NULL
, CheckOut datetime NOT NULL
, isholiday bit NOT null default 0
)
DECLARE @checkDay datetime
DECLARE @checkIn datetime
DECLARE @checkOut datetime
Set @checkDay = @begin
while (@checkDay <[email protected]) begin
Set @checkIn = CONVERT (varchar), @checkDay) + ' 08:35:00 '
Set @checkOut = CONVERT (varchar), @checkDay) + ' 18:30:00 '
Insert into #query_Table_Temp (checkday,checkin,checkout) VALUES (@checkDay, @checkIn, @checkOut)
--update #query_Table_Temp set Isholiday=1 where Checkday in (select Holiday from OPENQUERY (oamysql, ' select * from A_rest _holiday '))
Set @checkDay = DateAdd (day,1, @checkDay)
End
Delete from #query_Table_Temp where Checkday in (select Holiday from OPENQUERY (oamysql, ' select * from A_rest_holiday '))-- This sentence causes an error, removing all the statements that call the linked server
CREATE TABLE #attend_Temp (
Checkday datetime NOT NULL
, UserID int
, Name varchar (30)
, user_id varchar (30)
, dept_id int
, Defcheckin datetime
, Defcheckout datetime
, checkIn datetime NULL
, CheckOut datetime NULL
, rest bit NOT NULL default 0
, leave1 bit NOT null default 0
, leave2 bit NOT null default 0
, OUT1 bit NOT null default 0
, OUT2 bit NOT null default 0
, evection bit NOT null default 0
)
Insert #attend_Temp (checkday,userid,name,user_id,dept_id,defcheckin,defcheckout)
Select A.checkday,b.userid,b.name,b.oa_userid,b.defaultdeptid,a.checkin,a.checkout from #query_Table_Temp a join FileServiceDB.dbo.UserInfo b on 1=1 where b.active=1 and b.checkfree=0