warning message: DSD.BCIPut call to SQLExecute failed.

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 1:46 pm

HI ALL,

I have a simple server job, plain simple source to target mapping with no transformations in transformer. every time i run the job, am getting the following WARNING MESSAGES FOR ALL THE ROWS. majority of the rows are getting transferred to the reject with a few being written to the target table. can someone reflect and help me with it.

ABC..FormatContractMix.LoadTdmContractMix: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE dm_ABC SET current_ind = ?, load_job_name = ?, load_date = convert(smalldatetime, ?) WHERE (contract_type = ? AND sale_type = ? AND sale_source_type = ? AND sale_source = ?)
16 Rows affected.

contract_type = "XXX"
sale_type = "YYY"
sale_source_type = "ABX"
sale_source = "DFG"
current_ind = "Y"
load_job_name = "ABC"
load_date = "YYYY-MM-DD"

chulett · Post by **chulett** » Fri Mar 11, 2011 2:02 pm

The key piece of information is the 16 Rows affected part, which is an extra informational message the database is returning and which causes DataStage to generate the warning. What database is this? Are you purposefully trying to update multiple records at a time?

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 2:08 pm

chulett wrote:The key piece of information is the 16 Rows affected part, which is an extra informational message the database is returning and which causes DataStage to generate the warning. What database is this? Are you purposefully trying to update multiple records at a time?

am using sql server 2008 for both source and target..using update and then insert for the loading methodology...

ray.wurlod · Post by **ray.wurlod** » Fri Mar 11, 2011 2:48 pm

I don't see any value in that recordset that could be converted to smalldatetime.

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 2:49 pm

ray.wurlod wrote:I don't see any value in that recordset that could be converted to smalldatetime. ...

actually that is for the load_date column for which in transformer am using the date.tag(@date) in derivation to get the current date..

ray.wurlod · Post by **ray.wurlod** » Fri Mar 11, 2011 3:01 pm

OK, then I'll concur with Craig, because DataStage expects the database server (or ODBC driver) to be silent on request but yours is returning informational content. So DataStage alerts you to the fact that unexpected output has been received.

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 3:07 pm

ray.wurlod wrote:OK, then I'll concur with Craig, because DataStage expects the database server (or ODBC driver) to be silent on request but yours is returning informational content. So DataStage alerts you to the fact that unexpected output has been received.

alright, so what exactly am I to do, to get rid of those warning messages, and how will it effect the whole process...
sorry to ask such novice questions, but I am one

chulett · Post by **chulett** » Fri Mar 11, 2011 4:34 pm

Answer my question, please... are you intentionally updating multiple records in the target table with each record you process?

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 4:35 pm

chulett wrote:Answer my question, please... are you intentionally updating multiple records in the target table with each record you process?

by multiple do u mean DUPLICATES...

chulett · Post by **chulett** » Fri Mar 11, 2011 4:40 pm

Call it what you like, I'm trying to assertain if you are updating 16 rows in the one update you posted on purpose (i.e. it was expected) or by accident and it was an unexpected result. The former you may have to take up with your DBA while the latter could be because (for example) your key choice wasn't precise enough. That answer will help drive how to resolve this.

sush26283 · Post by **sush26283** » Fri Mar 11, 2011 4:51 pm

chulett wrote:Call it what you like, I'm trying to assertain if you are updating 16 rows in the one update you posted on purpose (i.e. it was expected) or by accident and it was an unexpected result. The former you may have to take up with your DBA while the latter could be because (for example) your key choice wasn't precise enough. That answer will help drive how to resolve this.

it certainly was an unexpected result..i didnt intend to do so

chulett · Post by **chulett** » Sat Mar 12, 2011 12:04 am

Then, as noted, see if you can correct the key fields you are using to properly identify the single record you are trying to update. Once your update only affects 1 record, the warnings will go away.