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