ODBC Connector Multiple update Queries
Moderators: chulett, rschirm, roy
ODBC Connector Multiple update Queries
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!)]
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!)]
Re: ODBC Connector Multiple update Queries
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.
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.
Venkata Srini
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).sriven786 wrote:User Defined SQL is disable if we use ODBC Connector as Output.
@neeraj Did you try to add a COMMIT instruction between the 2 UPDATE statements?
BI Consultant
DSXConsult
DSXConsult
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.sriven786 wrote:Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)
BI Consultant
DSXConsult
DSXConsult
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.
Code: Select all
update THOMAS.DATA1
set INT02 = ORCHESTRATE.INT02
where INT01 = ORCHESTRATE.INT01;
update THOMAS.DATA1
set INT03 = ORCHESTRATE.INT03
where INT01 = ORCHESTRATE.INT01;
Is there something i don't get ? I also use SQL Server on Azure.
BI Consultant
DSXConsult
DSXConsult