Hi,
Sorry for the late answer. Have you tried the Delimited Identifiers option as indicated by the screen shot below
Kind Regards
Ulf
Hi Ulf.
Yes, I just tried the scripting a create table with the delimited identifiers turned on (in Beta 120.3119) with the same results. (Does this count as reported bug for beta 120? <wink>)
An error occurred while performing the operation:
SQL compilation error:
Table '<OBFUSCATED>.PUBLIC._SW_0048FFAC_4764_4465_A045_71E398B418BC_SEQ_000001' does not exist or not authorized.
Table name is actually _SW_0048ffac_4764_4465_a045_71e398b418bc_Seq_000001
I did find a section under the database drivers (tool properties) where you can specify situations where delimited identifiers should be used. One of the options was for scripting. I enabled the scripting checkbox (indicating delimited identifiers should be used when scripting), disconnected from snowflake, then reconnected, attempt to script a CREATE table for a table with lowercase characters in the name and I got the same result, "<uppercased table name> does not exist or not authorized"
Hi,
could you include the SQL that is generated from the Script action? I.e the content of the SQL Commander once the action is run.
Also it would be great if you could supply us with the original DDL of the table.
If this is not possible maybe you could supply us with an test example which exemplifies the error.
Both the DDL/SQL to create the table and the resulting SQL from the Script action.
Kind Regards
Ulf
There is no SQL generated by the Script action as it errors before producing the SQL.
Here's how to replicate
DDL:
-- Create table with case sensitive table name CREATE TABLE "POC_EXAMPLE_DB"."PUBLIC"."00_Customer_Test" ( contact_id INTEGER , star_id VARCHAR(50) , first_name VARCHAR(50) , last_name VARCHAR(50) , state VARCHAR(20) , zip VARCHAR(12) ); -- Populate table with case sensitive table name INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (1, 'a1b2c3d4-e5f6-abcd-1234-000deadbeef0', 'Alan', 'Alda', 'ME', '00001'); INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (2, 'a1b2c3d4-e5f6-abcd-1234-111deadbeef1', 'Bob', 'Barker', 'NH', '11112'); INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (3, 'a1b2c3d4-e5f6-abcd-1234-222deadbeef2', 'Charlie', 'Chaplin', 'VT', '22223'); INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (4, 'a1b2c3d4-e5f6-abcd-1234-333deadbeef3', 'Danny', 'Devito', 'RI', '33334'); INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (5, 'a1b2c3d4-e5f6-abcd-1234-444deadbeef4', 'Emilio', 'Estevez', 'MA', '44445'); INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (6, 'a1b2c3d4-e5f6-abcd-1234-555deadbeef5', 'Farrah', 'Fawcett', 'CT', '55556');
1. Execute above statements to create and populate the table
2. Right click on table name, choose Script Table, set options to following image
Hope this helps.
-- Tom
It appears the non-quoting of table names is pervasive for Snowflake.
Here is the result of trying to create a new table with a lower case table name (because the lowercase table name is not quoted in the CREATE TABLE statement, this will result in an upper case table name in Snowflake).
Hi Tom,
Perfect. Thanks for the information. We will look into this.
Kind Regards
Ulf
Hi Tom
The use of delimited identifiers in the create table dialog is configured for the connection.
Connection -> Properties -> Snowflake -> Delimited Identifiers. Check the Action checkbox and delimited identifiers will be used for actions such as Create/Alter Table.
The error you reported at 3. we will address in an internal ticket.
My t-shirt size is XXL
Tom Wittbrodt
I'm trying to script a CREATE TABLE statement by right clicking on the table name, choosing "Script Table...", then choosing the CREATE radio button under the Scripting Type header, Options - Format SQL is checked, Statement delimiter is ';', Output Destination is SQL Commander: New Editor.
The table I'm selecting is _SW_807c1a63_b070_4ca5_a84c_a0c566698f4a_Seq_000002
(Note the use of lower case letters in the table name)
The error I'm getting is the following:
An error occurred while executing the database request for:
Short message:
An error occurred while performing the operation:
SQL compilation error:
Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.
Long Message:
SQL compilation error:
Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.
Details:
Type: com.onseven.dbvis.l.e.r
Stack Trace:
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:139)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:485)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:362)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:502)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:247)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:186)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:790)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:295)
at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.onseven.dbvis.jb.c.c.b(Z:2186)
at com.onseven.dbvis.jb.c.g$_b.call(Z:1369)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
[wrapped] com.onseven.dbvis.l.e.r: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.
at com.onseven.dbvis.l.e.k.qe(Z:729)
at com.onseven.dbvis.l.e.bb.ie(Z:982)
at com.onseven.dbvis.l.e.bb.sd(Z:2081)
at com.onseven.dbvis.ab.c.p.f(Z:2499)
at com.onseven.dbvis.db.d.f.l(Z:2320)
at com.onseven.dbvis.db.d.f.d(Z:1164)
at com.onseven.dbvis.l.e.q.c(Z:3364)
at com.onseven.dbvis.l.e.q.qe(Z:1160)
at com.onseven.dbvis.l.e.bb.ie(Z:982)
at com.onseven.dbvis.l.e.bb.sd(Z:2081)
at com.onseven.dbvis.objects.d.c.n.c(Z:2365)
at com.onseven.dbvis.objects.d.c.n.b(Z:1811)
at com.onseven.dbvis.objects.d.c.n$1.b(Z:346)
at com.onseven.dbvis.l.e.b.j.qe(Z:188)
at com.onseven.dbvis.l.e.bb.ie(Z:982)
at com.onseven.dbvis.l.e.l.c(Z:1374)
at com.onseven.dbvis.l.e.l.doInBackground(Z:1521)
at javax.swing.SwingWorker$1.call(SwingWorker.java:295)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at javax.swing.SwingWorker.run(SwingWorker.java:334)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
System Information:
Product: DbVisualizer Pro 11.0.5 [Build #3113]
OS: Linux
OS Version: 4.15.0-122-generic
OS Arch: amd64
Java Version: 1.8.0_201
Java VM: Java HotSpot(TM) 64-Bit Server VM
Java Vendor: Oracle Corporation
Java Home: /usr/lib/jvm/java-8-oracle/jre
DbVis Home: /opt/dbvis_11.0.5
User Home: /home/<USER>
PrefsDir: /home/<USER>/.dbvis
SessionId: 994
BindDir: /home/<USER>/.dbvis/[0]