February 12, 2004 at 1:11 am
Hi all,
strange problem here
I have a storedproc on SQLSERVER :
CREATE PROCEDURE [sp_Overdue_Trans_2]
AS
SELECT TOP 100 PERCENT tblopdracht.*, DATEDIFF(day, VrgDueDateTrans, GETDATE()) AS OverdueTrans,
dbo. udf_GetWorkingDays(duedatetrans,current_timestamp)
AS ODTRA,
dbo.udf_GetWorkingDays(DueDatPdi,current_timestamp) AS ODPDI
FROM dbo.tblOpdracht
WHERE (archief=0) and (DATEDIFF(day, DueDateTrans, GETDATE()) <> '' and isnull(status,'')<>'AFGW' and isnull(status,'') <> 'ANNUL' and isnull(status,'')<>'AFGWZG')
AND swt_TypeTrans = 1
GO
when i run this query it works fine ! no problem.
BUT when i call this storedProc in Access :
execute sp_overdue_Trans_2
then i get the same amount of records but the 2 last fields ODTRA and ODPDI are empty ! and when i run the stp on SQLServer, these fields are filled in properly !
well i figured out that it probably has something to do with the userdefined function
below is the code
CREATE FUNCTION dbo.udf_GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
-- IF (DATEDIFF(day, @StartDate, @EndDate) < 1)
-- BEGIN
-- RETURN ( 0 )
-- END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) ,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
declare @Holidays int
declare @ReturnValue int
SELECT @Holidays=Count(1) FROM tblVerlof WHERE Datum BETWEEN @StartDate AND @EndDate
select @ReturnValue = @WorkDays-@Holidays
if @returnvalue <0 select @returnvalue = 0
RETURN ( @returnvalue )
END
anyone any idea?? i'm totally lost here !
TIA
February 13, 2004 at 9:14 am
I am pretty sure no way Access knows what's going on inside you stored proc.
So what's left, maybe security issue? Is the owner of your store proc dbo? Try grant exec on the udf to public?
Finally, I don't think this is your problem, but in general using sp_ for proc name is bad idea, SQL handles them differently than procs with any other name. Consider sp_ to mean "special" & don't use it unless that's the effect you're after (and even then the proc probably ought to be a master DB proc). I apologize if this is patronizing & you are already aware of all this...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply