Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Migrating SQL Server User Defined Data types with DMS table error

15 May,2023 by Rambler

 Question :  In an AWS DMS Migration task I'm trying to migrate a SQL Server  table with columns defined as User Defined Data Types but getting a message with with  Table 'dbo'.'MyTable' does not exist, insufficient privileges or no supported columns 

CREATE TYPE ssn_varchar FROM varchar(11) NOT NULL ;
CREATE TYPE ssn_bigint FROM bigint;
CREATE TYPE ssn_tinyint FROM tinyint;
CREATE TYPE ssn_bit FROM bit;
CREATE TYPE ssn_smallint FROM smallint;
CREATE TYPE ssn_int FROM int
CREATE TYPE ssn_datetime FROM datetime;

--list user defined data types
select * from sys.types where is_user_defined = 1


CREATE TABLE [production].[myTable](
[Id] [int] NOT NULL,
[col_varchar] [dbo].[ssn_varchar] NOT NULL,
[col_bigint] [dbo].[ssn_bigint] NOT NULL,
[col_tinyint] [dbo].[ssn_tinyint] NOT NULL,
[col_bit] [dbo].[ssn_bit] NOT NULL,
[col_smallint] [dbo].[ssn_smallint] NOT NULL,
[col_int] [dbo].[ssn_int] NOT NULL,
[col_datetime] [dbo].[ssn_datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)

 

The SQL Server source permissions I'm using are based on the details found on : Permissions for full load only tasks  on the AWS documentation. 

Answer:DMS does not migrate the User Defined Data Types , but extracts the values and places them as column definitions . It is outside of this workaround scope to discuss how to move the user defined data types and rearchitect to PostgreSQL.

The AWS documented permissions for full load only tasks doesn't include permission to view the UDT definition. To allow the login to access the definition add the following permission to the login:

GRANT VIEW ANY DEFINITION TO [dms_user]

 

The VIEW DEFINITION View Definition permission in SQL Server to allow users to view the object definitions

 

 

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Migrating SQL Server User Defined Data types with DMS table error


dba-ninja.com