DSXchange

Hello,

I am using ODBC connector stage to connect to Azure SQL Server and execute the 2 update statement in sequence as below:

UPDATE HWP.WRK_VHEW_VOL
SET EXP_DT = '2017-11-09' , STG_FL='D'
FROM HWP.WRK_VHEW_VOL A
WHERE EXISTS
(
SELECT 1 FROM HWP.WRK_VHEW_VOL B
WHERE A.ID=B.ID
AND B.STG_FL='D'
)
AND A.Start_DT < '2017-11-09'
AND A.EXP_DT = '9999-12-31';


UPDATE HWP.WRK_VHEW_VOL
SET EXP_DT = DATEAdd(Day, -1,'2017-11-09')
FROM HWP.WRK_VHEW_VOL A
WHERE EXISTS
(
SELECT 1 FROM HWP.WRK_VHEW_VOL B
WHERE A.ID=B.ID
AND B.STG_FL='U'
)
AND A.Start_DT < '2017-11-09'
AND A.EXP_DT = '9999-12-31';


The Job is finishing successfully but only 1st query is getting executed. The 2nd query does not trigger.

I executed both the queries using SQL server management Studio and it executes both the queries one after another.

Can you please let me know if there is a limitation in the ODBC connector(User-defined SQL) that I can pass only 1 query in the Statements Box or have done anything wrong.

Regards
Neeraj

[Edit: removed the orange highlighting so the red part would stand out more - Andy (especially since I'm colorblind!)]

As far as I know the Connector can only handle one SQL statement per update. Though the documentation refers to "SQL - Specifies one or more SQL statements to use to write data." it is really referring to having one for Inserts, one for Updates, etc.

Where are you specifying these update statements?
Is it Before/After SQL?. As per documentation, we can execute multiple statements

After SQL
If you set the Before/After SQL property to Yes and specify an SQL statement in the After SQL property, the connector runs the statement. If you specify multiple statements in the After SQL property, the connector runs each additional statement in sequence.

User Defined SQL is disable if we use ODBC Connector as Output.

I guess the user is trying to use Update statement and as per documentation, only 1 update statement can be executed

Please clarify

sriven786 wrote:User Defined SQL is disable if we use ODBC Connector as Output.

Are you sure about that? I often use the "user defined SQL" section to execute instructions using the input columns with the "Teradata Connector" stage (I did not try the ODBC stage).

@neeraj Did you try to add a COMMIT instruction between the 2 UPDATE statements?

Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)

Answer is still the same, only a single DML statement is supported.

chulett wrote:Answer is still the same, only a single DML statement is supported.

yes. After sql can support multiple statements

sriven786 wrote:Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)

I am going a little off-topic here but you can definitely do it with an ODBC Connector, i just did it on DataStage 11.5 using Windows.

I also execute those statements using the same job (in the "user defined SQL" section) : They both update the DATA1 table.
Is there something i don't get ? I also use SQL Server on Azure.

User is using Datastage 11 on Unix and ODBC connector stage on Unix did not have the option of User Defined Sql.

Job Type: Parallel
OS: Unix

Note:Not sure how to attach image. so could not attach the screen snapshot