Hi

I have a Data Driven Query task including a lookup. If the lookup
returns anything, then the Update query should be run - otherwise, skip
row.

My ActiveX transformation looks like this (in part):

MatchCatID=DTSLookups("LkpMatchingMDSCatalogueRow" ).Execute(DTSSource("MDSCatID"),DTSSource("MDSUpda ted"))

If IsEmpty(MatchCatID) Then
Main=DTSTransformstat_SkipRow
ELSE
Main = DTSTransformstat_UpdateQuery
End If

The Lookup is like this. (I've checked it is named right, as in the
VBScript code).

SELECT CatID
FROM tblCatalogue
WHERE (CatID = ?) AND (ABS(DATEDIFF(second, LastUpdated,
CONVERT(datetime, ?))) < 2)

I've tested this SQL in Query Analyser - works fine.

Originally the second condition in the lookup was just LastUpdated=?.
But VBScript has its own little weirdnesses - in another DTS package it
updates the LastUpdated column, losing the millisecond part in the
process. So the comparison on = doesn't work anymore.
In its second version, the lookup didn't have the CONVERT(datetime, )
around the ?. I thought I'd put it in in case VBScript was being
useless about dates again.

Both the source and binding columns concerned are type datetime.

What's happening when I run the step is that the lookup is _never
returning anything_. It's not a problem with Update query, as the
transformation is setting Main=...SkipRow every time. I've specified
an error log for the DDQ and it shows no errors.

I've tried to trace exactly what the DDQ is doing in the lookup. But
all I get in Profiler is lots of "Exec prepared SQL" events - which is
fair enough, but there's no more detail - surely I should be able to
see the parameters being passed to the Prepared SQL statement? I'm
showing TextData in the trace, and can't see any other suitable columns
that might show me the interesting bit of the event.

Can anyone suggest what might be going on?

thanks

Seb

I changed my Lookup SQL

FROM:

SELECT CatID
FROM tblCatalogue
WHERE (CatID = ?) AND (ABS(DATEDIFF(second, LastUpdated,
CONVERT(datetime, ?))) < 2)

TO:

SELECT CatID
FROM tblCatalogue
WHERE (CatID = ?) AND (? BETWEEN DATEADD(second, - 1, LastUpdated)
AND DATEADD(second, 1, LastUpdated))

and now it works. I had a hunch that maybe either DTS Lookups or
whatever it is that Prepares SQL doesn't like sticking parameters too
far into functions, especially nested functions, and prefers them
standing "bare". Don't know if there's any sense in that, but anyway
the latter SQL above works, and the former doesn't.


sebt wrote: