|
SwisSQL Sybase to SQL Server Edition helps
you to automate migration to a large extent. However there are a few constructs
that users have to migrate manually. This document explains handling constructs
not supported by the SwisSQL Sybase to SQL Server Edition.
Handling
GROUP BY clause in SELECT queries
SQL Server 2000 requires that all non-aggregate columns in the SELECT
list be named in the GROUP BY clause, if it is used. But Sybase allows
specifying only a subset of columns from the select list in the GROUP
BY clause. This needs to be taken care during migration to avoid errors.
So, in Sybase the following query will work :
select col1, col2, max(col3)
from #test_table
group by col1
|
In SQL Server it yields the following error message:
Server: Msg 8120, Level 16, State 1, Line 1
Column '#test_table.col2' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
The same outputs can be achieved in SQL Server by two methods. The first
method works in Sybase also:
select col1,
col2,
(select max(col3)from #text_table
where col1 = a.col1)
from #test_table a
The second method uses a SQL Server derived table
select a.col1, a.col2, b.maxcol
from #test_table a,
(select col1, max(col3) as maxcol
from #test_table group by col1 ) b
where a.int_value = b.int_value)
|
Aggregate functions cannot be used in SET
list of update statements
The following syntax is not accepted by SQL server:
update test_table1
set col1 = max(col2)
from test_table1 T1, test_table2 T2
where T1.common_col = T2.common_col |
The above statement will throw the following
error message in SQL Server :
Server: Msg 157, Level 15, State 1, Line 12
An aggregate may not appear in the set list of an UPDATE statement.
This statement can be re-coded as follows:
update test_table1
set col1 = (select max(col2)
from test_table2 T2
where T2.common_col = T1.common_col)
from test_table1 T1 |
SELECT
statement with DISTINCT and ORDER BY clause
In SQL Server 2000, all the items in the ORDER BY clause must appear in
the select list if SELECT DISTINCT is specified. The following error message
will be displayed when this condition is violated :
Server: Msg 145, Level 15, State 1, Line 8
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Statements that generate this message require modification, either by
removing offending columns from the ORDER BY clause, or adding them to
the SELECT list.
Reserved words handling
There are a list of reserved words in SQL Server which are not reserved
words in Sybase ASE. The migrated objects with any of the below specified
names should be renamed during migration to SQL Server
| BACKUP |
CLOSE |
COLUMN |
COMMITTED |
CONTAINS |
| CONTAINSTABLE |
CROSS |
CURRENT_DATE |
CURRENT_TIME |
CURRENT_TIMESTAMP |
| CURRENT_USER |
DENY |
DISTRIBUTED |
END |
EXISTS |
| FILE |
FLOPPY |
FREETEXT |
FREETEXTTABLE |
FULL |
| IDENTITYCOL |
INNER |
JOIN |
LEFT |
NOCHECK |
| NOW |
OPENDATASOURCE |
OPENQUERY |
OPENROWSET |
OUTER |
| PERCENT |
PIPE |
REPEATABLE |
REPLICATION |
RESTORE |
| RESTRICT |
RIGHT |
ROWGUIDECOL |
SERIALIZABLE |
SESSION_USER |
| SYSTEMUSER |
TAPE |
TOP |
UPDATE |
UPDATETEXT |
| UNCOMMITTED |
VIEW |
|
|
|
| 
