Appendix A: Changes To Be Made In Sybase

Some possible errors are described below.

1. Delete occurrences of @@TRANSTATE

exec sp_ip_grep '@@TRANSTATE'

Example

If (@err != 0) Or (@@Transtate >= 2)

Should be:

If (@err != 0)

2. Use LEN() function instead of DataLength()

Instead of the DataLength() function for char, varchar and text fields, the LEN() function must be used. If it is not, problems will occur when field types are converted to UNICODE types.

sp_ip_grep 'DataLength'

3. Use LEN() function instead of CharLength()

Instead of the CharLength() function, the LEN() function must be used. If it is not, the SSMA tool will change it to the s2ss.char_length_varchar() emulation function, while the underlying fields will be translated to the nvarchar type.

Warning: The ASE LEN() function takes into account trailing spaces, but the MS SQL Server LEN() function does not. If you want to include trailing spaces, leave CharLength() unchanged and replace s2ss.char_length_varchar() by dbo.char_length_nvarchar() at a later stage. This replacement operation is described in the procedure (step 52).

4. Change the DateAdd(cwk, …) and DateAdd(calweekofyear, …) calls by the DateAdd(wk, …) call

MS SQL Server does not recognize the cwk 'datepart'. It does not matter, however, whether you advance the calendar by a European week or a US week, as a week has seven days in both Europe and the US.

Similarly, DateAdd(cdw, …) and DateAdd(caldayofweek, …) must be translated to DateAdd(dw, …).

5. Use IS NULL and IS NOT NULL

Because the OLEDB and ODBC drivers themselves send a default connection string, the programming code used for comparisons with NULL should be as defensive as possible. The default is ANSI_NULLS = ON. Using a search and replace operation on the files, the following changes can be made:

The first conversion (= NULL to IS NULL) cannot take place automatically. For the conversions <> NULL and != NULL, you can perform a search and replace operation in the files. This needs to be done first, because the = NULL pattern is also part of the !=NULL pattern. This method will result in fewer remaining items to check. In MS SQL, the NULL IS [NOT] x construct is not allowed and must be rewritten to x IS [NOT] NULL, which must also be done manually.