Hi All.
At the beginning I would like to mention that I am new here .
I was looking for answer but I couldn't find nothing, that's why I'm writting here.
I execute stored procedure from ODBC Connector stage using Before SQL Statements:
execute delete_data @vDays=#P_NumberOfDays#
This procedure should delete a lot of data (~200mln records) from a lot of tables (~500 tables). In the biggest table is about 100 mln records to delete.
Job is simple:
ODBC Connector (Select statement: select 1 as row; Before SQL Statements: execute delete_data @vDays=#P_NumberOfDays#) ---> Copy Stage
After executing above command job is running but finally there are no correct results. When I checked sessions on database (exec sp_who2), it looks like procedure is done (I can see delete commands) but after this appear command TM REQUEST and everything is rollbacked (status = ROLLBACK).
Finally it looks like nothing is done (records are not deleted). Additionally there are no any errors or warnings in Run Director logs. I am sure that procedure is executed because during execution database needs a lot of disk space for transaction logs.
When I execute this procedure using Microsoft SQL Server Management Studio, everything is ok (records are deleted).
But when I execute by DataStage job (ODBC Connector stage) there is no right results.
When I changed the stage from Copy to Peek, results are the same (records are no deleted).
Could you help me or give me a clue why I can't delete data using stored procedure in ODBC stage? Maybe i should use different stage?
Thanks in advance,
Jack
MS SQL Server excecution stored procedure (why rollback?)
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Mon Apr 09, 2018 2:47 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 8
- Joined: Mon Apr 09, 2018 2:47 am
As Ray has hinted already with his question, you will need to add your commit or "GO". There is an "autocommit mode" that you can specify in the ODBC but I don't think that it applied to the before or after SQL.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 8
- Joined: Mon Apr 09, 2018 2:47 am