Parameter Information cannot be derived from SQL statements with sub-select queries

Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.

Here's the query:

update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'

Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?

I guess you are using OLE DB connection. I have seen similar problems with Execute SQL task involving complex queries with parameters and OLE DB connection.

A simple work around is to set "ByPassPrepare" to true. Try this and let us know if it helped. Otherwise, I can investigate the issue more to suggest other work arounds.|||Kaarthik's answer is most likely the best way to go, however since you state that

Thanh Duong wrote:

i'm having this almost exact query in alot of places. Any way around this?

you may want to wrap this command in a Store Proc or View anyway. Then the parameters should work fine against the SP too.|||

Still failed with bypassprepare=true. I have a var mapped to parameter name 0 (like how i'm doing it with all other execute sql task). Any other solutions not involving sprocs?

Error: 0xC002F210 at Update Last Do_Seq_No, Execute SQL Task: Executing the query "update GCDE_SEQ

set LAST_NO =

(select max(CAST(OLD_DO_NO AS INT))

from DONL_DO_HDR)

,UPDT_USER = ?

,UPDT_DT = getdate()

where SEQ_NM = 'DO_SEQ_NO'" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||I could not repro your problem.

In "Execute SQL Task" I set the following properties:
ConnectionType=OLE DB
SQLStatement= update UserTable
set age = (select max(CAST(age AS INT)) from AgeTable)
,[name] = ?
,somedate = getdate()
where sex = 'M'
BypassPrepare=True

Parameter Mapping: Variable Name=nameVar, Direction=Input, Data Type=VARCHAR, Parameter Name=0

nameVar is a String variable.

I could successfully execute this task.

If I set BypassPrepare=False, the same task fails throwing the error message "[Execute SQL Task] Error: Executing the query "update UserTable
set age = (select max(CAST(age AS..." failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The tables I used for this taks are created as follows:

create table UserTable (
age int,
[name] varchar(10),
somedate datetime,
sex varchar(2)
)
create table AgeTable (
age varchar(5)
)|||THANKS FOR TRYING, ILL MESS AROUND WITH IT SOME MORE AND SEE WHAT I'M DOING WRONGsql