Unable to retrieve next sequence value from a migrated Oracle sequence
Published Jan 15 2019 01:45 PM 799 Views
First published on MSDN on Apr 08, 2011


Background:


SSMA 4.2 emulates an Oracle sequence as a table containing an IDENTITY column. Each sequence gets its own table, prefaced by the literal “$SSMA_seq_”.    Thus, an Oracle sequence named SUPPLIER_SEQ would be migrated to a table in the target SQL Server database named “$SSMA_seq_SUPPLIER_SEQ”, defined as




CREATE TABLE [dbo].[$SSMA_seq_SUPPLIER_SEQ](


[id] [numeric](38, 0) IDENTITY(1,1) NOT NULL


) ON [PRIMARY]




SSMA provides a scalar function used to emulate Oracle’s NEXTVAL:


sysdb.ssma_oracle.db_get_next_sequence_value(@dbname,@schema,@name)



Where the arguments are:



  • @dbname: The name of the database that contains the sequence.

  • @schema: The name of the schema that contains the sequence.

  • @name: The sequence name.


Return type:  numeric(38,0).



Problem statement:


After migrating an Oracle sequence to SQL Server 2005, the db_get_next_sequence_value UDF unexpectedly returned NULL.


SELECT [sysdb].[ssma_oracle].[db_get_next_sequence_value] (


N'Scott'


,N'dbo'


,N'Test')



Returns: NULL





Workaround:


The root cause and a fix for this issue are still under investigation. However, in the interim, we have identified the following workaround.
1) In SQL Server Management Studio, navigate to sysdb, Programmability, Scalar-valued Functions.
2) Right-click the function ssma_oracle.db_get_next_sequence_value and select ‘Modify.’









3) Alter the function to add the highlighted line (shown in context):



ALTER function [ssma_oracle].[db_get_next_sequence_value](@dbname sysname,


@schema sysname,


@name sysname) RETURNS integer


as begin


declare @fullname nvarchar(386)


set @fullname = ssma_oracle.db_get_full_name(@dbname,@schema,ssma_oracle.db_get_sequence_table(@name))


if object_id(@fullname) is null return null;


declare @curval integer


declare @spid int, @login_time datetime


select @spid = sysdb.ssma_oracle.get_active_spid(),@login_time = sysdb.ssma_oracle.get_active_login_time()


exec master..xp_ora2ms_exec2 @spid,@login_time,'sysdb','ssma_oracle',


'db_sp_get_next_sequence_value',@dbname,@schema,@name,@curval output


return @curval


end



The function now returns the correct NEXTVAL for the identity value’s seed and increment:









Author : Brian, SQL Escalation Services , Microsoft


Version history
Last update:
‎Jan 15 2019 01:45 PM
Updated by: