Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

Unable to retrieve next sequence value from a migrated Oracle sequence

Unable to retrieve next sequence value from a migrated Oracle sequence

  • Comments 2

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.’

clip_image002[7]

 

 

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:

 

clip_image004[8]

 

 

Author : Brian, SQL Escalation Services , Microsoft

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • Did not work for me.  I added the line but the value of the stored function is null.

    It appears that this is supposed to pull the sequence from the source database table dbo.$SSMA_seq_YOUR_SEQ_NAME.  That table is blank.  Its identity column, id, is filled and has the correct seed.  But the table is empty.

  • I updated it and found instead that altering the datatype for the function’s return value , the local variable is a better solution.

Page 1 of 1 (2 items)