BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TD2017
Fluorite | Level 6

Hello

 

I am trying to use a SAS DI table loader transformation to load into a SQL Server table that is using a sequence value as a default constraint for the PK column. The insert works outside of SAS DI if exclude the PK column from the insert statement and let the default constraint populate the PK column value.

 

In SAS DI even though the PK column is not mapped in the Table Loader mapping the transformation is still generating a insert SQL statement that includes the PK column and a null value which results in the error below:

 

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'XXXXXXXXXXXXXX_ID', table 'xxxxxxxxxxx'; column does not allow nulls. INSERT fails. : [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.

 

I have tried the solution to add the IGNORE_READ_ONLY_COLUMNS=YES to the advanced option of the library definition but I still get the error.

 

Any ideas on how to accomplish the insert and have the PK column ignored in the insert? 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
If I recall correctly you can't have that column in your table meta data definition.
Data never sleeps

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20
If I recall correctly you can't have that column in your table meta data definition.
Data never sleeps
Patrick
Opal | Level 21

@TD2017

Is the column explicitly defined as readonly in the SQL Server table? Else: What @LinusH says.

TD2017
Fluorite | Level 6

 

Thank you Linus and Patrick for your responses. 

 

Based on your responses and a previous post by "nar_sas"  I believe the following are viable solutions:

 

 

1. Set the ignore_read_only_columns=yes option on either the library or table definition will prevent the column from being included in the Insert statement BUT this only works if the column is an IDENTITY (i.e. read-only).

 

This allows the metadata for the table definition to remain complete and the identity column will be auto incremented as expected.

 

2. Add a "drop=<ColumnName>" statement into the options on the table load transformation (Options -> Loader* -> Additional data table options) and again the column specific will be excluded from the insert statement.

 

This allows the metadata for the table definition to remain complete. In addition this works for both identity columns and those using a sequence and default constraint (i.e. not read-only). Both will be auto incremented as expected. This appears to work for both the Insert (SQL) and Append (Proc Append) News Rows options in the load technique settings.

 

3. Alter the metadata for the target table to not include the columns to be excluded.

 

I haven't tested this one yet but I suspect it will get around the issue of the unmapped columns being included in the insert but comes with the side affect of not providing a accurate representation of the table. 

 

Thanks again!

TD2017
Fluorite | Level 6

It appears there is no way to mark the answers from both @Patrick and @LinusH as solutions but the credit should go to both as well as @nar_sas from an older post.

 

Thanks all!

Patrick
Opal | Level 21

@TD2017

Thanks for taking the time to share the results of your testing.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2669 views
  • 5 likes
  • 3 in conversation