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

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

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?