I am having trouble with an Execute SQL Task in my DTS.

When I create a simple SQL statement as below - the single result count is available as an Output Variable:
SELECT COUNT(*) FROM MYTABLE

However the SQL statement below that returns just one result count, doesn't have any available Output Variables to me. The Query works great in QA:
SELECT COUNT(*)
FROM Audittrail a
JOIN TransactionType tt ON tt.TransactionType = a.TransactionType
JOIN TransactionSession ts ON ts.TransactionSessionID = a.TransactionSessionID
AND ts.TransactionTime BETWEEN CONVERT(Char(10), DATEADD(d, - 1, GETDATE()), 101)
AND CONVERT(Char(16), DATEADD(d, - 1, GETDATE()), 101) + '23:59:59'
LEFT JOIN EventLog e ON a.EventID = e.EventID
WHERE tt.JobCode = 'Enforcement' AND a.Status = 'Failed.'

What happens if you say

SELECT COUNT(*) as Amount
FROM Audittrail a
JOIN TransactionType tt ON tt.TransactionType = a.TransactionType
JOIN TransactionSession ts ON ts.TransactionSessionID =
a.TransactionSessionID
AND ts.TransactionTime BETWEEN CONVERT(Char(10), DATEADD(d, - 1,
GETDATE()), 101)
AND CONVERT(Char(16), DATEADD(d, - 1, GETDATE()), 101) + '23:59:59'
LEFT JOIN EventLog e ON a.EventID = e.EventID
WHERE tt.JobCode = 'Enforcement' AND a.Status = 'Failed.'

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Joe Horton" <horj235 at lni dot wa dot gov> wrote

I am having trouble with an Execute SQL Task in my DTS.

When I create a simple SQL statement as below - the single result count is
available as an Output Variable:
SELECT COUNT(*) FROM MYTABLE

However the SQL statement below that returns just one result count, doesn't
have any available Output Variables to me. The Query works great in QA:
SELECT COUNT(*)
FROM Audittrail a
JOIN TransactionType tt ON tt.TransactionType = a.TransactionType
JOIN TransactionSession ts ON ts.TransactionSessionID =
a.TransactionSessionID
AND ts.TransactionTime BETWEEN CONVERT(Char(10), DATEADD(d, - 1,
GETDATE()), 101)
AND CONVERT(Char(16), DATEADD(d, - 1, GETDATE()), 101) + '23:59:59'
LEFT JOIN EventLog e ON a.EventID = e.EventID
WHERE tt.JobCode = 'Enforcement' AND a.Status = 'Failed.'

That approach works well - thanks.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote