Hi All,
We have PX job which loads data into a SQL Server2000 DB.
We have an identity column defined in the table, but we have set identity insert as off, because some other processes will also load into the table.
When this job tries to load into empty table, load is successful. But on attempting load into the actual table - which has about 1.2 Million records, the job hangs after 20-30 % execution.
Has anyone else faced a similar situation?
Appreciate if someone can provide a workaround for this.
Can we set IDENTITY_INSERT ON during our jobs execution and then set IDENTITY_INSERT OFF, so other porcesses are able to load properly and we load unqiues sequences into the table?
Job hanging due to Identity Column
Moderators: chulett, rschirm, roy
You can set Identity Insert On in OPEN statement and OFF in CLOSE statement. But that means that you will have to generate the keys and pass the key column in your insert. And no one else should load anything into that table at that time.
I am not sure why your job is hanging. Get together with your DBA and monitor at the database end. Your dba will be able to tell you whats going on. What is your commit level?
I am not sure why your job is hanging. Get together with your DBA and monitor at the database end. Your dba will be able to tell you whats going on. What is your commit level?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks Ray,
I just verified after removing the MS_IDENT. The job is still hanging after 30% processing.
I even ran it on a single node configuration table, but the job still hangs.
But when I load this data to an empty table, it loads well and then a server job is able to append to the existing large table also.
What all settings should I look for in SQL Server 2000 DB to verify if the issue is due to DB.
Please Let me know.
Thanks in Advance.
I just verified after removing the MS_IDENT. The job is still hanging after 30% processing.
I even ran it on a single node configuration table, but the job still hangs.
But when I load this data to an empty table, it loads well and then a server job is able to append to the existing large table also.
What all settings should I look for in SQL Server 2000 DB to verify if the issue is due to DB.
Please Let me know.
Thanks in Advance.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: