Hi,
Basically we need to insert data into sql server table with lock enable so that no sidu operation is allowed for other users.
I have tried having isolation level to serializable, using hint as TABLOCKX but when the job is under execution in DataStage, I am able to select records from front end client.
I have tried this with ODBC and DRS stages but result not as expected. I am able to select records in another session which should not happen until DS job completes.
Is there any way to do this (ie. need to lock the table exclusively and do the insert in DataStage job)?
Thanks in advance.
How to lock sql server table?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I'm assuming your "hint" is being used via user-defined (custom) SQL, yes? You may want to have your DBA help you, found this online as a possible issue for it not working:
The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers