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 Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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
)
DISTSTYLE KEY
DISTKEY
(
id_appointmentitemnumber
);

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]






AWS Tutorials

AWS Tutorials

AWS








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems