Problems with Execute SQL Task

• Lee Polikoff

  Mr or Mrs. 500

  Points: 576

  More actions

  February 4, 2008 at 7:55 pm

  #154110

  I have an Execute SQL task on an Oracle database that I want to perform the following (Note:This SQL query works fine in a Oracle environment).

  update MILESTONE_DATA x

  set LASTMONTHSACTUAL =

  (select ACTUAL from MILESTONE_DATA

  where PROGRAM = 'abcdef' and

  NODE_NAME = x.node_name and

  G_COLLECTDATE = ADD_MONTHS(x.g_collectdate,-1))

  where LASTMONTHSACTUAL is null;

  I create the SQL statement as a string and pass it in as a variable to the Execute SQL Task. The Execute SQL Task is set up as follows:

  ResultSet: None

  Connection Type: OLE DB

  Connection: ->Points to my database

  SQLSourceType: Variable

  SourceVariable: User::MPostSQLString

  BypassPrepare: True

  Whenever the task runs it generates the following error:

  [Execute SQL Task] Error: Executing the query "update MILESTONE_DATA x set LASTMONTHSACTUAL = (select ACTUAL from MILESTONE_DATA where PROGRAM = 'abcdef' and NODE_NAME = x.node_name and G_COLLECTDATE = ADD_MONTHS(x.g_collectdate,-1)) where LASTMONTHSACTUAL is null;" failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  If I copy the SQL Statement from the error message and run it in TOAD for ORACLE it works fine.

  How do I run a SQL statement like this using the Execute SQL Task?

• Lee Polikoff

  Mr or Mrs. 500

  Points: 576

  More actions

  February 5, 2008 at 1:38 pm

  #775317

  It ended up being the OLE Provider.