I needed to run a conditional SQL that include a value from a global variable
set prior to the task. For some reason, the conditional (IF) and the global
variable couldn't work together. One or the other by itself is OK. Below
are the SQL I am using:

Case 1 works (with IF):
IF ((SELECT COUNT(*)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC <
(SELECT MAX(EventStampUTC)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC < '9999-12-12')

)>5000)
PRINT 'OK'

Case 2 also works (with global variable):
SELECT COUNT(*)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC <
(SELECT MAX(EventStampUTC)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC < '9999-12-12')
AND EventStampUTC > DATEADD(s, 1, ?)

Case 3 gets "syntax error or access violation" (with both IF and global
variable):
IF ((SELECT COUNT(*)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC <
(SELECT MAX(EventStampUTC)
FROM v_AlarmEventHistoryInternal
WHERE EventStampUTC < '9999-12-12')
AND EventStampUTC > DATEADD(s, 1, ?)
)>5000)
PRINT 'OK'

The Design time is less than forgiving and not as strong as the runtime so
when I encounter things like this with the ? place holder I usually build
the statement bedore the task and assign.

Like this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Delphine" <Delphine (AT) discussions (DOT) microsoft.com> wrote