Multiple parameters in a query using a OLE DB COMM

问题:

I am trying to do this update in SSIS using a OLE DB COMMAND:

UPDATE 
TABLE_A
SET 
COLUMN_C = CONCAT(?,' ',?)
FROM 
TABLE_A INNER JOIN TABLE_B ON
TABLE_A.ID = TABLE_B.ID

But when I try to map the parameters I am getting this message:

"the parameter type cannot be deduced because a single expression contains two untyped parameters"

I do not know if I am doing something wrong with this query or if it is not possible to use more than one parameter in one single sentence.

I am using VS 2010

回答1:

I like @Billinkc answer and comments. I just want to show that there is way to workaround the OLEDB provider limitation.

declare @a as varchar(100) = ?
declare @b as varchar(100) = ?
UPDATE 
TABLE_A
SET 
COLUMN_C = CONCAT(@a,' ',@b)
FROM 
TABLE_A INNER JOIN TABLE_B ON
TABLE_A.ID = TABLE_B.ID

回答2:

Parameter Binding with SQL Server 2012 and later

SQL Server 2012 and later have changed the SQL parser or optimizer to use a more stringent method of determining the unkown parameter input types.

This results in SQL statements such as "WHERE col1 >= ? + ?" to fail with an error such as mx.ODBC.Error.ProgrammingError: ('42000', 11503, "[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The parameter type cannot be deduced because a single expression contains two untyped parameters, '@P1' and '@P2'.", 10191).

A Solution

1. use explicit casts to tell the SQL parser which type to expect from the right hand side operation, e.g. "WHERE col1 >= ? + CAST(? as int)". This gives the second argument an explicit type and allows the parser to deduce the type of the result,

回答3:

The problem is one of metadata. SSIS, really cares about it. CONCAT, new in 2012 is amazing in that it lets you concatenate everything regardless of data type without worrying about the backwards-to-dot-net-developers data type precedence.

I suspect you need to

A) add a Derived Column Transform to create the concatenated value. Yes, this means you have to check for NULLs, data type precedence and all the other fun things.

B) Update your query to be something like

UPDATE 
TABLE_A
SET 
COLUMN_C = ?
FROM 
TABLE_A INNER JOIN TABLE_B ON
TABLE_A.ID = TABLE_B.ID
WHERE
    SomethingUniquelyIdentifying = ?

and then map in your value from A) to the 0th ordinal and your business key to the second (otherwise you're going to update every value in every row to the current value in the buffer)