In ASE, a bit field or a bit parameter always has a value of 0 or 1. Undefined bits (NULL) do not exist in ASE. MS SQL Server allows not just 0 and 1, but also NULL as a value. In two cases, this results in a difference.

• Bit variables and bit parameters that have not been initialized have a value of 0 in ASE, but a NULL value in MS SQL Server.

DECLARE @b Bit

SELECT @b AS b

	b
ASE	0
MS SQL Server	NULL

Solution: Initialize bit parameters and bit variables at 0.

DECLARE @b Bit = NULL

• A column that should have NULL values because of an outer join contains a bit value of 0 in ASE. However, in MS SQL Server, this column is NULL.

CREATE TABLE T_Left (a Char(10), i Int)

CREATE TABLE T_Right (i Cnt, b Bit)

INSERT INTO T_Left VALUES ('A', 1)

SELECT L.a AS L_a

, L.i AS L_i

, R.i AS R_i

, R.b AS R_b

FROM T_Left L

LEFT OUTER JOIN T_Right R

ON L.i = R.i

DROP TABLE T_Left

DROP TABLE T_Right

	L_a	L_i	R_i	R_b
ASE	A	1	NULL	0
MS SQL Server	A	1	NULL	NULL

Solution: Use the IsNull function in the SELECT clause for bits that may be NULL, and then use Cast to convert the value back to a bit type.

SELECT …, Cast(IsNull(R.b, 0) AS Bit) AS R_b

You can find bit fields and bit parameters by searching for 'Bit' or 'T_Indicator', as the T_Indicator and T_Indicator_DefTrue UDTs are of the bit type.

In ASE, when you declare a cursor containing variables, the variables will get a value at the time the 'OPEN CURSOR' statement is executed. In MS SQL Server, the variables will get a value at the time the 'DECLARE CURSOR' statement is executed.

In ASE, the code snippet below is considered to be the most efficient use of cursors. When the 'DECLARE CURSOR' statement is executed, space is reserved for the cursor once. Whenever 'OPEN CURSOR' is performed, the @PartCode variable will get a different value.

DECLARE @ProdHeaderDossierCode T_Code_ProdHeadDossier

, @PartCode T_Code_Part

DECLARE cr_Outer CURSOR FOR

SELECT PartCode FROM T_Part

DECLARE cr_Inner CURSOR FOR

SELECT ProdHeaderDossierCode

FROM T_ProductionHeader

WHERE PartCode = @PartCode

OPEN cr_Outer

FETCH cr_Outer INTO @PartCode

WHILE @@SQLSTATUS = 0

BEGIN

OPEN cr_Inner

FETCH cr_Inner INTO @ProdHeaderDossierCode

WHILE @@SQLSTATUS = 0

BEGIN

-- Some processing is done here

FETCH cr_Inner INTO @ProdHeaderDossierCode

END

CLOSE cr_Inner

FETCH cr_Outer INTO @PartCode

END

CLOSE cr_Outer

DEALLOCATE CURSOR cr_Inner

DEALLOCATE CURSOR cr_Outer

In MS SQL Server, this coding method has a different outcome. When the 'DECLARE CURSOR' statement is executed, the @PartCode variable already gets a value in the cursor. As the local variable is undefined at that time, the cursor statement is executed with @PartCode = NULL. Because no production files with PartCode = NULL exist, the inner loop will be run. In MS SQL Server, we therefore need the following code to make sure the behavior is the same as in ASE:

DECLARE @ProdHeaderDossierCode T_Code_ProdHeadDossier

, @PartCode T_Code_Part

DECLARE cr_Outer CURSOR FOR

SELECT PartCode FROM T_Part

OPEN cr_Outer

FETCH cr_Outer INTO @PartCode

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE cr_Inner CURSOR FOR

SELECT ProdHeaderDossierCode

FROM T_ProductionHeader

WHERE PartCode = @PartCode

OPEN cr_Inner

FETCH cr_Inner INTO @ProdHeaderDossierCode

WHILE @@FETCH_STATUS = 0

BEGIN

-- Some processing is done here

FETCH cr_Inner INTO @ProdHeaderDossierCode

END

CLOSE cr_Inner

DEALLOCATE cr_Inner

FETCH cr_Outer INTO @PartCode

END

CLOSE cr_Outer

DEALLOCATE cr_Outer

This problem does not just occur when you are working with nested cursors. The following code snippet also behaves differently in ASE and MS SQL Server:

DECLARE @PartCode T_Code_Part

, @Qty T_Quantum_Qty10_3

DECLARE cr_Example CURSOR FOR

SELECT Qty

FROM T_Inventory

WHERE PartCode = @PartCode

SET @PartCode = '1000.001.000000'

OPEN cr_Example

FETCH cr_Example INTO @Qty

WHILE @@SQLSTATUS = 0

BEGIN

-- Some processing is done here

FETCH cr_Example INTO @Qty

END

CLOSE cr_Example

DEALLOCATE CURSOR cr_Example

Here, the same problem occurs as with the previous code snippet. At the time the cursor is declared, the @PartCode variable does not have a value yet.

Solution: All 'DECLARE CURSOR' statements must be moved to just above the corresponding 'OPEN CURSOR' statement. The 'DEALLOCATE CURSOR' statements must be moved to just after the corresponding 'CLOSE CURSOR' statement. If there is no DEALLOCATE, create one.

The possible values of @@SQLSTATUS in ASE do not match the possible values of @@FETCH_STATUS in MS SQL Server. The following translation should be used:

@@SQLStatus	@@Fetch_Status	Description
0	0	Fetch successful
1	-2	Fetch failed Record deleted or moved
2	-1	End of record set reached

The SSMA tool translates @@SQLSTATUS 0 and 1 incorrectly. The translation of @@SQLSTATUS 2 is in itself correct, but not easily understandable. Use the table below for a correct translation that is also easier to understand.

ASE	@@SQLSTATUS <> 0	@@SQLSTATUS = 1	@@SQLSTATUS <> 2
SSMA translation	CASE @@FETCH_STATUS WHEN -1 THEN 2 ELSE 0 END <> 0	CASE @@FETCH_STATUS WHEN -1 THEN 2 ELSE 0 END = 1	CASE @@FETCH_STATUS WHEN -1 THEN 2 ELSE 0 END <> 2
Error	@@FETCH_STATUS = 2 is now considered as 'Fetch successful'	@@FETCH_STATUS = 2 is now considered as 'Fetch successful'	This is not wrong, but the alternative is easier.
Improvement	@@FETCH_STATUS <> 0	@@FETCH_STATUS = -2	@@FETCH_STATUS <> -1

Problems can arise with date fields that were passed as strings. ASE tries to translate a string converted to a date first according to the US format, then according to the European format and lastly according to the ISO format. MS SQL Server tries to translate a string to a date based on the format of the language linked to the logged on user, then according to the ISO format. In both cases, this may result in an incorrect translation when the day of the month is less than 13.

Example for March 7, 2012:

U.S. format:	03.07.2012	Can be translated as July 3, 2012
European format:	07-03-2012	Can be translated as July 3, 2012
ISO format:	2012-03-07	Always works correctly

Whenever a date is to be passed as a string, always use the ISO format.

As mentioned in Appendix B, item 2, the SSMA tool cannot always make sense of complex constructs. Often, a CROSS JOIN is used later on in the statement. Although most instances are marked by an SSMA error code, some are not. Apparently, in these cases, the SSMA tool is convinced that the translation with the CROSS JOIN is correct. Therefore, check the code for 'CROSS JOIN' and compare the ASE code with the new MS SQL code. If there were two tables in the FROM clause, without them being linked to each other in the WHERE clause, the translation is correct. In most other cases, the original ASE code can be restored. This code also compiles on MS SQL Server, and yields the same results it does in ASE.

CASE <expression> WHEN NULL THEN …

This is translated in the background to IF <expression> = NULL THEN. As the database is set to ANSI_NULLS OFF and the Isah application also has ANSI_NULLS OFF in the connection string, the Isah application works as expected. However, the default connection string of OLE DB, ODBC and the like is ANSI_NULLS ON. This means that if there are customizations using this (for example to make data available via a web browser), such code will behave differently. Defensive programming is better, so you are recommended to use the following construct:

CASE WHEN <expression> IS NULL THEN …

Previously, code similar to the code in the following code snippet was sometimes used:

G_DeliveryLine_D:

AND ( @@ProcID <> 'IP_prc_HistOrderFlow' )

This is how the trigger was blocked, except when the IP_prc_HistOrderFlow procedure was the procedure calling the trigger. In MS SQL Server, however, the value of @@ProcID in a trigger returns the object_id of the trigger itself rather than the ID of the calling procedure (as was the case in ASE). An auxiliary table has therefore been created called T_TmpCallingProc to simulate the behavior of ASE. Use code similar to the code below to achieve the behavior we know from ASE.

IP_prc_HistOrderFlow:

BEGIN TRANSACTION:

DELETE FROM dbo.T_TmpCallingProc WHERE SPID = @@SPID

INSERT INTO dbo.T_TmpCallingProc

VALUES (@@SPID, N'IP_prc_HistOrderFlow')

⁞ -- Processing takes place here

DELETE FROM dbo.T_TmpCallingProc WHERE SPID = @@SPID

IF @@TRANCOUNT > 0 COMMIT TRANSACTION

G_DeliveryLine_D:

AND ( NOT EXISTS ( SELECT 1 FROM dbo.T_TmpCallingProc

WHERE SPID = @@SPID

AND ProcName = N'IP_prc_HistOrderFlow'

)

)

In principle, there is no difference between the behavior of @@RowCount in MS SQL Server and in ASE. And yet, @@RowCount is often incorrectly used in custom triggers. However, if the first statement is SET NOCOUNT ON, @@RowCount is set to 1. In that case, you should use (SELECT Count(1) FROM inserted/deleted).

SELECT @CallerDesc = object_name(@@PROCID)

SELECT @CallerDesc = @CallerDesc + N' -> G_DossierDetail_I'

SELECT @numrows = @@ROWCOUNT

IF @numrows = 0

BEGIN

RETURN

END

The 'SELECT @numrows = @@ROWCOUNT' statement must be the first statement in the trigger after the declare. '@numrows' will always be 1, because the 'SELECT @CallerDesc …' statement will result in '1 row affected'. You want to know how many rows are to be processed by the trigger. Because the use of the new 'MERGE' statement results in other problems, it would be better to use the code snippet below.

DECLARE @numrows int

SELECT @numrows = COUNT(*) FROM inserted -- in insert/update triggers

SELECT @numrows = COUNT(*) FROM deleted -- in delete/update triggers

IF @numrows = 0

BEGIN

RETURN

END