3093

Multiple parameters in a query using a OLE DB COMM

Question:

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

Answer1:

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

Answer2:

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,


Answer3:

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)



Recommend

  • Implementing ignoredProperties() on both a Object subclass and its own subclass
  • Object Browser can't browse my own solution?
  • Default parameter using another parameter
  • Memory usage of storing strings as varchar in MySQL
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to find data from last week in MySQL
  • mapping between two ontologies
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • custom string delimiters stringtemplate-4
  • Dynamically set LESS variables from user settings
  • WARNING:root:Some characters could not be decoded, and were replaced with REPLACEMENT CHARACTER. Wit
  • Relative paths. baseUrl and paths not working on ionic2 - angular2
  • How to display a tooltip according to mouse position? - JavaFX
  • Watson Conversation - Why is the ANYTHING ELSE node not chosen
  • Merge Module leaving files during uninstall
  • Identifier too long in Oracle
  • Connecting bluetooth device to windows phone 8 application
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • Compress a file with GZipStream while maintaining its meta-data
  • Is there a way to dynamically embed PDF Files in a JSP pulled from the file system?
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Android device acting as an accessory
  • Implicit joins and Where in Doctrine - how?
  • I18n locale disregarding fallbacks
  • converting text file into xml using php?
  • Eliminate partial duplicate rows from result set
  • Force show.bind execution
  • Unable to decode certificate at client new X509Certificate2()
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • PHP CURL timing out but CLI CURL works
  • With Hadoop, can I create a tasktracker on a machine that isn't running a datanode?
  • Get one-time binding to work for ng-if
  • Symfony2: How to get request parameter
  • ORA-29908: missing primary invocation for ancillary operator
  • Jquery - Jquery Wysiwyg return html as a string
  • QuartzCore.framework for Mono Develop
  • Arrays break string types in Julia
  • WPF Applying a trigger on binding failure
  • Java static initializers and reflection