title: Convert stored procedure from Oracle to SQL Server with Oracle-To-SqlServer Migration Tool
body: <p>I converted below stored procedure with Oracle-To-SqlServer migration tool, but it's not able to convert it correctly:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE sp_Get_count(
  ClientCountout OUT VARCHAR2
  )
  is
  
  --Quarter VARCHAR2(20);
  --ClientCount VARCHAR2(20);
  
  --
  --Quarterout VARCHAR2(20);
  --ClientCountout VARCHAR2(20);
  
  varQuarter VARCHAR2(20);
  varStart_Date VARCHAR2(20);
  varEnd_Date VARCHAR2(20);
  
  CURSOR cr_client
     IS
      with dates as (
      select to_date('1-Jan-2012', 'dd-mon-yyyy', 'nls_date_language =american') startdate 
      ,      to_date('31-Mar-2012', 'dd-mon-yyyy', 'nls_date_language =american') enddate
      from   dual
      )
      Select to_char(to_date(Start_Date,'dd-mon-yyyy'), 'YYYY-Q') Quarter, Start_Date, End_Date from 
      (select to_char(add_months(startdate, level*3-3), 'DD-MON-YYYY') Start_Date,
             to_char(add_months(enddate, level*3-3), 'DD-MON-YYYY') End_Date
      from   dates
      connect by level &lt;= 10) ; 
  
   BEGIN
  
              OPEN cr_client;
  
       IF cr_client%ISOPEN
         THEN
            LOOP        
              FETCH cr_client
                        INTO varQuarter, varStart_Date,varEnd_Date ;   
              EXIT WHEN cr_client%NOTFOUND;
  
         ClientCountout:= USER_BUILDER.get_Client_Count(varStart_Date ,varEnd_Date) ; 
  
         dbms_output.put_line('Client Count for: ' || varQuarter || ' is '|| ClientCountout);
  
    END LOOP;
  
    CLOSE cr_client;
     END IF;
  END;
  </code></pre>
  
  <p>Below is the converted stored procedure for SQL Server:</p>
  
  <pre><code>CREATE PROCEDURE USER_BUILDER.sp_Get_count
     @CLIENTCOUNTOUT varchar(max)  OUTPUT
  AS 
     /*Generated by SQL Server Migration Assistant for Oracle version 7.8.0.*/
     BEGIN
        SET @CLIENTCOUNTOUT = NULL
  
        /*
        *   Quarter VARCHAR2(20);
        *   ClientCount VARCHAR2(20);
        *
        *   Quarterout VARCHAR2(20);
        *   ClientCountout VARCHAR2(20);
        */
        DECLARE
           @VARQUARTER varchar(20), 
           @VARSTART_DATE varchar(20), 
           @VAREND_DATE varchar(20)
  
        /* 
        *   SSMA error messages:
        *   O2SS0208: Subquery factoring clause conversion is not supported. 
        *   WITH dates
        *      (
        *         SELECT to_date('1-Jan-2012', 'dd-mon-yyyy', 'nls_date_language =american') startdate, to_date('31-Mar-2012', 'dd-mon-yyyy', 'nls_date_language =american') enddate
        *         FROM dual
        *      ), AS
  
        DECLARE
            CR_CLIENT CURSOR LOCAL FOR 
              WITH 
                 h$cte AS 
                 (
                    SELECT DATES.STARTDATE, DATES.ENDDATE, 1 AS LEVEL, CAST(row_number() OVER(
                       ORDER BY @@spid) AS varchar(max)) AS path
                    FROM DATES
                     UNION ALL
                    SELECT DATES.STARTDATE, DATES.ENDDATE, h$cte.LEVEL + 1 AS LEVEL, path + ',' + CAST(row_number() OVER(
                       ORDER BY @@spid) AS varchar(max)) AS path
                    FROM DATES, h$cte
                    WHERE LEVEL &lt;= 10
                 )
  
                 SELECT ssma_oracle.to_char_date(ssma_oracle.to_date2(fci.START_DATE, 'dd-mon-yyyy'), 'YYYY-Q') AS QUARTER, fci.START_DATE, fci.END_DATE
                 FROM 
                    (
                       SELECT TOP 9223372036854775807 ssma_oracle.to_char_date(dateadd(m, h$cte.LEVEL * 3 - 3, h$cte.STARTDATE), 'DD-MON-YYYY') AS START_DATE, ssma_oracle.to_char_date(dateadd(m, h$cte.LEVEL * 3 - 3, h$cte.ENDDATE), 'DD-MON-YYYY') AS END_DATE
                       FROM h$cte
                       ORDER BY h$cte.path
                    )  AS fci      */
  
  
  
        OPEN CR_CLIENT
  
        IF CURSOR_STATUS('local', N'CR_CLIENT') &gt; -1
           BEGIN
  
              WHILE 1 = 1
  
                 BEGIN
  
                    FETCH CR_CLIENT
                        INTO @VARQUARTER, @VARSTART_DATE, @VAREND_DATE
  
                    /*
                    *   SSMA warning messages:
                    *   O2SS0113: The value of @@FETCH_STATUS might be changed by previous FETCH operations on other cursors, if the cursors are used simultaneously.
                    */
  
                    IF @@FETCH_STATUS &lt;&gt; 0
                       BREAK
  
                    /* 
                    *   SSMA error messages:
                    *   O2SS0556: Identifier USER_BUILDER.get_Client_Count cannot be converted because it was not resolved.
                    *   This may happen because referenced object is missing from the database.
                    *   It is recommended to fix and recompile all invalid objects in Oracle before attempting a conversion.
                    *
  
                    SET @CLIENTCOUNTOUT = USER_BUILDER.GET_CLIENT_COUNT                  */
  
  
  
                    PRINT 'Client Count for: ' + ISNULL(@VARQUARTER, '') + ' is ' + ISNULL(@CLIENTCOUNTOUT, '')
  
                 END
  
              CLOSE CR_CLIENT
  
              DEALLOCATE CR_CLIENT
  
           END
  
     END
  </code></pre>
  
  <p>If you check SQL Server converted stored procedure, you will see "SSMA error messages" with details "O2SS0208: Subquery factoring clause conversion is not supported" and query has been commented by the migration tool.</p>
  
  <p>Thanks</p>