Execute SQL Task with UPDATE Statement
-
I am trying to run the following UPDATe statement in an Execute SQL task. I need to use 2 global variables as parameters, but I keep getting a syntax error when ever I try to click either the Parameters Button or the Parse Query button.
UPDATE MyTable
SET Code = o.Code1
FROM MyTable as a, OtherTable AS o
WHERE a.numberPK = s.MyTableNumberFK
AND s.BatchGroup = ?
AND s.EnteredDate = ?
This works fine when I replace the "?"s with single quotes. The Parse Query checks out, but the parameters button gives an error stating that "The SQL Statement does not contain any parameters". As soon as I put the question marks in place, I get an error.
I am using SQL 2K SP3a.
Any help is greatly appreciated. I am going nuts!
-
The parsing of SQL tasks in DTS seems to be preety basic (i.e. rubbish!) - it deons't like all sorts of things like DECLARE's etc etc - but can hanlde stored procedures so why not e.g.
CREATE PROCEDURE PROC_UpdateMyTable
(
@BatchGroup char(10), -- ??? hatever type it is
@EnteredDate datetime
)
AS
UPDATE MyTable
SET Code = o.Code1
FROM MyTable as a, OtherTable AS o
WHERE a.numberPK = s.MyTableNumberFK
AND s.BatchGroup = @BatchGroup
AND s.EnteredDate = @EnteredDate
and in DTS
Exec PROC_UpdateMyTable ?, ?
James Horsley
Workflow Consulting Limited -
Thanks for the Reply. I will try that method instead. I wonder if this is more clear in 2K5, or at least a little more consistent.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply