locked
SSMA Can't find tables named VALIDATION and YEAR??? RRS feed

  • Question

  • Hello, folks.

    So, on this new chapiter on my adventures of migrating Oracle DB to SQL with SSMA I found a new curious issue, as the question itself:  SSMA is simply ignoring two specific tables named VALIDATION and YEAR.  

    So, the scenario:  I'm preparing a dinamic XML script to migrate some specific tables with the console.  On my script I have a very long list of tables (but not all them).  Every one in the list is correctly treated but those two.

    A sample of the xml file:

    <metabase-object object-name="lil2015.vacrepl" /> <metabase-object object-name="lil2015.vactype" /> <metabase-object object-name="lil2015.vacweeklyquota" /> <metabase-object object-name="lil2015.validation" /> <metabase-object object-name="lil2015.varitinerary" /> <metabase-object object-name="lil2015.varpoint" /> <metabase-object object-name="lil2015.varpointedit" />

    <!-- some other tables -->

    <metabase-object object-name="lil2015.workforcereq" /> <metabase-object object-name="lil2015.workforcereqloc" /> <metabase-object object-name="lil2015.workforceversion" /> <metabase-object object-name="lil2015.wpchoice" /> <metabase-object object-name="lil2015.wpcriteria" /> <metabase-object object-name="lil2015.year" /> <metabase-object object-name="lil2015.zone" />


    And the output log:

    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VACREPL
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VACTYPE
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VACWEEKLYQUOTA
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VARITINERARY
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VARPOINT
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.VARPOINTEDIT
    
    (some other tables)
    
    2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.WORKFORCEREQ
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.WORKFORCEREQLOC
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.WORKFORCEVERSION
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.WPCHOICE
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.WPCRITERIA
    [2019-05-13 09:13:36]  MESSAGE generic Object passed as a parameter table LIL2015.ZONE
    [2019-05-13 09:13:36]  MESSAGE generic Running command on all tables in schema LIL2015

    So, no LIL2015.VALIDATION after LIL2015.VACWEEKLYQUOTA, neither LIL2015.YEAR after LIL2015.WPCRITERIA.  The weirdest thing is that I have neither an error messager no a warning;  SSMA is simply skipping those two tables like that, without any explanation.

    Are those some sort of reserved keyword for SSMA?  If in the improbability they are, why doesn't it throw an error or something?  Does anybody has a clue of what's going on here?  

    I will test something later: run a migration with only these two tables to see what kind of messages (or maybe error) it will return.  Gonna post the results later.


    Monday, May 13, 2019 1:41 PM

Answers

  • Hi,

    Please quote the names like so:

    			<metabase-object object-name="lil2015.&quot;VALIDATION&quot;"  /> 	
    			<metabase-object object-name="lil2015.&quot;YEAR&quot;"  /> 

    Make sure to use the upper-case when quoting, as Oracle will not upper-case quoted identifiers for you.

    It appears that SSMA in this case expects objects names that match keywords to be quoted. We will investigate whether this constraint can be relaxed, but in the meantime just quote everything that matches a keyword. In general, since identifier names in Oracle are case-sensitive, it is a good practice to reference your objects with their full names (quoted in proper case) instead of relying on Oracle to upper-case for you behind the scenes.

    Thanks,
    Alex.

    Monday, May 20, 2019 5:00 PM

All replies

  • This is really intriguing, tried launching it with only these two tables on the scrip, like this:

           <migrate-data verbose="true"> 
    			<data-migration-connection   
    				source-server="ora8"   
    				target-server="gsql2" />   
    			<metabase-object object-name="lil2015.validation"  /> 	
    			<metabase-object object-name="lil2015.year"  /> 	
    		</migrate-data> 

    And got this on the output:

    [2019-05-13 10:54:26]    START reconnect-target-database
    [2019-05-13 10:54:26]  MESSAGE generic Reconnecting to sql-server server 'gsql2'
    [2019-05-13 10:54:27]   FINISH
    [2019-05-13 10:54:27]    START migrate-data
    [2019-05-13 10:54:27] FATALERR No metabase object were specified for the command

    So SSMA is deliberated skiping the tables, like the words VALIDATION and YEAR were really commenting out the lines on the xml script.  It returned a fatal error simply because there were no other table on the list.

    Now it became a real problem.  Why exactly is it avoiding these tables/words?  Is there a way to escape them or force somehow the loading?  Is there special character use for such reserved words?

    Any advices?

    Monday, May 13, 2019 3:19 PM
  • Hi,

    Which version of SSMA are you using? Please try with SQL Server Migration Assistant 8.1 for Oracle. You can download it from here. And please try to use the GUI to migrate these two tables. 

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 14, 2019 5:07 AM
  • I'm already using 8.1

    I will try and test the GUI for those two specific tables, maybe I can find something relevant like a hidden message or something generated in the project files after a migration - if they are migrated, that is.

    But using the GUI for real jobs is out of question since the main reason we're testing SSMA is the possibility to script and automating data exchange. 

    But my questions still stand: are YEAR and VALIDATION reserved words?  Why they make SSMA skip theirs entries lines in the XML file?  Why are there no warning nor any message about them?

    Tuesday, May 14, 2019 4:02 PM
  • Hi,

    I found that YEAR and VALIDATION are Oracle reserved words. Please refer to List of Oracle Reserved Words.


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 15, 2019 1:23 AM
  • Although that's an official Oracle document, I'm not convinced this is the root cause of the problem:

    SQL> create table year 2 (year varchar2(30)); Table created. SQL> insert into year (year) values ('This is my year!'); 1 row created. SQL> commit; Commit complete. SQL> select year from year; YEAR ------------------------------ This is my year!

    SQL> set serveroutput on size 30000

    SQL> declare
      2      year varchar2(50) := 'This is my year!';
      3  begin
      4  dbms_output.enable(5000);
      5  dbms_output.put_line(year);
      6  end;
      7  /
    This is my year!

    PL/SQL procedure successfully completed.

    SQL>

    So, I can use them "freely" as an object name or in a PL/SQL code (as I already do in the actual database to be migrated).  Maybe there are some special situations where they are not allowed, such as role names or usernames, but this is not the case here. 

    Moreover, explicitly using a reserved Oracle should return an ORA-XXXXX error.  The point here is that SSMA seems to be completely hiding a possible error (if any) , like a "ON ERROR CONTINUE" situtation.

    But now the plot thickens: I tried the GUI and it perfectly migrates these two tables.  I looked in the project folder but found not of importance, nor anything specific for the words YEAR nor VALIDATION.  Did I just hit an obscure bug?  If so, should I have any hope for a patch anytime soon?

    Wednesday, May 15, 2019 3:49 PM
  • OK, just did a last test just changing the name of the tables to YEAR2 and VALIDATION2 on the Oracle database.  The XML reflecting this change:

           <migrate-data verbose="true"> 
    			<data-migration-connection   
    				source-server="ora8"   
    				target-server="gsql2" />   
    			<metabase-object object-name="lil2015.validation2"  /> 		
    			<metabase-object object-name="lil2015.year2"  /> 	
    		</migrate-data> 

    Did not change anything else; same columns, same data, same permissions.  And there you are:

    [2019-05-16 10:04:22]   FINISH 
    [2019-05-16 10:04:22]    START migrate-data 
    [2019-05-16 10:04:22]  MESSAGE generic Object passed as a parameter table LIL2015.VALIDATION2
    [2019-05-16 10:04:22]  MESSAGE generic Object passed as a parameter table LIL2015.YEAR2
    [2019-05-16 10:04:22]  MESSAGE generic Running command on all tables in schema LIL2015
    [2019-05-16 10:04:22]  MESSAGE generic Migration started...

    Although I can McGyver these two tables like that, its' not a pratical solution, specially that I don't know neither which nor how many other words are silently skipped by SSMA console.  Could somebody please submit a bug report or instruct me how to do it, please?


    Thursday, May 16, 2019 2:20 PM
  • Hi Leandro_Martins_de_Lima,

    You can report what you have found about SSMA at SQL Server feedback. Thanks for your contribution:-)

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 20, 2019 5:32 AM
  • Hi,

    Please quote the names like so:

    			<metabase-object object-name="lil2015.&quot;VALIDATION&quot;"  /> 	
    			<metabase-object object-name="lil2015.&quot;YEAR&quot;"  /> 

    Make sure to use the upper-case when quoting, as Oracle will not upper-case quoted identifiers for you.

    It appears that SSMA in this case expects objects names that match keywords to be quoted. We will investigate whether this constraint can be relaxed, but in the meantime just quote everything that matches a keyword. In general, since identifier names in Oracle are case-sensitive, it is a good practice to reference your objects with their full names (quoted in proper case) instead of relying on Oracle to upper-case for you behind the scenes.

    Thanks,
    Alex.

    Monday, May 20, 2019 5:00 PM
  • Thank you very much for you reply, Alexander.  Your solution is more acceptable, I tested here and it correctly migrated both tables.  This avoids my ugly rename/revert names back approach.  Still this is  just a workaround; I have to manually treat these tables by adding the quot notation to theirs names when dinamically creating the XML script.  Also, I don't want to have a list of all possible keywords to quote; I just have these two special tables for now, but if a new one shows up I will have to manually add it on my program. 

    Thank you for your replies too, Chen.  With the remark of Alexander (the keywords matching) I will report this situation in the feedback page as you suggested me. 

    Tuesday, May 21, 2019 2:29 PM
  • Hi,

    I'm working on SSMA team, you don't need to report it anywhere else. I looked at this a little bit and it appears to me that there are some places in Oracle grammar, where keywords can be unambiguously treated as identifiers and other places where they have to be explicitly quoted. SSMA just tries to be more conservative and requires you to quote keywords in most/all cases. I'll check, if we can relax the constraint for this specific case, but very unlikely, as there is a rather simple workaround available.

    You don't need to maintain list of keywords, my suggestion is to just use the quoted names everywhere for everything, which removes any ambiguity and guarantees correct behavior in all cases.

    Regards,
    Alex.

    Wednesday, May 22, 2019 5:23 PM
  • Thanks for you feedback, Alexander.

    Yeah, you're right, I went with the quote everything solution, it's easier and avoid future surprises.

    I do understand that this workaround is enough to prevent such situation and I agree that in fact a patch is not applicable in this case, but I would still suggest to either make this detail clear in the doc or maybe the make the application give a message or a warning about it.  It took me a while to realize data was missing in the target database while there were no errors in the log.  

    Once more, thank you for your help.

    Wednesday, May 22, 2019 7:08 PM