April 8, 2008 at 6:34 am
Since I'm running the SSIS package on the same machine I ran the SMS query on, I'm not thinking it's the date format, but I'll check it.
April 8, 2008 at 7:10 am
Yes, but ... I'm going to try a few other things first. It'd take me a couple of hours to script it all out and I'd end up editing so much out it would take me a long time, and the issue appears to be in the function or its permissions. It would make sense that the date being passed in is in the wrong format except for the stuff I said before. The code for the function follows. The parameters being passed in are of the correct type (I checked.) This is a really simple function -- nothing complicated going on here, just a lazy obsessive compulsive programmer.
Thanks!
USE [cashier]
GO
/****** Object: UserDefinedFunction [dbo].[fVoidRequestDate] Script Date: 04/08/2008 08:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:aza1
-- Create date: 3/12/2008
-- Description:Checks to see if record was voided on requested date.
--Returns 1 if voided on requested date, 0 if not
-- =============================================
CREATE FUNCTION [dbo].[fVoidRequestDate]
(
@VoidDate datetime,
@RequestDate varchar(10)
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result =
CASE
WHEN convert( varchar(12), @VoidDate, 101 ) = @RequestDate THEN 1
ELSE 0
END
RETURN @Result
END
April 8, 2008 at 7:37 am
Honestly, it sounds like the function is simply not reaching the first statement. It sounds like it's always evaluating the ELSE when other people run it.
Which doesn't make sense if you're using the exact same parameter as the other person, but that's where I would start tracing it. Put in dummy values in the THEN clause to verify this. Or the ELSE clause.
April 8, 2008 at 7:47 am
Removing the function and using BETWEEN to check the date is working for all users. I haven't solved the mystery, but now the SSIS package is creating the correct data in the flat file, so I'll go with this and deal with the mystery on a day when I don't have quite so much to do.
Thanks for all your help
April 8, 2008 at 7:53 am
My guess here is still date formats.
I tried out your function and passed in 2 identical dates which I believe is how SSIS converts a date to a string.
SELECT [dbo].[fVoidRequestDate]('2008-04-08','2008-04-08') but it returns 0. It defo looks to me like a problem with the format of the datestrings you're passing into your function. I'd be tempted to change the function and the sproc so that you're comparing datetimes instead, then at least you'd be able to rule out collation and conversion type issues.
Good luck with that. I'd be interested to know what it was when you solve it...
Kindest Regards,
Frank Bazan
April 8, 2008 at 7:58 am
BETWEEN has a very nasty habit of dropping records. I don't use it at all. I prefer Greater Than / Equal and Less Than or the reverse. This makes sure that those weird time parts of the DateTime fields don't cause records to drop out of the recordset.
Usually this happens around the midnight hour or the 00:00:000 time frame. But I've seen it happen in other circumstances. I totally get that this might have been your problem all along.
April 8, 2008 at 7:59 am
Yes, it must be, because I also tried substituting
AND CONVERT(varchar(10),oc.void_dtt,101) = CONVERT(varchar(10),@ExportDateString,101)
in the tvf and again, when I executed the stored procedure, I got the correct values, but when I ran the package, it wrote all positive values to the flat file.
April 8, 2008 at 8:07 am
Here's a question, what is your mapping on the Flat File Connection set up as? Is it set to a datatype that doesn't allow negative values?
April 8, 2008 at 8:23 am
I used to have problems with BETWEEN until I wrote my earliest and lastest functions. That's because the latest datetime returned by MAX(datetime_value) is 23:57 something, so any records created between 23:57:999 and 24:00 aren't picked up by the Between function. I had to had to pretzel my way to 23:59:999 with a function (adding to processing time)[Microsoft must have a reason for this, right?].
FUNCTION [dbo].[fLatestDatetime]
(
@Date Datetime
)
RETURNS Datetime
AS
BEGIN
DECLARE @LatestDatetime Datetime
Set @LatestDatetime = CONVERT(Datetime,(CONVERT(Varchar(10),@Date,101) + ' 23:59:59.999'))
RETURN @LatestDatetime
END
Thanks again for all your help.
April 8, 2008 at 8:24 am
No, once I removed the fVoidRequestDate() function, everything (including the negative sign) flowed through just fine. The flat file is a text file, it takes whatever you give it.
April 8, 2008 at 8:31 am
Not necessarily. What about the difference between signed & unsigned integers? If you have columns in your flat file connection defined as unsigned, this could be a problem.
Of course, I'm making an assumption that signed verses unsigned means a negative or a positive difference.
April 8, 2008 at 8:49 am
It's a string field in the flat file. The CTE in the stored procedure is converting the value from money to char(). If the money amount is negative, the character amount contains the sign. When the field arrives at the dts package, it is a character field. So a char field is being sent to a string field. That wasn't the issue.
The problem was in a function that checked the date the transaction was voided against the date requested by the user. For some reason, no matter what, the function checking the dates was always evaluating to 0, the dates not matching, when the sql was run by anyone (including the dts package) other than the dba or me.
The problem was resolved by removing the function from the code and substituting a BETWEEN statement.
April 8, 2008 at 9:14 am
Okay. Just verifying. It never hurts to check every angle just to be sure. @=)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply