Hi - i'm using the SQL Server Migration Assistant (SSMA)  tool to
migrate an Oracle 10 database to a SQL Server 2000 database.

The reports show that a 100% conversion is possible but the SQL
produced has only 'stubs' for all functions and all procedures. For
instance I get only this ...

  CREATE
    FUNCTION dbo.FUN_EMP_EMI_GET_FRMTTED_NAME
      (
        @EMP_AUTOID_IN numeric(9),
        @EMI_ACTIVE_FROM_IN datetime
      )
      RETURNS varchar(8000)
    AS

GO

.... as a conversion of what is in oracle 50+ lines of SQL (see bottom
of post for full text of Oracle Function).

Is it an accepted part of SSMA that it it reports it can convert
something but it can't ? I've not been able to find much support
activity so i don't know what's considered the norm.

thanks

Richard.



create or replace FUNCTION
"DRS_4_SQLSERVER_A"."FUN_EMP_EMI_GET_FRMTTED_NAME"  (EMP_AUTOID_IN IN
DRS_EMPLOYEE.EMP_AUTOID%TYPE,EMI_ACTIVE_FROM_IN IN
DRS_EMPLOYEE_INFO.EMI_ACTIVE_FROM%TYPE)
--FUN_EMP_EMI_GET_FORMATTED_NAME
  RETURN VARCHAR2
IS
  CURSOR CUR_EMP_EMI
  IS
  SELECT  *
  FROM VW_EMP_EMI
  WHERE
  (
  (EMI_EMP_ID = EMP_AUTOID_IN)
  AND
  (EMI_IS_DISABLED = 0)
  AND
  (EMI_ACTIVE_FROM <= EMI_ACTIVE_FROM_IN)
  AND
  (EMI_ACTIVE_TO > EMI_ACTIVE_FROM_IN)
  )
  ORDER BY EMI_ACTIVE_FROM DESC;

  REC_EMI         VW_EMP_EMI%ROWTYPE;
  STR_WORK_NAME_FAMILY     VW_EMP_EMI.EMI_NAME_FAMILY%TYPE;
  STR_WORK_NAME_FIRST     VW_EMP_EMI.EMI_NAME_FIRST%TYPE;
  STR_WORK_EMPLOYEE_ID     VW_EMP_EMI.EMP_EMPLOYEE_ID%TYPE;

  STR_FORMATTED_NAME     VARCHAR2(60);

BEGIN
  OPEN CUR_EMP_EMI;
  FETCH CUR_EMP_EMI INTO REC_EMI;

  STR_FORMATTED_NAME := '';
  IF CUR_EMP_EMI%FOUND THEN
    STR_WORK_NAME_FAMILY := LTRIM(RTRIM(REC_EMI.EMI_NAME_FAMILY));
    STR_WORK_NAME_FIRST := LTRIM(RTRIM(REC_EMI.EMI_NAME_FIRST));
    STR_WORK_EMPLOYEE_ID := LTRIM(RTRIM(REC_EMI.EMP_EMPLOYEE_ID));
    IF LENGTH(STR_WORK_NAME_FAMILY) > 0 THEN
--      we only return a non-blank value if we (A) find a record
--      and (B) find that the family name is non-blank otherwise
--      we return spaces
        IF LENGTH(STR_WORK_NAME_FIRST) > 0 THEN
                STR_FORMATTED_NAME := CONCAT(STR_WORK_NAME_FAMILY,',');
                STR_FORMATTED_NAME :=
CONCAT(STR_FORMATTED_NAME,STR_WORK_NAME_FIRST);
        ELSE
                STR_FORMATTED_NAME := STR_WORK_NAME_FAMILY;
        END IF;

        IF LENGTH(STR_WORK_EMPLOYEE_ID) > 0 THEN
                STR_FORMATTED_NAME := CONCAT(STR_FORMATTED_NAME,' [');
                STR_FORMATTED_NAME :=
CONCAT(STR_FORMATTED_NAME,STR_WORK_EMPLOYEE_ID);
                STR_FORMATTED_NAME := CONCAT(STR_FORMATTED_NAME,']');
        END IF;
     END IF;
  END IF;
  CLOSE CUR_EMP_EMI;
  RETURN STR_FORMATTED_NAME;
END;

northof40 wrote:
Show quoteHide quote From a developer's POV, this seems very normal to me. The syntax of Oracle
SQL is so different from MSSQL, it can't convert all your code. What it
*can* convert, is all the objects themselves because they don't consist of
SQL code.

Well i agree the fact that SSMA can do any of what is does do is to be
applauded. The weird thing in this case is that

(A) SSMA reports that it *can* convert those functions and stored
procedures (but then doesn't) and

(B) SSMA is quite capable of converting triggers of similar complexity.

I wouldn't be at all surprised if SSMA had reported it couldn't convert
them and then didn't but in this case it reported it could and then ...
didn't ;-)

Be interested to hear from anyone else who has experience of using this
tool..

While I'm at it if anyone from within Microsoft (I know you're out
there somewhere !) would like a UI bug report on this tool I have one
for them. When doing 'Migrate Data' there's something a bit screwy in
the code that manages which tables are selected. If you select more
than one it actually migrates only one table. Also if you have selected
more than one and then tick them off SSMA gets pretty confused about
what is now ticked.

Anyway like i say if anyone has experience of using SSMA and has seen
it say it'll convert function and then hasn't i'd be interested to
hear.

thanks

Richard.

SQL 2005 Flat File source (in Import wizard) missing
What's the best way to keep a live db and an archive db?
run Profiler on a specific SP/Query/Function
Need Help with "System.Data.SqlClient.Sqlerror: Cannot open backup device (DLT tape) ...
Displaying parms in Profiler when using DSN