SQL Command Not Assigning Parameter

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL Command Not Assigning Parameter

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2008-08-04 : 08:40:24

I have the following SQL Command in DataFlow. The Parameter is on the last line of the command. When I hit the Parameter button to assign the value, I get the following error msg below. If I stick in a real value like '2008-07-25', it works perfectly. I've never had problems with SQL command and Parameters before in SSIS. I don't know what about this statement is causing SSIS not allowing me to reference the parameter. Please help. It would be wonderful if someone would copy and paste this command into their own dataflow and you'll see exactly what I'm talking about. You don't have to have a real DB. Just try it and you'll see it will not even allow you to assign the parameter.
Thanks!

----
WITH MoxyTotalAlloc(OrderID, NumShares) AS (SELECT OrderID, SUM(AllocQty) AS Expr1
FROM MoxyAllocation
WHERE (OmnibusID = 3)
GROUP BY OrderID), MaxMoxyFill(OrderID, ExecTime) AS
(SELECT OrderID, MAX(CONVERT(VarChar, FillDate, 114)) AS FillDate
FROM MoxyFill
GROUP BY OrderID)
SELECT CONVERT(VarChar, MO.OrderDate, 23) AS TradeDate, MoxyTranType.TranType AS Side, MTA.NumShares, UPPER(MO.Symbol) AS Ticker,
CASE Limit WHEN 'MKT' THEN 'MKT' ELSE 'LMT' END AS MktLimit, CASE Limit WHEN 'MKT' THEN '' ELSE Limit END AS LimitPX,
CASE CONVERT(VarChar, GoodThroughDate, 23) WHEN '1900-01-02' THEN 'DAY' ELSE 'GTC' END AS DayGTC, UPPER(MO.OrderBrokerID)
AS Broker, MTA.NumShares AS ExecNumShares, MoxyTradeSummary.TotalFillCost / MoxyTradeSummary.TotalFillQty AS ExecPx,
MoxyTradeSummary.TotalBillComm / MoxyTradeSummary.TotalFillQty AS Comm, CONVERT(VarChar, MO.OrderDate, 114) AS OrderTime,
MMF.ExecTime
FROM MoxyOrders AS MO INNER JOIN
MoxyTotalAlloc AS MTA ON MO.OrderID = MTA.OrderID INNER JOIN
MaxMoxyFill AS MMF ON MO.OrderID = MMF.OrderID INNER JOIN
MoxyTranType ON MO.TranCode = MoxyTranType.TranCode AND MO.TranCode = MoxyTranType.TranCode INNER JOIN
MoxyTradeSummary ON MO.OrderID = MoxyTradeSummary.OrderID
WHERE (CONVERT(VarChar, MO.OrderDate, 23) = ?)

---------
Error Message:
[url]http://img329.imageshack.us/img329/6859/err1nz1.jpg[/url]

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:36:46

As suggested by error message what you should do is to create a string variable to hold the entire query string and use SQL Command from variable option in DataFlow
the string will be
'WITH MoxyTotalAlloc(OrderID, NumShares) AS (SELECT OrderID, SUM(AllocQty) AS Expr1
FROM MoxyAllocation
WHERE (OmnibusID = 3)
GROUP BY OrderID), MaxMoxyFill(OrderID, ExecTime) AS
(SELECT OrderID, MAX(CONVERT(VarChar, FillDate, 114)) AS FillDate
FROM MoxyFill
GROUP BY OrderID)
SELECT CONVERT(VarChar, MO.OrderDate, 23) AS TradeDate, MoxyTranType.TranType AS Side, MTA.NumShares, UPPER(MO.Symbol) AS Ticker,
CASE Limit WHEN 'MKT' THEN 'MKT' ELSE 'LMT' END AS MktLimit, CASE Limit WHEN 'MKT' THEN '' ELSE Limit END AS LimitPX,
CASE CONVERT(VarChar, GoodThroughDate, 23) WHEN '1900-01-02' THEN 'DAY' ELSE 'GTC' END AS DayGTC, UPPER(MO.OrderBrokerID)
AS Broker, MTA.NumShares AS ExecNumShares, MoxyTradeSummary.TotalFillCost / MoxyTradeSummary.TotalFillQty AS ExecPx,
MoxyTradeSummary.TotalBillComm / MoxyTradeSummary.TotalFillQty AS Comm, CONVERT(VarChar, MO.OrderDate, 114) AS OrderTime,
MMF.ExecTime
FROM MoxyOrders AS MO INNER JOIN
MoxyTotalAlloc AS MTA ON MO.OrderID = MTA.OrderID INNER JOIN
MaxMoxyFill AS MMF ON MO.OrderID = MMF.OrderID INNER JOIN
MoxyTranType ON MO.TranCode = MoxyTranType.TranCode AND MO.TranCode = MoxyTranType.TranCode INNER JOIN
MoxyTradeSummary ON MO.OrderID = MoxyTradeSummary.OrderID
WHERE (CONVERT(VarChar, MO.OrderDate, 23) = '+ @User::Variable +')'

and @User::Variable is another variable holding your required value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources