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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |