photo

kaushikc

shared this question
7 months ago

Employees Involved

photo

DMT Support Team

Admin

Statistics

1
Comments
1
Views

Relates to

Share

Tags

1
votes

Conversion from sequence to identity column in SSMA for Oracle

Hi,

I am migrating a database from Oracle to SQL Server with

project setting for converting sequences as ‘Use identity column’.

After schema conversion and data migration is done, when

I insert new value in table, I got the error message as ‘Procedure or function db_sp_set_sequence_value has too many

arguments specified.’ I resolved the error by disabling the trigger

which was generated by SSMA for that table.

Also, sequence in Oracle had cache of 20. So, even with only 9 rows present in table, by default SQL Server was taking identity seed as 21. So I had to manually reset the identity to the next value, i.e. 10.

Are these the right things to do? Or is there any better

approach for this?

Thanks in advance.

Add Comment

Comments (1)

photo Employee
1

Hello,

I was able to completely reproduce the situation that you described.

When in Oracle population of key columns is implemented with trigger plus sequence and you de—Āided convert trigger+sequence to SQL Server identity(1,1),

it's reasonable get rid of trigger and sequence at SQL Server side.

You are absolutely right, SSMA converts Oracle cache 20 like identity (21,1) and it's a bug. You should adjust identity seed manually.

Also, for some reason, SSMA adds extra database name N'TEST_FUNCTIONS' to the procedure call EXECUTE ssma_oracle.db_sp_set_sequence_value N'TEST_FUNCTIONS', N'dbo', N'SEQ_TEST', @currval

in the auxilary trigger [dbo].[T_TEST_SEQ_DEF$CurrvalSupport]. So you should also remove extra database manually.

Apparently, if you choose sequence-to-identity conversion you could disable trigger [InsteadOfInsertOn$T_TEST_SEQ_DEF] on [dbo].[T_TEST_SEQ_DEF].

And if there are occurrences of TEST_FUNCTIONS.SEQ_TEST.currval in your programmability, they will be substitute with SSMA emulation ssma_oracle.db_get_next_sequence_value(N'dbo', N'SEQ_TEST').

So don't disable auxiliary trigger [dbo].[T_TEST_SEQ_DEF$CurrvalSupport] which is used to set current identity value.

Best regards,

DBBest support team

Leave Comment

photo

Attach files...

The file must be a jpg, gif, png, bmp, ico, pdf, doc, rtf, txt, zip or rar no more than 2M