In step 23, @@TRANSTATE is deleted. An item was probably missed. Please refer to Appendix A, item 1, for a description of the solution.

Sometimes, the error messages will tell you exactly what the problem is. In this case, a field is used in the SELECT clause that has not been aggregated, while this field is not present in the GROUP BY and/or HAVING clause. In ASE this is allowed, but in MS SQL Server it is not.

The question is, however, if the original code actually does what it is supposed to do. Usually, the relevant field has to be added to the GROUP BY clause. Sometimes, it makes no difference to ASE whether or not the additional field has been added to the GROUP BY clause. In MS SQL Server, every field in the SELECT clause must be present in the GROUP BY clause, must be aggregated, or must be a constant. An example of ASE code causing the error:

DECLARE @MachGrpCode T_Code_MachGrp

SELECT @MachGrpCode = 'SCHU'

SELECT DatePart(cwk, CapacityAvailabledate) AS Period

, Sum(Capacity)/3600 As Capacity

, MachGrpCode

FROM T_MachGrpRealCapacity

WHERE MachGrpCode = @MachGrpCode

AND DateDiff(dd, '20090101', CapacityAvailabledate) >= 0

AND DateDiff(dd, '20091231', CapacityAvailabledate) <= 0

GROUP BY DatePart(cwk, CapacityAvailabledate)

The code above enforces the use of a constant in the MachGrpCode field. That is why it makes no difference to ASE whether or not this field is present in the GROUP BY clause. In MS SQL Server, however, this code does create a problem and must be added to the GROUP BY clause (as shown below).

DECLARE @MachGrpCode T_Code_MachGrp

SELECT @MachGrpCode = 'SCHU'

SELECT dbo.ssma_DatePart('cwk', CapacityAvailabledate) AS Period

, Sum(Capacity)/3600 As Capacity

, MachGrpCode

FROM T_MachGrpRealCapacity

WHERE MachGrpCode = @MachGrpCode

AND DateDiff(dd, '20090101', CapacityAvailabledate) >= 0

AND DateDiff(dd, '20091231', CapacityAvailabledate) <= 0

GROUP BY dbo.ssma_DatePart('cwk', CapacityAvailabledate), MachGrpCode

The SSMA tool regularly fails to make sense of complex constructs. In the case of a statement with OUTER JOIN clauses, it often should have displayed error code S2SS0062. In this case, refer to the item below.

Whenever code with derived tables (also called 'inline views') is generated on the MS SQL Server side, you are recommended to try the original ASE code. It usually has the intended effect on the MS SQL Server side.

An example:

SELECT @cfRevisionEndDate = dateadd(dd, -1, agg_expr$1)

FROM

(

SELECT

) AS t1

CROSS JOIN

(

SELECT min(dbo.T_DocumentMain.RevisionDate) AS agg_expr$1

FROM dbo.T_DocumentMain

WHERE dbo.T_DocumentMain.RevOfDocId = @DocId

) AS t2

Original ASE code

Select @cfRevisionEndDate = DateAdd(dd, -1, Min(RevisionDate))

From T_DocumentMain

Where RevOfDocId = @DocId

Lastly, we often see this error in complex and nested SubString functions. Code using this function stems from the Sybase ASE 11.0 period, when the CASE statement was not working properly yet. You are therefore recommended to convert such code to a CASE construct. An example of the SSMA code for MS SQL Server:

CustVendId = SubString( SubString( FJ.CustId + ' ', 1, 5 ) + '1'

, (1-isnull(0*ascii(rtrim(FJ.CustId)),1))

, 6)

+ SubString( SubString( FJ.VendId + ' ', 1, 5 ) + '2'

, (1-isnull(0*ascii(rtrim(FJ.VendId)),1))

, 6)

Try to find out what this code means. The code above could, for example, be translated to the CASE snippet below:

CASE

WHEN IsNull(RTrim(CustId), '') <> '' THEN CustId + '1'

WHEN IsNull(RTrim(VendId), '') <> '' THEN VendId + '2'

ELSE ''

END AS CustVendId

It will be obvious that it can be very difficult to decipher code as set out above.

This should never appear in code. Where necessary, use a TRY – CATCH construct instead

Use DateAdd(wk, …) or DateAdd(cdw, …).

These can be ignored.

Sybase automatically applies RTrim to VarChar fields, whereas MS SQL does not automatically apply RTrim to NVarChar fields. Because VarChar is translated to NVarChar, the behavior changes.

Sybase automatically applies RTRim to the right argument of LIKE, but MS SQL Server does not. Although the SSMA tool attempts to correct for this, the result is not always as intended. Due to the migration from VarChar to NVarChar, some cases will have unintended results.

Example for ASE:

CREATE PROC SIP_sel_GrpCodeLike (@GrpCode T_Code_Group05) AS

BEGIN

CREATE TABLE #Tmp (GrpCode VarChar(5))

INSERT INTO #Tmp SELECT @GrpCode

SELECT DISTINCT Cust.GrpCode

FROM T_Customer Cust, #Tmp

WHERE Cust.GrpCode LIKE #Tmp.GrpCode + '%'

END

Example of MS SQL Server code as generated by the SSMA tool:

CREATE PROC SIP_sel_GrpCodeLike (@GrpCode Char(5)) AS

BEGIN

CREATE TABLE #Tmp (GrpCode VarChar(5))

INSERT #Tmp SELECT @GrpCode

SELECT DISTINCT Cust.GrpCode

FROM T_Customer Cust, #Tmp

WHERE Cust.GrpCode LIKE RTrim(#Tmp.GrpCode + '%')

END

In a later stage, the UDTs will be restored, and VarChar is translated to NVarChar.

The code will then be as follows:

CREATE PROC SIP_sel_GrpCodeLike (@GrpCode T_Code_Group05) AS

BEGIN

CREATE TABLE #Tmp (GrpCode NVarChar(5))

INSERT #Tmp SELECT @GrpCode

SELECT DISTINCT Cust.GrpCode

FROM T_Customer Cust, #Tmp

WHERE Cust.GrpCode LIKE RTrim(#Tmp.GrpCode + N'%')

END

Because Sybase does apply RTrim to a VarChar, but MS SQL Server does not apply RTrim to an NVarchar, this does not yield the correct result. The RTrim parenthesis is actually in the wrong place. The code below does work as intended.

CREATE PROC SIP_sel_GrpCodeLike (@GrpCode T_Code_Group05) AS

BEGIN

CREATE TABLE #Tmp (GrpCode NVarChar(5))

INSERT #Tmp SELECT @GrpCode

SELECT DISTINCT Cust.GrpCode

FROM T_Customer Cust, #Tmp

WHERE Cust.GrpCode LIKE RTrim(#Tmp.GrpCode) + N'%'

END

Check the dynamic SQL for possible errors. All problems that may occur in SQL may also occur in dynamic SQL. Dynamic SQLerrors are usually easy to solve, however. See Appendix D: Errors To Be Solved For Dynamic SQL

It is not feasible to discuss all system tables of MS SQL Server here; the list below only contains the most important differences.

1. SysObjects

   This table is now in the sys schema and is called 'objects' and therefore 'sys.Objects'.

   The ID column is now called 'object_id'.

2. SysColumns

   This table is now in the sys schema and is called 'columns' and therefore 'sys.Columns'.

   The ID column is now called 'object_id'.

3. SysTypes

   This table is now in the sys schema and is called 'types' and therefore 'sys.Types'.

   The UserType column is now called 'user_type_id'.

   The Type column is now called 'system_type_id'.

   The Length column is now called 'max_length'.

   The Prec column is now called 'precision'.

   UserType > 100 used to return the UDTs.

   Now use 'is_user_defined = 1'.

   UserType < 100 used to return the system types.

   Now use 'is_user_defined = 0'.

4. SysReferences

   This table no longer exists. Use the sys.foreign_keys table.

   SysReferences.ConstrId is now sys.foreign_keys.object_id.

   SysReferences.TableId is now sys.foreign_keys.parent_object_id.

   SysReferences.RefTabId is now sys.foreign_keys.referenced_object_id.

   The Fokey1 to FoKey16 columns and the RefKey1 to RefKey16 columns no longer exist. Instead, you can use the sys.foreign_key_columns linking table in sys.columns to retrieve the information about the columns of the foreign key.

These can be ignored.