调用使用链接服务器的mssql存储过程遇到的问题
本帖最后由 ycliaojy 于 2013-03-24 20:40:29 编辑 php调用使用链接服务器的mssql存储过程出错，哪位有遇过到类似的问题?
//省略其它语句
$queryP= "exec OA_pfm_attend_group_toExempt @begin='$DATE1',@end='$DATE2'";
$cursor = msexequery( $msconnection, $queryP );
//省略其它语句
//组合的$queryP是：exec OA_pfm_attend_group_toExempt @begin='2013-03-01',@end='2013-03-24'
存储过程在查询分析器执行没有问题，把所有使用链接服务器的语句删除后php调用也没有问题，是不是php调用的存储过程不能使用链接服务器?我写个asp试试有问题没［经过测试，存储过程在asp下执行也没有任何问］
php的出错信息无参考价值：
错误#0:
SQL语句: exec OA_pfm_attend_group_toExempt @begin='2013-03-01',@end='2013-03-24'
sqlserver事件跟踪和日志信息查不到有用的信息．
存储过程代码：
存储过程 链接服务器
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<=@end) begin
set @checkIn = convert(varchar(10),@checkDay)+' 08:35:00'
set @checkOut = convert(varchar(10),@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'))--这句引起出错，把这些调用链接服务器的所有语句删除就举出错
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
--select a.checkDay,a.checkIn,a.checkOut,b.UserID,b.Name,b.defaultDeptID,b.oa_UserID from #query_Table_Temp a join FileServiceDB.dbo.UserInfo b on 1=1 where b.Active=1 and b.checkFree=0
update #attend_Temp set checkIn=(select min(checkTime) from OA_checkInOut where userID=#attend_Temp.userID and datediff(minute,checkTime,#attend_Temp.defCheckIn) between -150 and 210)
update #attend_Temp set checkOut=(select max(checkTime) from OA_checkInOut where userID=#attend_Temp.userID and datediff(minute,#attend_Temp.defCheckOut,checkTime) between -270 and 690)
--其它语句
select checkDay,(select count(*) from #attend_Temp where checkDay=#query_Table_Temp.checkDay and checkIn>#query_Table_Temp.checkIn and out1=0 and leave1=0 and rest=0 and evection=0) as 迟到人次
,(select count(*) from #attend_Temp where checkDay=#query_Table_Temp.checkDay and checkOut<#query_Table_Temp.checkOut and out2=0 and leave2=0 and rest=0 and evection=0) as 早退人次
,(select count(*) from #attend_Temp where checkDay=#query_Table_Temp.checkDay and checkIn is null and out1=0 and leave1=0 and rest=0 and evection=0) as 上班未登记人次
,(select count(*) from #attend_Temp where checkDay=#query_Table_Temp.checkDay and checkOut is null and out2=0 and leave2=0 and rest=0 and evection=0) as 下班未登记人次
from #query_Table_Temp
drop table #query_Table_Temp
drop table #attend_Temp
set nocount off
end
GO
