O2SS0013: EXECUTE IMMEDIATE statement was converted, but dynamic string was not converted. It must be converted manually.

65877627 0 Reputation points
2023-11-27T10:51:08.5966667+00:00

Hi,

I've checked common conversion errors for Oracle Database to SQL Server 2016 migration with SSMA (https://learn.microsoft.com/en-us/sql/ssma/oracle/messages/o2ss0007?view=sql-server-ver16) however this error is missingO2SS0013, EXECUTE IMMEDIATE statement was converted, but dynamic string was not converted. It must be converted manually. Below you find the details of the error. Can someone help me convert this and show how to convert it so that the procedure GETSEQUENCEVALUE can work and be migrated to the SQL Server 2016 and Data can be migrated?

ORACLE Procedure on Windows 2003 server:

 

CREATE OR REPLACE 
PROCEDURE getSequenceValue 
(
SeqName VARCHAR2, 
nValue IN OUT Number
)
AS
BEGIN 
EXECUTE IMMEDIATE 'SELECT ' || SeqName || '.NEXTVAL FROM DUAL'
INTO nValue;
END;

 

 

SSMA ERROR for SQL Migration:

 

CREATE PROCEDURE GETSEQUENCEVALUE  
@SEQNAME varchar(max),
/*
*   SSMA warning messages:
*   O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/

 

@NVALUE float(53)  OUTPUT
AS 
BEGIN

 

/* 
*   SSMA error messages:
*   O2SS0013: EXECUTE IMMEDIATE statement was converted into EXEC(...) statement, but dynamic string was not converted. It must be converted manually.
*

 

EXECUTE ('SELECT ' + ISNULL(@SEQNAME, '') + '.NEXTVAL FROM DUAL')
*/

 

 

DECLARE
@db_null_statement int

 

END
GO

Would love to hear from you, thanks!

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
515 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Osjaetor 475 Reputation points
    2023-11-27T12:19:29.0233333+00:00

    Hi 65877627,

    Can you try with that?

    Oracle Procedure:

    CREATE OR REPLACE
    PROCEDURE getSequenceValue
    (
      SeqName VARCHAR2,
      nValue IN OUT Number
    )
    AS
    BEGIN
      EXECUTE IMMEDIATE 'SELECT ' || SeqName || '.NEXTVAL FROM DUAL'
      INTO nValue;
    END;
    

    SQL Server 2016 Procedure:

    CREATE PROCEDURE GETSEQUENCEVALUE
    (
      @SEQNAME varchar(max),
      @NVALUE float(53) OUTPUT
    )
    AS
    BEGIN
      -- SSMA warning: Conversion from NUMBER datatype can cause data loss.
    
      -- SSMA error: EXECUTE IMMEDIATE statement was converted into EXEC(...) statement, but dynamic string was not converted. It must be converted manually.
    
      DECLARE @sqlText nvarchar(max);
    
      SET @sqlText = N'SELECT ' + @SEQNAME + '.NEXTVAL FROM DUAL';
    
      EXEC sp_executesql @sqlText, N'@SEQNAME varchar(max)', @SEQNAME, @NVALUE OUTPUT;
    END;
    

    Regards,


  2. Erland Sommarskog 106.5K Reputation points
    2023-11-27T22:17:17.48+00:00

    The code suggested by Osjaetor will not work. There are a number of errors in it.

    However, rather than trying to correct those errors, I suggest scapping the procedure altogether.

    To get the next value from a sequence, just say

    SELECT @x = NEXT VALUE FOR pelle
    
    0 comments No comments