SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

AWS Schema Conversion Tool SCT fails to Convert Table DDL from SQL Server to Amazon Redshift

AWS Schema Conversion Tool SCT fails to convert table DDL SQL script or CREATE TABLE command from SQL Server database to Amazon Redshift database when the table has an identity column with data type smallint.

Here is the SQL DDL create command when I use Amazon SCT Schema Conversion Tool for migration of a SQL Server database schema objects including database tables to Amazon Redshift database.

CREATE TABLE IF NOT EXISTS ofsa.appointmentitemnumber
id_appointmentitemnumber INT IDENTITY(-32768, 1) NOT NULL,
appointmentitemnumber BIGINT ENCODE ZSTD

As seen below AWS Schema Conversion Tool converted SQL Create Table command for a specific table from SQL Server database to be created on Amazon Redshift database as follows

AWS Schema Conversion Tool to convert SQL Create Table command from SQL Server to Amazon Redshift

When the database developer tries to apply the above SQL DDL Create Table command provided by AWS Schema Conversion tool (SCT), the result will be unsuccessfull. Unfortunately the output message is not really helpful to understand the real reason behind the SQL error preventing table creation on target Amazon Redshift database.

AWS Schema Conversion Tool error. The object was not applied.

What the Schema Conversion Tool misses here in this table create SQL script is that Amazon Redshift SQL database engine does not allow to create a table with identity column of data type SmallInt.
The identity column data type should be of type Int or BigInt but not SmallInt on the other hand.

If the Amazon Redshift database programmer executes the CREATE TABLE command manually on the target Redshift database, developer will experience following error which provides some what more information about the nature of the SQL error.

[Amazon](500310) Invalid operation: invalid type specified for identity column "id_appointmentitemnumber"; [SQL State=42601, DB Errorcode=500310]


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.