Problems with Execute SQL Task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Problems with Execute SQL Task

Author

Topic

Whalensdad
Starting Member

2 Posts

Posted - 2008-02-05 : 07:38:17

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?

Whalensdad
Starting Member

2 Posts

Posted - 2008-02-05 : 15:39:16

It ended up being the OLE provider I chose.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources