I'm having a problem getting bind variables to work correctly in an ODBC Connector stage which is performing an update to a SQL Server 2012 table.
The SQL I'm trying to get it to execute is :
Code: Select all
update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk 'orchestrate.MediaFilePath' ,SINGLE_BLOB) a)
where Id=orchestrate.Id;
Write mode of Connector is set to 'Update'. I have the "Id" column defined as a key, and the "MediaFilePath" as a non-key column.
When executing the job, I'm seeing the following warning in the log, and records rejected by the ODBC Connector target stage:
"odbc_TBL_BlobData,0: Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed"
Running the SQL manually with bind variables replaced by actual values, as below, works as expected and updates the existing record with the content of the referenced file.
Code: Select all
update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk '\\fs01\public$\POC\Test.xhtml',SINGLE_BLOB) a)
where Id=10009935600000000001236;
Can anybody shed any light on why I might be seeing this warning? I suspect it's something to do with single-quoting the bind variable, although I've tried escaping the quote as below, but I still received the same warning:
Code: Select all
update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk ''orchestrate.MediaFilePath'' ,SINGLE_BLOB) a)
where Id=orchestrate.Id