Cannot INSERT into a table which was converted from ORACLE 1

[kate8]

Hi,
I am not quite sure where I should post my question.
I have several tables which were converted from ORACLE database. I can select data from these tables, but cannot do the "INSERT" and there is no error message, just run forever if I don't stop it. I checked with the people who did the convertion, they said they did the same as other tables, which are working. I try to figure out why my tables donot allow me doing "INSERT". It is not the permission issue, I have checked it. What else should I check?
Thanks a lot for any helps!!!

 

[markros]

What is your insert command and have you tried running this insert command from SSMS?

It should return you the error message in the messages pane.

PluralSight Learning Library

 

[kate8]

I did it from NetBeans. I can open SSMS, but I am not the administrator. I am new for SQL Server.

Command should be right since I just did a very simple insert. like
"insert into dbo.MEMBERS (ID,LOCATION,NAME)
values ('100','testing','TEST')"

How to check the error message? Thank you!!!

 

[gmmastros]

Can you check to see if there are triggers on the table. It's possible that a trigger (written poorly) could cause your simple insert to take an incredibly long time.

Open SSMS
Open a query window
Run this:

sp_helptrigger 'MEMBERS'

There are other reasons that could explain why the insert is taking forever. For example, you could have millions of rows in this table without having an index. If this is the case, you can see what indexes are on the table with this:

sp_helpindex 'MEMBERS'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kate8]

All these tables have trigger. That could be the problem. I'll check it.
Thanks a lot!!

 

[kate8]

Hi gmmastros,

After I ran sp_helptrigger 'MEMBERS', I open the triggers "InsteadInsertOn$MEMBER" under this table, it shows
"SSMA error messages:
* O2SS0239: ROWID column is not accessible because the 'Generate ROWID' project setting is disabled.
and
" SSMA error messages:
* O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s)."[/color red]

So it means the trigger was not converted correctly. Is there any ways I can fix the problem or the table need to be re-converted?

Thank you so much for your help!! At least, I now know what the problem is. Thank you!!

 

[gmmastros]

I checked with the people who did the convertion, they said they did the same as other tables, which are working.

Seems like they did not convert everything properly. I doubt the whole table needs to be reconverted, but I would check with them. You may be able to get them to reconvert the triggers for this table and then drop the existing trigger(s) and recreate them based on the trigger you get from the people that did the conversion originally.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kate8]

gmmastros, Thank you!!!

They redo all the triggers. Now it is working!!