Parameter Mapping not working in SQL Execute task

Question

Parameter Mapping not working in SQL Execute task

ramadesai108

SSChampion

Points: 10609
More actions
May 17, 2011 at 10:11 am

#240404

Have a input parameter with Date variable and parameter name is "0". My statement is:
UPDATE g
SET g.DateTime = s.DateTime
,g.[OrderID] = s.[OrderID]
FROM [DB1] g
JOIN [DB2] s
ON g.id = s.id
WHERE s.ModifiedDate > ?
It gives me "invalid object name 'g'"
But when I remove the where clause, the update works.
This proves that the parameter mapping is not working in SQL Execute Task. Any ideas?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

The problem is that you are inserting a datatime value directly in your SQL statement, giving the following result (for today's date):

UPDATE g

SET g.[DateTime]= s.[DateTime]

,g.[OrderID]= s.[OrderID]

FROM

[DB1] g

JOIN

[DB2] s

ON g.id = s.id

WHERE s.ModifiedDate > 20110518

while it should be:

UPDATE g

SET g.[DateTime]= s.[DateTime]

,g.[OrderID]= s.[OrderID]

FROM

[DB1] g

JOIN

[DB2] s

ON g.id = s.id

WHERE s.ModifiedDate > '20110518'

So you left out the single quotes. The problem is that you just can't put single quotes around the question mark, because it will be parsed as a string instead of a parameter placeholder.

So your best bet is to create variable, populate it using the question mark and then use the variable in your query:

DECLARE @Date DATETIME;

SET @Date = ?;

UPDATE g

SET g.[DateTime]= s.[DateTime]

,g.[OrderID]= s.[OrderID]

FROM

[DB1] g

JOIN

[DB2] s

ON g.id = s.id

WHERE s.ModifiedDate > @Date

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

I added the declare statement and replaces the ? with @Date. When I parse the query, it gives me syntax error. If I just use ? instead of @Date, I still get syntax error. Somehow, it is not recognizing the parameter.

Thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

Forgot to mention that I also got the error: "The Declare cursor SQL construct or statement is not supported."

Thanks.

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 4

The question mark is not part of the SQL syntax, it is an OLE DB parameter placeholder. This means when you execute the SQL within the task, the OLE DB provider replaces all the question marks with the current parameter values before it sends the query to SQL Server.

So try the code, don't parse it, but just run the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

Koen,

I ended up fixing the issue as follows:

WHERE

CONVERT(VARCHAR,s.ModifiedDate) > ' + ? + '

That actually worked. Thanks for pointing me in the right direction. Have a nice day. 🙂

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 6

OK, glad that you got it working.

Just remember that if there is any index on the ModifiedColumn, the query optimizer won't use it anymore, as you converted the column to varchar. (if I'm not mistaken)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ramadesai108 SSChampion Points: 10609 More actions · Answer 7

That is correct that the optimizer will not use the index.