Hi, I posted this earlier today in the
microsoft.public.sqlserver.programming group, but I've just realised I
should probably have posted it here instead as it's probably more
relevant to this group. So here goes...


My problem is with a T-SQL statement, which itself is part of a DTS
Data Driven Query task (hence the question marks which are parameters).



UPDATE tblPosts SET strJobTitle=? WHERE intPostsRefno=
(SELECT intPostsRefNo FROM tblPersonPosts WHERE strPayrollFP10 = ? and
intPersonID = ?)


When I hit 'Parse/Show Parameters', as expected I get the error:
Parmeter information cannot be derived from SQL statements with
sub-queries. Set parameter information before preparing command.' So,
as I've done previously with DDQ's, I temporarily removed the subquery
brackets in order to parse it, giving me:


UPDATE tblPosts SET strJobTitle=? WHERE intPostsRefno=
SELECT intPostsRefNo FROM tblPersonPosts WHERE strPayrollFP10 = ? and
intPersonID = ?


However, when I now hit 'Parse/Show Parameters', I get the UNexpected
error messaege: Invalid column name 'intPersonID'. Now, I know this is
utter rubbish, because if I mod the above code to:


UPDATE tblPosts SET strJobTitle=? WHERE intPostsRefno=
SELECT intPostsRefNo FROM tblPersonPosts WHERE intPersonID = ?


(i.e. remove the first clause in the subquery) then it works fine. It
also throws the same error for the other field (strPayrollFP10) if I
swap them round.


Martin yesterday very kindly replied with two suggestions, but despite
being perfectly good SQL that should normally have worked, the parser
decided it was going to throw them out too. They are as follows:


1) UPDATE tblPosts
SET strJobTitle=? WHERE EXISTS
(SELECT 1 FROM tblPersonPosts
WHERE tblPosts.intPostsRefno=tblPersonPosts.intPostsRefN o
AND tblPersonPosts.strPayrollFP10=? AND
tblPersonPosts.intPersonID=?)


(Gives the error 'Parmeter information cannot be derived from SQL
statements with sub-queries. Set parameter information before preparing
command' error - even if I temporarily remove the sub-query brackets in
order to parse it.)


2) UPDATE tblPosts
SET a.strJobTitle=? FROM tblPosts a, tblPersonPosts b
WHERE a.intPostsRefno=b.intPostsRefNo
AND b.strPayrollFP10=? AND b.intPersonID=?


(Gives the error 'The column prefix b does not match with a table name
or alias used in the query')

Rob also earlier replied to the post in the other groupk, suggesting:

UPDATE a
SET a.strJobTitle=?
FROM tblPosts a JOIN tblPersonPosts b
ON a.intPostsRefno=b.intPostsRefNo
WHERE b.strPayrollFP10=? AND b.intPersonID=?

....but this just threw an "Invalid object name 'a'" error.


These errors quite simply should not be appearing as there is
absolutely nothing wrong with the syntax, and it's even more
frustrating when I've now effectively tried just about every valid way
of writing this - let's face it - quite simple SQL query.


I have trawled the net, Google groups, any books I could find,
consulted with colleagues, experimented, juggled the SQL, and every
other thing I could think of, but I'm now completely at a dead end and
running out of time to get this cracked. I just don't understand why
this is throwing the errors as this is not advanced SQL by any means;
and if it doesn't like the parameters being there, then how are we
supposed to use anything but the most basic of DDQ queries?


If anyone could possibly point out what I'm doing wrong, or how I could
get around this, I would be so, so grateful. Thanks in advance if
anyone can help.

I have just found a post from Allan Mitchell, in reply to another
question -
http://groups.google.co.uk/group/mic...8812591fa3f299
- in which Allan says:

"The problem will no doubt still be the fact that the design time
interface is confused by placing the ? placeholders within a subquery.
There is an easy way around this though and it is more elegant than
that we have in DTS 2000. You can create an Expression that maps to
the SQLStatementSource property of the task. This will allow you to
use your variables. It also has a validation window that will let you
know what the string looks like before running the task itself. "

I feel that there may finally be a glimmer of hope in my epic struggle
to find an answer to this! I don't know if this IS the solution, but
it's the only promising lead I've been able to find. The only thing is,
I don't understand what the reference to an 'Expression' is...does
anyone know what this is, how I access it, and how I use it with my DDQ?