Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Multiple SQL Statements in SQL Script

Hi I want to run a SQL Deploy Script that has 1000 DDL statements inside it. When I pasted it into SQL Script and execute I get the following error:

Multiple SQL Statements in a single API call are not supported; use one API call per statement instead.

This contradicts the Help page which states:
"The script may contain multiple SQL statements"

I can't break up 1000 DDL Statements into 1000 different execution steps. Is it not possible to execute this via Matillion?

3 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Anthony,

The SQL Script Orchestration component does support writing multiple DDL Statements. I have tested it with our latest release 1.29.9.

Its possible that there is a subset of DDL that we haven’t tested and is raising this issue. Are you able to share your DDL with us?

What version of the product are you currently on? (Help→About)

Best
Kalyan


Anthony Schneider —

Matillion ETL
Version: 1.29.9 (build 74)AMI Version: 1.29.9


I just tested a simple one (Rather than my 1000 to confirm behaviour).

Example;

CREATE TABLE TEST1 (TEST1 INTEGER);
CREATE TABLE TEST2 (TEST2 INTEGER);


Matillion Agent  

Kalyan Arangam —

Hi Anthony,

My sincere apologies, I mistakenly ran tests on our redshift product.

I have raised an internal ticket to fix our documentation. Ticket reference: EMD-5136

This is a restriction of the Snowflake JDBC driver, it does not allow multiple ddl statements to be run as a Batch.

The only alternative (I found) to run a batch is to use SnowSQL (CLI Client).
If you intend to use it with matillion, SSH to the matillion instance and install SnowSQL. You may then use the bash script component to execute scripts hosted on the ec2-instance or on S3.

https://docs.snowflake.net/manuals/user-guide/snowsql-use.html#running-batch-scripts

Best
Kalyan

Post Your Community Answer

To add an answer please login