Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MarkyBoy666
Starting Member
14 Posts |
 Posted - 2006-05-10 : 03:29:59
|
I am hoping someone can help me find a solution for this as I've been banging my head against a brick wall for several days with this now, and I quite simply need to get it finished!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. Somebody yesterday very kindly replied to this question on another forum, 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.intPostsRefNo 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') 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, usenet 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. Thank you so much in advance!  MarkyBoy |
|
|
|
|
|
|
|
DTS task - help needed with parameters in query