Hi all,
As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.
I can do this in two separate Execute SQL tasks as follows:
Task 1 syntax
DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'
Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0
UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'
However I cannot make this work with a single SQL statement such as
DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'
because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.
Is this behaviour by design, is it a bug, or is there something I've missed?
Thanks as ever,
Richard
Richard,
What error message do you get?
-Jamie
