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.
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.
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.
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 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: