SSMA2008 for Sybase doesn’t inherently migrate user defined data type to SQL Server.
This applies to the latest build on SSMA for Sybase v4.2.2277. A future version might have the feature to convert them implicitly but as of now we don’t do it.
a) If a Table in Sybase is using a user defined datatype the same is transparently converted to respective system data types. SSMA replaces Sybase user-defined types by the underlying types and they also become part of the table definitions. Any rules applied for user data types will be ignored during conversion.
For example if there is a Table in Sybase as follows.
CREATE TABLE [TableWithCustomType]
(
[Name] MyCustomType NOT NULL
)
It will get converted as follows.
CREATE TABLE [dbo].[TableWithCustomType]
(
[Name] char(10) NOT NULL
)
b) If the need is to have a custom DataType with the same name and precision then they have to be added manually.
SYBASE : execute sp_addtype "MyCustomType" , char(10)
SQL Server : execute sp_addtype "MyCustomType" , “char(10)”
The complete list of syntax can be found here http://msdn.microsoft.com/en-us/library/ms189784.aspx
Below are the steps for the work around to have the same user defined types (also known as alias in SQL Server parlance) in SQL Server Tables post migration from Sybase.
Below are the steps to be followed to make tables in SQL Server have the same types as the tables in Sybase in the type happens to be a user defied one.
a) Find tables and StoredProcedures on Sybase using the User Defined Data Type using the following query. Make a note of the tables, stored procedures.
select o.name, c.name from syscolumns c inner join sysobjects o on c.id = o.id inner join systypes t on t.type = c.type where t.name = 'MyCustomType'
b) Added the User Defined Data types in SQL Server by executing the following on SQL Server management Studio.
SQL Server : execute sp_addtype "MyCustomType" , “char(10)”
c) Move the Schema from Sybase to SQL Server. ( Note: Don’t do the data migration yet )
d) Use following query on SQL Server to modify the concerned tables on SQL Server.
USE [Test]
GO
/****** Object: Table [dbo].[TableWithCustomType]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableWithCustomType]') AND type in (N'U'))
DROP TABLE [dbo].[TableWithCustomType]
GO
USE [Test]
GO
/****** Object: Table [dbo].[TableWithCustomType]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableWithCustomType](
[Name] dbo.MyCustomType
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
e) Right click on the SQL Server Database in SSMA and Sync the changes from Database è SSMS Metadata
Please note the direction of the update.
f) Migrate the data after that. The data would migrate with no issue and the columns will be of user defined data type on SQL Server.
Author : Angshuman(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Engineer, Microsoft