Error create table with data step in SQL Server

[SAS_LuisBolivar]

Error create table with data step in SQL Server.

ERROR: Error attempting to CREATE a DBMS table. .

It does not indicate an error description and when executing via pass-through it does not fail, but the SCD 2 transformation generates the tables with data passing.

Thanks.

[SAS_LuisBolivar]

Execution log sample:

MPRINT(ETLS_PUSHTODBMS): ;
MPRINT(ETLS_PUSHTODBMS): data etlstmp.WP9SZD4 (dbnull = ( ETLS_KEY = NO ETLS_CLSDATE = YES));
MPRINT(ETLS_PUSHTODBMS): attrib ETLS_KEY length = 8;
MPRINT(ETLS_PUSHTODBMS): attrib ETLS_CLSDATE length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'VALID_TO_DTTM';
MPRINT(ETLS_PUSHTODBMS): call missing(of _all_);
MPRINT(ETLS_PUSHTODBMS): stop;
MPRINT(ETLS_PUSHTODBMS): run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. .
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
WARNING: The data set ETLSTMP.WP9SZD4 may be incomplete. When this step was stopped there were 0 observations and 2 variables.
ERROR: ROLLBACK issued due to errors for data set ETLSTMP.WP9SZD4.DATA.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

[SASKiwi]

The first thing I would do is check if the table already exists or not and if it is, drop it first. You can do this easily by using SQL Passthru and an EXECUTE statement containing a DROP TABLE.

[Patrick]

What @SASKiwi mentions is a fair point but normally SAS DIS generated code does these checks and only attempts to create a table if it doesn't exist already.

For your text with explicit pass-through: Did you also use libref etlstmp?

I haven't used DIS for years now but in all the implementations where we used it, we always ended up to write a custom transformation for SCD2 loading into databases. We never managed to configure the OOTB SCD2 transformation in a way that it didn't generate code that resulted in way too much unnecessary data movements between SAS and the database.

[SAS_LuisBolivar]

Good morning.
 
The SCD2 transformation generates a proc dataset code that deletes the table before creating it, this process does not fail.
 
MPRINT(ETLS_SCDLOADER): LIBNAME etlstmp SQLSVR READBUFF=32767 INSERTBUFF=32767 Datasrc=XXXXXX CONNECTION=GLOBAL
SCHEMA=dbo USER=xxxx PASSWORD="XXXXXXXXXXX" DBMSTEMP=YES;
NOTE: Libref ETLSTMP was successfully assigned as follows:
Engine: SQLSVR
Physical Name: XXXXXXXXXXXXXX
NOTE: Dropping table ...
MPRINT(ETLS_PUSHTODBMS): proc datasets lib = etlstmp nolist nowarn memtype = (data view);
MPRINT(ETLS_PUSHTODBMS): delete WP9SZD4;
MPRINT(ETLS_PUSHTODBMS): quit;
NOTE: PROCEDURE DATASETS used (Total process time):
18 The SAS System 19:45 Friday, October 28, 2022
real time 0.05 seconds
cpu time 0.00 seconds
MPRINT(ETLS_PUSHTODBMS): ;
MPRINT(ETLS_PUSHTODBMS): data etlstmp.WP9SZD4 (dbnull = ( ETLS_KEY = NO ETLS_CLSDATE = YES));
MPRINT(ETLS_PUSHTODBMS): attrib ETLS_KEY length = 8;
MPRINT(ETLS_PUSHTODBMS): attrib ETLS_CLSDATE length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'VALID_TO_DTTM';
MPRINT(ETLS_PUSHTODBMS): call missing(of _all_);
MPRINT(ETLS_PUSHTODBMS): stop;
MPRINT(ETLS_PUSHTODBMS): run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. .
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
WARNING: The data set ETLSTMP.WP9SZD4 may be incomplete. When this step was stopped there were 0 observations and 2 variables.
ERROR: ROLLBACK issued due to errors for data set ETLSTMP.WP9SZD4.DATA.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

[Patrick]

If the Drop Table works under this libref then one would hope that you also got the Create Table permissions.

Add to the pre-code of the SCD2 transformation the following options:

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;.

These options will cause SAS to write the generated SQL to the SAS log that gets sent to the DBMS. May-be that will provide a bit more information why the Create Table statement fails.

But as already mentioned: Even if you can make this work if you are dealing with bigger data volumes then you will likely have to implement a custom transformation for SCD2 loading for performance reasons.

[SAS_LuisBolivar]

The Result:

CREATE TABLE "##WYQXD98" ("ETLS_KEY" NOT NULL ,"ETLS_CLSDATE" )

[Patrick]

---

@SAS_LuisBolivar wrote:
The Result:

CREATE TABLE "##WYQXD98" ("ETLS_KEY" NOT NULL ,"ETLS_CLSDATE" )

---

Above syntax is invalid because it's missing the data type definitions. If you execute this syntax directly against SQL Server you will get the following error.

Correct syntax would need to look similar to below.

CREATE TABLE "##WYQXD98" ("ETLS_KEY" int NOT NULL,"ETLS_CLSDATE" datetime);

If the SQL you shared is really what gets sent to the DB then you need to contact SAS Tech Support (support@sas.com).

If contacting SAS Tech Support then I would create and execute "sample" code that replicates the issue. Something like:

OPTIONS SASTRACE=',,d,' SASTRACELOC=SASLOG NOSTSUFFIX;
<libname from DIS generated code>
<proc datasets/delete from DIS generated code>
<data step with the error from DIS generated code>

Share the Code and the SAS Log with SAS Tech Support.

Also share the exact SAS version you're using, the OS, the SQL Server version and the SQL driver type and version used. If you don't know how to get this information then a SAS Admin at your site should be able to support you.

For the SAS version and the OS: I'm normally just running a Proc Setinit and include the "header" section to what I send to SAS Tech Support.