Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parameter Information cannot be derived from SQL statements with sub-select queries Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:03 PM
Points: 321, Visits: 1,287
Trying to execute the SSIS package in BIDS-2005 and keep getting same error. While same package works in BIDS 2008 very well. I downgraded package from 2008 to 2005 also made it from scratch using BIDS-2005 but still same error.

bypassprepare is TRUE also

passing BatchID1 as ? in below querry. BatchID is column in dbo.my_table.

trying to load row count from my_db.dbo.xyz to dbo.my_table

UPDATE dbo.my_table SET SrcRecCnt = (select COUNT(*) from My_db.dbo.xyz)
WHERE BatchID=?"

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.

If need to create expression can suugest expression for this query ?

Any one has face this issue and can help me with solution would be great help

Thanks





Aim to inspire rather than to teach.
SQL Server DBA
Post #1444378
Posted Friday, April 19, 2013 8:38 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 1,485, Visits: 1,035
Yeah, you need to create an expression.
Its fairly easy tho.
Add a new parameter, call it something like 'strUpdateSQL'
change it to a string.
Make sure you Properties Pane is open (F4)
Click on the parameter value. No go to Properties pane, Change the evaluateasexpression property to True.
Click the Expression Elipses.

and your expression will be something like:
"UPDATE dbo.my_table SET SrcRecCnt = (select COUNT(*) from My_db.dbo.xyz)
WHERE BatchID=" + (DT_STR, 10, 1252) @[User::BatchID]


@[User::BatchID] is the Parameter you want to use for your batch ID.
Post #1444461
Posted Friday, April 19, 2013 9:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:03 PM
Points: 321, Visits: 1,287
HI Ray
I was able to solve this task with your great help . i really really appereciate it .
10000 hats of to you and your skills.


Aim to inspire rather than to teach.
SQL Server DBA
Post #1444487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse