none
SSMA Conversion issue RRS feed

  • Question

  • SSMA Conversion issue

    SSMA during conversion added schema and table name before the columns which will return wrong output logically. For e.g. the no column which is inserted into #tab from hist table


    SYBASE:
    select no, desc, code 
    into #tab
    from hist

    select no, desc, code from hist
    where no in 
    (select no from #tab) --> The no column used in this line originally didn't have any prefix 


    SQL After SSMA Conversion in SQL Server:
    select dbo.hist.no, dbo.hist.desc, dbo.hist.code 
    into #tab
    from dbo.hist

    select dbo.hist.no, dbo.hist.desc, dbo.hist.code from dbo.hist
    where dbo.hist.no in 
    (select dbo.hist.no from #tab) --> The no column used in this line post migration had the schema and table name as prefix.


    1. Why SSMA converted like this with the schema and table name as prefix
    2. if there are multiple scenarios like this, how can i find all of them
    3. Syntax wise, it is not failing but logically the output is different than expected since the column used in the inner query


    SQL Server DBA

    Tuesday, October 4, 2016 11:06 AM

All replies

  • Hello,

    That's how we use object names in SQL Server, with the full qualified name = Schema.Objectname and the query result is the same as without the schema name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 4, 2016 11:12 AM
  • SQL After SSMA Conversion in SQL Server:
    select dbo.hist.no, dbo.hist.desc, dbo.hist.code 
    into #tab
    from dbo.hist

    select dbo.hist.no, dbo.hist.desc, dbo.hist.code from dbo.hist
    where dbo.hist.no in 
    (select dbo.hist.no from #tab)

    My query regarding the highlighted line, why column of #tab table is being qualified with hist table


    SQL Server DBA

    Wednesday, October 5, 2016 10:25 AM