Compartilhar via

SSMA : Migrate User Defined Data Types from Sybase to SQL Server

  • Artigo

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 https://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.

                   clip_image002

 

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

Comments

  • Anonymous
    December 09, 2013
    Hello Angshuman , would you know how to retrieve the actual data type from the cust datatype in Sybase ? My email address is reza@kazersystems.com Kindest regards, Reza

  • Anonymous
    December 09, 2013
    Hi Reza, Use sp_help 'MyCustomType ' go For details refer infocenter.sybase.com/.../index.jsp Regards, Angshuman