topic Re: Error attempting to CREATE a DBMS table in SAS Enterprise Guide

Error attempting to CREATE a DBMS table

L5ive — Wed, 07 Jul 2021 09:45:07 GMT

Hi Guys,

im trying to create a table on DB how ever i get the below Log.

Data edbact.test;set test;run;

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [Oracle][ODBC][Ora]ORA-01031: insufficient privileges .

i talked with the administrators of the server they said i have the right access to create tables on the DB however they don't seem to understand why this error is happening.

i appreciate your help and ideas so i can communicate with them.

Thank you,

Re: Error attempting to CREATE a DBMS table

andreas_lds — Wed, 07 Jul 2021 09:56:20 GMT

So the db-server says that you don't have the necessary privileges to create a table, while the admins say you have.

Have you checked https://support.sas.com/kb/47/797.html?

Re: Error attempting to CREATE a DBMS table

SASKiwi — Wed, 07 Jul 2021 21:16:28 GMT

Usually create table privileges will be limited to a specific database and schema. Are you sure you are writing to the correct schema? Also you could try actual Oracle SQL code in SQL Passthru.

Re: Error attempting to CREATE a DBMS table

L5ive — Thu, 08 Jul 2021 14:32:15 GMT

I was successfully able to assign the ODBC library however now i have a different problem as follows

first i wrote a line to assign the library given to me by the admins

libname ODBCWORK odbc user=work pwd='MYPWSD' datasrc='EGYDBACT-WORK';

Then i wrote the following code to create a test table

DATA ODBCWORK.TESTTABLEAU; SET TEST;RUN;

then i found that all the tables that were in the library were replaced by only the table i created. and if i opened a new session i can see the original database again

Before Defing the the Library After Defing the the Library

this also here is a photo of the proprieties of the DB for your reference

Re: Error attempting to CREATE a DBMS table

Sajid01 — Thu, 08 Jul 2021 14:44:59 GMT

That was the natural out come of your statement.

Try this This will create a dataset test in your work library. This will have the contents of testtableau table


DATA  test;
set ODBCWORK.TESTTABLEAU; 
RUN;

Re: Error attempting to CREATE a DBMS table

Tom — Thu, 08 Jul 2021 14:56:47 GMT

I am not sure what GUI those screen shots are from, they do not look like anything I have seen in SAS, but perhaps you are using a different interface.

Is it possible the default schema is a temporary space that is cleared when your database session ends? That would explain why a new session does not see the new table.

I do not see any mention of which schema you are connecting to in the LIBNAME statement. You could try using the SCHEMA= dataset option to override the default schema.

Re: Error attempting to CREATE a DBMS table

SASKiwi — Thu, 08 Jul 2021 20:16:30 GMT

Looks like you are creating a database temporary table. The default behaviour for these types of tables is for these to only exist while you maintain your database connection. As soon as you close the connection the temporary table is deleted. If you want to keep your database connection across multiple LIBNAME statements then try adding the CONNECTION = GLOBAL to your LIBNAME.

Re: Error attempting to CREATE a DBMS table

andreas_lds — Mon, 12 Jul 2021 06:48:09 GMT

@Tom wrote:

I am not sure what GUI those screen shots are from, they do not look like anything I have seen in SAS, but perhaps you are using a different interface.

Screenshots look like EG 8.x with the dark color theme.

Re: Error attempting to CREATE a DBMS table

L5ive — Mon, 20 Sep 2021 07:41:41 GMT

below is the complete log of the line.

hopefully this would be helpful

;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HTMLBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 data ora_work.testTableau_new;set ora_work.provider;keep npcod;run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-01031: insufficient privileges..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set ORA_WORK.TESTTABLEAU_NEW may be incomplete. When this step was stopped there were 0 observations and 1
variables.
ERROR: ROLLBACK issued due to errors for data set ORA_WORK.TESTTABLEAU_NEW.DATA.
NOTE: DATA statement used (Total process time):
real time 0.30 seconds
cpu time 0.00 seconds

29
30
31 %LET _CLIENTTASKLABEL=;
32 %LET _CLIENTPROCESSFLOWNAME=;
33 %LET _CLIENTPROJECTPATH=;
34 %LET _CLIENTPROJECTPATHHOST=;
35 %LET _CLIENTPROJECTNAME=;
36 %LET _SASPROGRAMFILE=;
37 %LET _SASPROGRAMFILEHOST=;
38
39 ;*';*";*/;quit;run;
40 ODS _ALL_ CLOSE;
41
42
43 QUIT; RUN;
44

Re: Error attempting to CREATE a DBMS table

Sajid01 — Mon, 20 Sep 2021 09:32:47 GMT

The log is clear. You do not have privileges to create a table in Oracle.