Sybase to SQL Server MIgration Issues

Concatenating NULLABLE char column with NOT NULLABLE char column: 

                While concatenating the Nullable character column with the NOT Nullable column, Sybase right trimming the first column(Nullable character column) and then concatenating but SQL Server directly concatenating .So SQL Server gives the output along with the trailing spaces for the first column.

 

While concatenating the Nullable character column with the NOT Nullable column, Sybase right trimming the first column(Nullable character column) and then concatenating but SQL Server directly concatenating .So SQL Server gives the output along with the trailing spaces for the first column.Assume the table with two columns of nullable char(10) and not nullable char (10).

There is a record like below.

 When concatenating the two columns , SYBASE will gives the output without space like below.

SELECT column1+column2,* FROM dbo.test

 When concatenating the two columns ,SQL SERVER gives the output with space like below.

SELECT column1+column2,* FROM dbo.test

 Finally Sybase, right trimming the nullable character column while concatenation but not SQL Server.

Solution:

Resolve the above given scenario and get the same result as like as Sybase, it’s mandatory to add the rtrim() for the first column like below.

SELECT rtrim (column1) +column2,* FROM dbo.test

Don’t use ANSI Padding off to resolve this issue. This is not recommendable in future version.

Please click here to know about sybase to sql server migration issues 

SSMA error message S2SS0046

CONVERT or CAST expression from binary to integer might cause a problem because of platform dependence

                  Comparing binary value with Timestamp column was different between Sybase and SQL server. The below code was working fine in Sybase but not in SQL server

Where timestamp = substring(CONVERT(varbinary (16), CONVERT(INT, @row_upd)), 5, 8)  where @row_upd was varchar(38 datatype)

To handle this the same code has been rewritten using Dummy value concatenation

0x00000000+substring (0x00000000 +CONVERT (varbinary(8), CONVERT(INT, @row_upd')),5,8)

 

Sybase to SQL Server MIgration Issues

Group by statement:

In Sybase, Group by statement doesn’t require only the column in group by clause to be placed in select list in Sybase. But in SQL server it is mandatory that both select list columns and group by columns should match or select list columns should be the subset of the columns in group by clause.

Replace the statements with Min () aggregate function for the columns which is not part of the group by list.

Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server MIgration Issues

Avoid using Emulated function:

         When the migration of objects by Using SSMA, emulated functions would have created in SYSDB by SSMA and those functions will be referred to replace the SQL Server system functions in the database objects. These emulated functions, may affect the performance of the query.

To avoid the performance impact, should use the below changes. Emulated function call has been replaced with actual statement change using case statement (Like If @start <0 for substring) or by making necessary platform specific parameter alteration (Like Proper SQL server specific  datepart ).

Please click here to know about sybase to sql server migration issues   

Sybase to SQL Server MIgration Issues

Remote Procedure call:

Remote procedure call to other  Sybase ASE Db located remote server called through four part name. Example of the remote procedure call given below.

Exec  remoteserver.dbname.schemaname.procname

In SQL Server the same statement cannot execute.To replicate the same scenario in SQL Server, Remote Sybase ASE server has been added as linked server and the four part reference has been modified as

Exec (dbname.schemaname.procname) at remoteserver

Please click here to know about sybase to sql server migration issues  

Sybase to SQL Server MIgration issues

Replace Tempdb tables with ##tables

                          Temp db Tables behavior is different between Sybase and SQL Server. In Sybase such tables exist till end of the owner session or explicit drop but in SQL server it will exist till explicit Drop. In Sybase Temp table created like tempdb.schema.temptable. To achieve same functionality Global temp table should be used so that the temp table is visible across all Sessions and Exist till end of the owner session or explicit drop. In SQL server temp table created like ##temptable. Preferably drop temp tables like below. I have placed Sybase equivalent SQL server scripts for create and dropping tables.

Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server MIgration Issues

Binary to Numeric Conversion fails in SQL Server

                    Converting binary value to Numeric is possible in Sybase, but not possible in SQL Server. Instead of, we can convert the Binary value into Integer. This is not exact solution for replicate Sybase scripts.But we can adjust some scenarios with this changes.

Please click here to know about sybase to sql server migration issues  

Sybase to SQL Server MIgration Issues

Issue for Inserting Timestamp

If we try to insert the actual timestamp data from Sybase table to SQL Server, we are getting error with ‘Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. Severity 16’.We intent to create same database copy to SQL Server based on Sybase with all data. But this above given error stopping the data insertion. I have attached the screenshot for the direct insertion along with error.

To avoid the above given error along with data migration, Map timestamp to binary (8) type for table columns. During SSMA conversion, add a new column named ssma_timestamp that has a Transact-SQL timestamp type. Add the default @@DBTS for the old timestamp column to ensure that this column receives proper timestamp values after the migration finishes. The ssma_timestamp column is necessary to advance the @@DBTS value after the row is update. We should change the timestamp to ssma_timestamp for the required places of procedures, functions, triggers and other objects. Usually these changes will be in the select statement of the scripts. Please find the screenshot below with the required changes.

Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server MIgration Issues

Nullability difference in Table creation:

           While creating table in Sybase, default nullability of the column is Not null.But in server server the default nullability of the column is Null. That means if the developer did not specify the nullability of the column while creating table,sybase defines the column as Not Nullable and SQL Server defines the column as Nullable.

You can see the example for the default nullability value in the right side of the screenshot for the corresponding database.

Sybase:

SQL Server:

 

            While migrating ‘create table statement’, we should give the NOT NULL for the columns doesn’t have nullability in the column definition.



Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server Migration Issues

Variable Scope/behavior in Update statment:

                   In Sybase intermediate calculation with variable for the update statement,Variable value is based on last executed statement or last executed record.In Sybase,variable updation will happen after the column updation.Which means the updation in variable has low priority when compare with the updation in column  for the single update statement.If we think the output of the updated variable want to become the input of the column updation for each and every row,we cannot attain in Sybase.Because the update in column will execute first and then update in variable execute in single update statement even we placed left or right side.

But in SQL Server we can perform the above operation.The variables are highly dynamic and record dependent.The priority for the variable updation is in high priority.So we can get consistent result.

If you see the below given screenshots you can understand the scenario
which i am trying to explain.


Variable behavior in Sybase Update statement






Variable behavior in SQL Server Update statement



































             I just shared the difference between two technologies for the update statement while using intermediate variable.Hopefully we won't get such a scenario from Source.




















Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server Migration Issues

Data truncation while Insertion

                 The behavior of Sybase and SQL Server is different in the Insert statement.

When we trying to insert additional characters when compare with actual column length,

Sybase automatically truncates the excess characters and inserts.

But SQL Server will raises the below given error. 

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

                 If we are trying to insert the more than 10 character for the column which is having

length 10,Sybase will removes the characters from 11th position and inserts the first 10 character
into the table.But SQL Server raises the error.You can understand easily with the
following Screenshots given below.



Sybase,Inserting too many characters than actual column length

  

SQL Server,Inserting too many characters than actual column length



Please click here to know about sybase to sql server migration issues 

Sybase to SQL Server Migration Issues

 Default Date/Datetime format in Sybase Vs SQL Server:

               Default Date/Datetime Format is different for both Sybase and SQL Server.
So we need to change the target system’s(SQL Server) date/datetime format,
based on the source system’s output.I am giving the default datetime formats below.

Sybase Default Date Format:
              MON DD YYYY H: HH:MM: SSS AM

SQL Server Default Date Format:
              YYYY-MM-DD HH:MM:SS

Please click here to know about sybase to sql server migration issues

Cursor behavior in SQL Server vs Sybase

Objective is to be, if one cursor’s input is based on the other cursor. We should execute the input cursor with prior and input cursor’s script want to come first in the procedure

Cursor interdependency based on parameter supported by Sybase, even the inner cursors declare statement placed next of the outer cursors declare statement. But Cursor interdependency based on parameter not supported in SQL server, if the Cursor declaration is not done inside the outer cursor’s open statement. We will get the different result set for the same logic code based on the Sybase and SQL Server. If we made the code changes to declare the Inner cursor inside the Outer cursor (between Open and close block), we can get the same result set. I have given detailed explanation below along with screenshot.

Sybase Script with Cursor Inter-dependency

Here first cursors output is the input for the second cursor. But the declaration of the second cursor located before the first cursors open statement. Though Sybase maintains the Inter-dependency between the cursor and passes the first cursor’s output to next cursor’s input. So the Sybase output is based on the two cursor’s input and output. You can see the output of the Sybase below.

One more is, Cursor de-allocation handled by Sybase engine automatically. But In SQL Server we should give the de-allocation explicitly for the required places.

Sybase Output for the above given sample code

To achieve the same result set in SQL Server we should declare the Second (Inner) Cursor declaration and open statement inside the First (Outer) Cursor’s Open statement. Otherwise SQL Server engine will gives the NULL value for the @var2 and only give the below result set.

SQL Server output with bug 

SQL Server Script with relevant changes 

Here we given De-allocate statement for each cursor explicitly for the required places .Outer cursors output will simply flow as a inner cursor’s input. If we made the code changes like above we can get the same result as like as Sybase. Once again I attached actual result for your reference.

SQL Server Output with relevant changes 

Please click here to know about sybase to sql server migration issues 

SSMA error message S2SS0015

Unparsed SQL [ZERO:] cannot be converted:

               SSMA raises above given error, while migrating the 'GOTO' Transact sql.

Though SSMA raises the error,both Sybase and SQL Server will get execute the code successfully along with GOTO.To maintain the best practice,readability we should convert the 'GOTO' Control statement into IF....ELSE,Nested IF or any other Control statement.I have placed sample code with GOTO Statement along with the output.

Below given sample code will give the same result without GOTO to follow best practice.Try to avoid GOTO statement in the program.

Please click here to know about sybase to sql server migration issues

Missing parameter in procedure call

Expects Parameter in the procedure call:

                 While calling procedure,Sybase adding NULL for the required parameters and executed successfully, even we miss to pass some parameters .But the SQL Server raises the error while missing to pass some parameters.

                     If we are passing 2 parameter while calling the 'dbo.calledproc' instead of 3 actual parameter,Sybase engine adds NULL for the third parameter.But SQL Server raises the below error.

Msg 201, Level 16, State 4, Procedure CALLED_PROC, Line 0

Procedure or function 'CALLED_PROC' expects parameter '@PARAMETER3', which was not supplied.

SQL Server Result wihile missing parameter in procedure call


To avoid the above given error,we should add NULL for remaining parameters for procedure call.Then the proceudre will get executed succussfully.Find the correct screenshot below.

Please click here to know about sybase to sql server migration issues

Passing Excess parameter in procedure call

Passing additional parameter in the procedure call:

                      While calling procedure,Sybase passes only required parameters and executed successfully, even we pass the too many parameters .But the SQL Server raises the error while passing too many parameter.
                     If we are passing 4 parameter while calling the 'dbo.calledproc' instead of 3 actual parameter,Sybase engine removes the fourth parameter and pass the first three parameter.But SQL Server raises the below error.

Msg 8144, Level 16, State 2, Procedure CALLED_PROC, Line 0
Procedure or function CALLED_PROC has too many arguments specified.

SQL Server Result while passing Excess parameter

To avoid the above given error,we should remove the additional/unwanted parameters from procedure call.Then the proceudre will get executed succussfully.Find the correct screenshot below. 
      .


Please click here to know about sybase to sql server migration issues

SSMA error message S2SS0015

Unparsed SQL, cannot converted

 Please Check the Script to satisfy the following best practices to avoid the S2SS0015 error message.

1.Required space should be there,in between column and sql server keywords.

2.Check wether,given keywords available in sql server.

3.Need to check,variable declaration is correct.

4.Check for syntax error.

INSERTINTO #temp_swtch
 (           column1,
             column2,
             column3)
       VALUES (

            'first value',
            'second value',
            1 )

In the above given statement,Space character missing between INSERT and INTO.

If we change the statement like below we can resolve the S2SS0015 Error.

INSERT INTO #temp_swtch
 (           column1,
             column2,
             column3)
       VALUES (

            'first value',
            'second value',
            1 )

Please click here to know about sybase to sql server migration issues

SSMA error message S2SS0002

The following SQL statement cannot be converted

CREATE TABLE tempdb.guest.temptable (
                                 user_id int NOT NULL,
                                 req_id int NOT NULL
                                                                 )
                                 LOCK  DATAROWS

LOCK  DATAROWS keyword is not available in sql server.

To achieve the same in sql server, we should give nolock for the select statments involving the above table  or else we should set the isolation level as 'Read uncommitted '.

Please click here to know about sybase to sql server migration issues

SSMA warning message S2SS0016

SQL Server might use a different error code
                        Error code may change based on the Sybase and SQL Server for the same error.But in some cases both are having same error code.
                        Selecting the error from @@error and Returning error code by @@error wont make any difference in both the technologies.And generic condition check also wont make any difference.That means
     IF @@ERROR=0
     IF @@ERROR<>0
Both statement behaves same in sybase and SQL Server.We dont need to make any change for the below statement also.
SELECT @@ERROR
RETURN @@ERROR

If the script contains specific error code condition.Then we should take care of the error.
Please use the below links to refer Sybase error codes and meaning.

http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc36584.1260/html/iqerror/Errsybcode.htm

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.39996_1250/html/svrtsg/svrtsg382.htm

http://manuals.sybase.com/onlinebooks/group-asarc/srv10024/errtsg/@Generic__BookTextView/77

According to the Sybase error codes change the equivalent SQL Server error codes in the Code.

Please click here to know about sybase to sql server migration issues