Execute SQL Task with no rows affected

Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

What error or errors are returned when the task fails? Can you please copy and paste the error output from BIDS or from a log file into a reply?

Are you attempting to store the resultset from the query in a variable?

Can you run the query successfully (where zero rows are affected) without error using SQL*Plus or another query tool?

This information will likely be invaluable in helping track down the source of the error.

|||

Thank you for your reply.

This is the error:

"Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "update TableName set FieldA=SYSDATE where FieldB is Null" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task."

No, I didn't attempt to store the resultset, I just executed the query.

and yes, I ran the query successfully using SQL*Plus with the result : "0 rows updated."

Please your help.

Thanks

Noam

|||

I found it.

My connection was odbc.

I changed the connection to Ado.Net provider for odbc and it works without fail.

Why?

It's another question.

Thank you anyway.

Noam